BULK INSERT in MySQL

[Marc Vanzegbroeck]

Hi,

I'm converting data from an existing DBF to MySQL. I have tables with more than 2.000.000 records.
Now I'm going to the top of the DBF, and with an Do While !eof() I skip to the dbf and insert each record in the SQL.
It is very slow (more than 2 hours in some cases).
Is there a faster way to do this?
In SQLite I use 'Begin immediate' and 'Commit' before the loop, what resulted in a faster execution, but it doesn't work in MySQL.
Also using 'Start stransaction' and 'commit' doesn't help.

Thanks

Marc

[ADutheil]

Read the values from the DBF to a temporary string and use INSERT statements with multiple VALUES lists to insert several rows at a time.
sql = "INSERT INTO your_table (field1, field2) VALUES ('string1', value1),('string2', value2),('string3', value3);"

You can also use a free tool like dbf2sql: http://www.tkinformidia.net/uploads/1/2/0/0/12005968/dbf2sql-3.0.zip

[Marc Vanzegbroeck]

André,

Thanks for the information. I will try to insert more records at the same time.

Regards,

Marc

[Marc Vanzegbroeck]

Hi,

I found a faster way to import/export into MySQL by using CSV-files.

The only problem I have is if I export to a CSV-file and do a append from into a DBF with the command

Code: Select all  Expand view

oSQL:query([SELECT * INTO OUTFILE 'C:/temp/TESTPLAN.CSV' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM planning])

only 1 record is imported into the DBF. It seems that the LINES TERMINATED BY '\n' command is only a LF and not CRLF.
Does anyone know how to export to a CSV with CRLF?

Thanks
Marc

[Marc Vanzegbroeck]

I found it!!

Code: Select all  Expand view

\r\n

Marc