One idea I thought of, is for each line detecting the row type, doing a global search +replace on all commas to wrap each field in <field></field> tags, and each row in its rowtype tag, to make an XML document that I can use the xml functions on. Seems like would be less time on each line, and only 1 pass of text processing + XML parse (keeping 5 MB file in memory), but if a 5000 line CSV took 30 minutes a 60000 line file might take God knows how long :)
Anyway, we have a sample set of those flat files that I can experiment with tomorrow. will also try the sql bulk insert. thanks, - David On 2/13/06, Pete Ruckelshaus <[EMAIL PROTECTED]> wrote: > IME, CF is pretty bad at string parsing, especially for larger files. > I had a similar requirement (though I wasn't generating XML, I was > using SQL for data comparison & import) for a pretty straightforward > 5000 line CSV file, it took FOREVER to run (upwards of 30-45 minutes). > I tried importing the file into MS Access and then ran the same > script using SQL rather than file reads creating a query object, and > it ran in about 20 seconds. > > You might want to try a couple of test cases before investing too much > time in a single approach. > > Pete > > On 2/13/06, David <[EMAIL PROTECTED]> wrote: > > Hi All, > > > > I have a set of flat files, CSV data. Each file has 1-2 types of > > records, distinguished by a number in the first field in the row (row > > type ID: e.g., 20=parent row, 21=child row). Each row type has its own > > structure (though each file has only 1-2 of those). 22 files. Most > > have about 1000-10000 rows, one has 20,000 (1 MB), one has 60,000 rows > > (6 MB). > > > > We need to process this data, convert into a similar XML document, > > based on an XML schema we were given. Needs to happen periodically on > > request (whenever notified of updates). ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232185 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
