MYSQL import data from csv using LOAD DATA INFILE

Question

I am importing some data of 20,000 rows from a CSV file into MySQL.

Columns in the CSV file are in a different order than MySQL tables' columns. How can I automatically assign columns corresponding to MySQL table columns?

When I execute

LOAD DATA INFILE 'abc.csv' INTO TABLE abc

this query adds all data to the first column.

What is the auto syntax for importing data to MySQL?

Answer 1

You can use the LOAD DATA INFILE command to import a CSV file into a table.

Check the link MySQL - LOAD DATA INFILE.

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...);

For MySQL 8.0 users:

Using the LOCAL keyword holds security risks and as of MySQL 8.0 the LOCAL capability is set to False by default. You might see the error:

ERROR 1148: The used command is not allowed with this MySQL version

You can overwrite it by following the instructions in the documentation. Beware that such an overwrite does not solve the security issue, but rather is just an acknowledgment that you are aware and willing to take the risk.

Answer 2

You probably need to set the FIELDS TERMINATED BY ',' or whatever the delimiter happens to be.

For a CSV file, your statement should look like this:

LOAD DATA INFILE 'data.csv' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;