Have the exporter create a pipe delimited file and drop it in the right place
set a cron to look for that file and the action is a directdbms that has
<x-tad-bigger>
LOAD DATA INFILE '<@webroot>uploads/uploadfile.txt'
REPLACE
INTO TABLE my_table
FIELDS TERMINATED BY '|'
LINES TERMINATED BY ';'
</x-tad-bigger>
which goes very very fast.
Thank you.
put the arrays away.
On May 14, 2004, at 1:08 PM, Dave Shelley wrote:
<x-tad-bigger>Roland,</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>I wouldn’t recommend parsing the file into an array. It would be better to use the MySQL command LOAD DATA INFILE to import the data. If you need to massage the data, you can load it into a temporary table, process it, then move it to your production table.</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>I did this on a project recently. Importing >40K records, massaging the data, updating changed records, inserting new records and deleting old ones. It all ran in under a minute from a Witango cron job.</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>You will also want to use a delimiter that does not appear anywhere in the data. A vertical bar is a good option.</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>Dave Shelley</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger>
<x-tad-bigger>-----Original Message-----</x-tad-bigger>
<x-tad-bigger>From:</x-tad-bigger><x-tad-bigger> Roland Dumas [mailto:[EMAIL PROTECTED]</x-tad-bigger>
<x-tad-bigger> </x-tad-bigger><x-tad-bigger>Sent:</x-tad-bigger><x-tad-bigger> May 14, 2004 3:46 PM</x-tad-bigger>
<x-tad-bigger>To:</x-tad-bigger><x-tad-bigger> [EMAIL PROTECTED]</x-tad-bigger>
<x-tad-bigger>Subject:</x-tad-bigger><x-tad-bigger> Re: Witango-Talk: best practice for uploading from file to db</x-tad-bigger>
Perhaps my problem is not being able to make the delimiters change from the defaults. No matter what I set the delimiters to be (I'm trying a pipe delimiter here), the array attributes don't override the defaults.
<@ASSIGNrequest$newarrayvalue="<@ARRAYvalue='@@request$fromFile'>"CDELIM=|RDELIM=;>
On May 14, 2004, at 11:26 AM, Roland Dumas wrote:
System A exports a file that has many records, 10K to 15K. Records have fields that are text and fields that are numeric.
That file will be read by witango and turned into records to add to a database.
I can have the exported file in any text format. (CSV, different delimiters, quoted values, whatever)
I did a couple of quick attempts to read the file, turn it into an array, and ran into issues. No matter what I set the delimiters to, a comma in a field triggers errors when creating the array.
So, rather than stumble into the right approach, I thought I'd ask the list for what the best practices are for creating uploaders like this.
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
