How Do I Map CSV Columns to MySQL Table Columns During Data Import using LOAD DATA INFILE?

Assigning Columns in CSV to MySQL Table Columns during Data Import

When importing data from a CSV file into MySQL using the LOAD DATA INFILE command, it's essential to align the order of columns in the file with the corresponding columns in the MySQL table for accurate data placement. To achieve this alignment automatically:

LOAD DATA INFILE Syntax with Column Mapping:

The LOAD DATA INFILE command supports a FIELDS clause that allows you to specify the order in which columns in the CSV file should be assigned to the MySQL table columns. The syntax is as follows:

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...);

Example:

Let's say you have a CSV file with columns in the order: name, age, address. And you want to import the data into a MySQL table with columns: age, name, address. You can use the following query:

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(age, name, address);

This query will automatically assign the columns in the CSV file to the correct columns in the MySQL table based on the order specified in the FIELDS clause.

Note for MySQL 8.0 Users:

The LOCAL keyword in the above syntax has security implications. In MySQL 8.0, LOCAL is set to False by default. If you encounter an error related to this, you can either disable the LOCAL capability or acknowledge the security risk by following the instructions in the MySQL documentation.

The above is the detailed content of How Do I Map CSV Columns to MySQL Table Columns During Data Import using LOAD DATA INFILE?. For more information, please follow other related articles on the PHP Chinese website!