Importing data into RevDB
davidocoker at gmail.com
Fri Dec 4 17:53:22 EST 2009
>Depending on how big the files are and how quickly they need to be
>processed, I would recommend you build a scanner that toggles at least
>Flag 1 is "flgQuoteRunOn" as true or false
>Flag 2 is "flgEscapeChar" as true or false
>put false into flgQuoteRunOn
>put false into flgEscapeChar
>put "/" into escChar
>put quote into q
>put comma into c
>Now run a repeat loop to re-cast the strings into logical chunks.
>Afterwards, use human inspection to find those spots that cannot be
>converted with logic loops (see examples below)
>Be careful of a few things
> embedded commas, quotes, and escape chars
>15446,"gold,silver watch", 599.00
>15447,"gold,silver, 18" chain", 199.00
>15447,"gold,silver, 18 1/2" chain", 199.00
Thanks for the suggestions and example Jim!
Of course I'm using sample data to test with, but the real files that need to be worked with are pretty massive. To give you an idea of just how massive...
I have a subset of data that I've been working with for over a week now, massaging text, filing in blank fields (concatenation in Excel) and thought I had everything taken care of as of late last night. I found out this morning that they needed one more column of data merged into my "finished" file.
I'm running the update now from my local mySQL server and it appears that it will take somewhere around 28 hours to complete the run. Can you hear me say Yay! :-)
>Just so you know CSV is the second worst format ever invented.
>They are still searching for worst one, but have not found it yet.
<BIG GRIN> No question about it! </BIG GRIN>
More information about the Use-livecode