Update Text File Via JET.OLEDB

[JTaylor]

Hey Stan, since you are playing today thought I would ask a question...

Is it possible to update records that have been loaded from a Text file into a Recordset?   Keeps telling me that I can for the a linked table with the ISAM that is in use.   I show two of my attempts here.   Everything works fine except the update.  Thanks.

Code (winbatch) Select Expand



   
       
    myConn = CreateObject("ADODB.Connection")
    myRS = CreateObject("ADODB.Recordset")
    adoConst = ObjectConstantsGet(myConn)
    fpath = DirScript()

    myConn.Open('Provider=Microsoft.Jet.OLEDB.4.0; Data Source="%fpath%"; Extended Properties="Text;HDR=Yes;FMT=Delimited"')

    If myConn.State <> adoConst.adStateOpen Then
        Message("ADO Connection failed", "Error: ADO Connection")
        myConn.Close
        Exit
    End If

    SQLText = "SELECT * FROM [bn_see.txt]"
    myRS.Open (SQLText, myConn, adoConst.adOpenDynamic, adoConst.adLockBatchOptimistic, adoConst.adCmdText)

    If myRS.State <> adoConst.adStateOpen Then
        Message("ADO Recordset failed", "Error: ADO Recordset")
        myRS.Close
        myConn.Close
        Exit
    End If

    SQLText = "Update [bn_see.txt] set [currency] = 'USDXXX' where [currency] = 'USD'"
;   myConn.Execute(SQLText)        ; UPDATE ATTEMPTED HERE

    While myRS.EOF == @FALSE
        rec_num = myRS.Fields("sku").Value
        title   = myRS.Fields("name").Value
;       myRS.Fields("currency") = "USDXXX"          ; UPDATE ATTEMPTED HERE
message(rec_num,title)       
        myRS.MoveNext
        If IsKeyDown(@CTRL&@SHIFT) Then myRS.MoveLast
    EndWhile


    myRS.Close
    myRS = 0
    myConn.Close
    myConn = 0



[JTaylor]

Appears this is not possible without involving some other pieces.

Jim

[stanl]

I didn't see the oRS.update or oRS.updatebatch()  method being used.

[JTaylor]

Hmmmmmmmm...Must have removed that when the other pieces failed.   While Those methods do not generate an error themselves, any thing I have tried to actually change values results in an error.   Such as below.

      myRS.Fields("label") = "HELLO"

Everything I have found indicates this is not possible but thought you might know different.  My main interest is in a batch update solution since if I have to loop through every record I have a solution for that already.   I have already achieved one of my major goals in that I am able to sort the records without intermediates steps, due to their being several million of them.

Thanks.

Jim

[stanl]

Well if it helps, I looked at some old scripts I wrote circa 2006. Seems if I wanted to make changes I would first save the recordset in Microsoft XML format, then re-open with MsPersist driver and the changes took.

[EDIT]
you want to post a sample of the text data and what to update, I can give it a try.

[JTaylor]

Okay.   Let me give it a go first.  Appreciate the offer but do not want to take up your time if I can figure it out.   I know of what you speak and have seen examples using that Driver.   Will let you know how it goes.  Thanks.

Jim

[JTaylor]

Okay.  I have loaded and, as importantly, sorted and rearranged the file via JET.OLEDB and then written it out to XML and then loaded it again via MSPersist.   I still can't figure out a way to do a batch update though.  That is, something like the following...I know I don't have myRS.UpdateBatch() in there.   Can't get past the error on the myJET.Execute(SQLText) line.   It doesn't seem to like the "[titles.xml]" bit but not sure what else to put in its place.  I am trying to avoid looping through every line.

Any help would be appreciated.  Thanks.

Jim

Code (winbatch) Select Expand

   

    myJET = CreateObject("ADODB.Connection")
    myRS = CreateObject("ADODB.Recordset")
    adoConst = ObjectConstantsGet(myJET)
    fpath = DirScript()
    fname = "titles.xml"

   ;myJET.Open('Provider=MSPersist; Data Source="%fpath%%fname%"; ')
    myJET.Open("Provider=MSPersist")
    myRS = myJET.Execute(fname)

;  myRS.Open (SQLText, myJET, adoConst.adOpenDynamic, adoConst.adLockBatchOptimistic, adoConst.adCmdText)

    SQLText = "Update [titles.xml] set format = 'HELLO' "
    myJET.Execute(SQLText)


    myRS.Close
    myRS = 0
    myJET.Close
    myJET = 0



Code (xml) Select Expand


<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly' rs:updatable='true'>
<s:AttributeType name='artist' rs:number='8' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='bn_see#txt'
rs:basecolumn='ARTIST'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='author' rs:number='7' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='bn_see#txt'
rs:basecolumn='AUTHOR'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='buyurl' rs:number='5' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='bn_see#txt'
rs:basecolumn='BUYURL'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='format' rs:number='11' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='bn_see#txt'
rs:basecolumn='FORMAT'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='imageurl' rs:number='6' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='bn_see#txt'
rs:basecolumn='IMAGEURL'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='label' rs:number='10' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='bn_see#txt'
rs:basecolumn='LABEL'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='name' rs:number='2' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='bn_see#txt'
rs:basecolumn='NAME'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='price' rs:number='3' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='bn_see#txt'
rs:basecolumn='PRICE'>
<s:datatype dt:type='float' dt:maxLength='8' rs:precision='15' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='publisher' rs:number='9' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='bn_see#txt'
rs:basecolumn='PUBLISHER'>
<s:datatype dt:type='string' dt:maxLength='255'/>
</s:AttributeType>
<s:AttributeType name='retailprice' rs:number='4' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='bn_see#txt'
rs:basecolumn='RETAILPRICE'>
<s:datatype dt:type='float' dt:maxLength='8' rs:precision='15' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='sku' rs:number='1' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='bn_see#txt'
rs:basecolumn='SKU'>
<s:datatype dt:type='float' dt:maxLength='8' rs:precision='15' rs:fixedlength='true'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row artist='Belinda Carlisle' buyurl='xxxFw%2Fher-greatest-hits-belinda-carlisle%2F104340%3Fean%3D0008811060626'
format='CD' imageurl='xxx0008811060626_p0_v1.jpg' label='Mca' name='Her Greatest Hits'
price='11.130000000000001' retailprice='11.99' sku='8811060626'/>
<z:row artist='The Who' buyurl='xxxFw%2Fthe-who-sell-out-the-who%2F75564%3Fean%3D0008811126827'
format='CD' imageurl='xxx0008811126827_p0_v1.jpg' label='Mca' name='Who Sell Out'
price='12.43' retailprice='13.99' sku='8811126827'/>
<z:row artist='Sonny & Cher' buyurl='xxxFw%2Fgreatest-hits-sonny-cher%2F72925%3Fean%3D0008811174521'
format='CD' imageurl='xxx0008811174521_p0_v1.jpg' label='Mca' name='Greatest Hits [1974]'
price='12.43' retailprice='13.99' sku='8811174521'/>
<z:row artist='Sublime' buyurl='xxxFw%2Fsublime-acoustic-bradley-nowell-friends-sublime%2F24659738%3Fean%3D0008811188924'
format='CD' imageurl='xxx0008811188924_p0_v1.jpg' label='Mca' name='Sublime Acoustic: Bradley Nowell & Friends'
price='7.9299999999999997' retailprice='9.9900000000000002' sku='8811188924'/>
<z:row artist='Lynyrd Skynyrd' buyurl='xxxFw%2Fgimme-back-my-bullets-lynyrd-skynyrd%2F67815%3Fean%3D0008811202323'
format='CD' imageurl='xxx0008811202323_p0_v1.jpg' label='Mca' name='Gimme Back My Bullets'
price='12.43' retailprice='13.99' sku='8811202323'/>
<z:row artist='Don Williams' buyurl='xxxFw%2F20th-century-masters-the-millennium-collection-the-best-of-don-williams-vol-1-don-williams%2F4012642%3Fean%3D0008811225025'
format='CD' imageurl='xxx0008811225025_p0_v1.jpg' label='Mca Nashville'
name='20th Century Masters - The Millennium Collection: The Best of Don Williams, Vol. 1' price='12.43' retailprice='13.99'
sku='8811225025'/>
<z:row artist='Donna Fargo' buyurl='xxxFw%2F20th-century-masters-the-millennium-collection-the-best-of-donna-fargo-donna-fargo%2F6176495%3Fean%3D0008817024325'
format='CD' imageurl='xxx0008817024325_p0_v1.jpg' label='Mca Nashville'
name='20th Century Masters - The Millennium Collection: The Best of Donna Fargo' price='7.9299999999999997' retailprice='9.9900000000000002'
sku='8817024325'/>
<z:row buyurl='xxxFw%2Fdvd-cimarron-city-the-complete-series%2F24124794%3Fean%3D0011301634368'
format='DVD' imageurl='xxx0011301634368_p0_v1.jpg' name='Cimarron City: the Complete Series'
price='33.5' publisher='Timeless Media' retailprice='34.990000000000002' sku='11301634368'/>
<z:row buyurl='xxxFw%2Fdvd-world-war-ii-in-color%2F25965326%3Fean%3D0011301661364'
format='DVD' imageurl='xxx0011301661364_p0_v1.jpg' name='World War Ii In Color'
price='11.31' publisher='Timeless Media' retailprice='11.99' sku='11301661364'/>
<z:row artist='Alison Krauss & Union Station' buyurl='xxxFw%2Fso-long-so-wrong-alison-krauss-union-station%2F268198%3Fean%3D0011661036529'
format='CD' imageurl='xxx0011661036529_p0_v1.jpg' label='Rounder / Umgd'
name='So Long So Wrong' price='10.02' retailprice='11.99' sku='11661036529'/>
</rs:data>
</xml>


[stanl]

I cannot find the xml or mdb I used (and haven't used Jet in over 10 years). However, for batch updating I would open the saved xml recordset with

Code (WINBATCH) Select Expand


oRS.Open(cOut,"Provider=MSPersist;",1,4,256)



[EDIT]


I'll try to put something together with ACE Provider

[JTaylor]

Okay.  Yes.  I used the RecordSet approach for opening as well but it doesn't appear to have any batch updating capabilities.   At least everything I have found assumes one is going to loop through it one by one.  Thanks.

Jim

[stanl]

Jim;


My bad. MsPersist is for disconnected recordsets so your text files would have to be in tables in the first place. Have you considered SQLite for updating?

[JTaylor]

Things will end up in a database table.   It requires some processing before that occurs so was investigating options.   One, of course, was to do the processing in the table rather than before...other than the sort, which significantly reduces load time.   Since I have to sort first it sort of naturally followed that doing the other processing made sense as well, as part of the processing involves deleting over 2.5 million of the records and adding/updating another field on the rest.   SQLite though might be a good intermediate tool since it would keep the load off of my production server for the processing and give me better tools than I have now.   Will be interested to see if there is a speed difference.   The current process is all text processing and then using a DOS sort.   JET.OLEDB has allowed me to do the sort and rearranging on the load which is a great improvement but too much data to filter as part of the select so still text processing on that front.   Will let you know if SQLite solves that problem in a more efficient way.   Thanks for the idea.

Jim