RE: Witango-Talk: best practice for uploading from file to db

[Dave Shelley]

There are a couple of problems with putting that much data into an array. 1)       you have to loop through 10-15,000 rows doing inserts 2)       if one record is bad (ie contains a delimiter or such) your @array tag will break   You can trigger a MySQL import from Witango. We used a .bat that did other thing, like backing up the db. Then did: c:\mysql\bin\mysql --user=xxxx --database=xxxx <c:\sql\import.sql   import.sql contained the SQL statements to do the import, including load data infile 'c:\filepath.txt' into table import_table fields terminated by '|';   The file format is fairly flexible. Check your mySQL reference docs.   Dave   -----Original Message----- From: Roland Dumas [mailto:[EMAIL PROTECTED] Sent: May 14, 2004 4:33 PM To: [EMAIL PROTECTED] Subject: Re: Witango-Talk: best practice for uploading from file to db   On May 14, 2004, at 1:08 PM, Dave Shelley wrote: Roland,   I wouldn’t recommend parsing the file into an array. how come? It would be better to use the MySQL command LOAD DATA INFILE to import the data. Can I send the mysql command from witango? What's the file format that the data would have to live in? If you need to massage the data, you can load it into a temporary table, process it, then move it to your production table.   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.   You will also want to use a delimiter that does not appear anywhere in the data. A vertical bar is a good option. for mucking with arrays yes. That's what I was trying to do that Scott made simple. (Since I've not done an uploader before, I'm in search of a 'best practice' guide. If going via arrays is stupid or limiting, then it's nice to know not to go that route.) ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf