Difference between CSV import and CSV using LOAD DATA?

Question

In phpMyAdmin there are two options to import a CSV file.

One is CSV. The other is CSV using LOAD DATA.

What's the difference between these two? Is there an advantage to using one over the other?

Answer 1

As stated above the LOAD DATA option is actually telling phpMyAdmin to use the MySQL command to let MySQL parse and load the file rather than phpMyAdmin parsing it first.

As also stated above, giving MySQL access to load the file can be dangerous if you don't feel 100% secure about the source and accuracy of the file it's self. It's like using a php form with no sql injection protection to insert data.

However, in some cases phpMyAdmin does not format the data correctly or has trouble parsing it when the regular CSV" option is used. This will cause un-explained errors such as "invalid format on line N" or "incorrect field count on line N" Those might not be exact error messages since I'm not logged into phpMyAdmin at the moment. In these cases the LOAD DATA option can be used to get passed the error. I think the extra option of Use local keyword has to do with making sure the correct commands for that specific version of MySQL on the local server is used. Not sure about the last part though.

Something to keep in mind is also the size of the file (number of lines being imported) I have had to break down a 1600 line file into smaller files even when using the LOAD DATA option in order to get it to go through. It gave no errors but the "affected rows" was incorrect when the file was too big.

Answer 2

To add to the other replies: the "CSV" one insists you have exactly the same amount of columns in the text file and the table. "CSV using LOAD DATA" does not.

Answer 3

The first option will have phpMyAdmin parse the CSV file itself and then generate and execute the SQL to insert the data. The second option will let MySQL take care of loading, processing, and inserting the data.

Both options (should) behave the same way, but the LOAD DATA INFILE option is generally much faster, and you don't have to worry about PHP's memory/execution time limits. The only problem is that it isn't supported by all configurations because there are security implications for giving MySQL access to the uploaded files, and as such it is often disabled (ex. shared hosting).