MYSQL import data from csv using LOAD DATA INFILE

Question

I am importing some data of 20000 rows from a CSV file into Mysql.

Columns in the CSV are in a different order than MySQL table's columns. How to 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.

Please suggest auto syntax for importing data to Mysql.

Answer 1

You can use LOAD DATA INFILE command to import csv file into table.

Check this 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 hold 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 docs. Beware that such overwrite does not solve the security issue but rather just an acknowledge that you are aware and willing to take the risk.