How Can I Map CSV Columns to MySQL Table Columns When Importing Data?

Importing CSV Data into MySQL with Column Mapping

When importing data from a CSV file into a MySQL table, it's crucial to ensure that the columns in the file match the columns in the table. Otherwise, the data may be imported incorrectly.

To automatically assign CSV file columns to MySQL table columns, use the LOAD DATA INFILE command with the FIELDS clause. Here's an example:

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

In this syntax:

• FIELDS specifies the delimiter separating columns in the CSV file.
• ENCLOSED BY specifies the character enclosing field values.
• LINES specifies the delimiter separating rows in the CSV file.
• IGNORE 1 LINES skips the first line of the CSV file (which may contain column names).
• (col1, col2, ...) maps the CSV file columns to the corresponding table columns.

Note for MySQL 8.0 Users:

MySQL 8.0 disables the LOCAL keyword by default due to security concerns. If you see the error "ERROR 1148: The used command is not allowed with this MySQL version", follow the instructions provided in the MySQL documentation tooverwrite it. However, be aware that this does not resolve the security issue.

The above is the detailed content of How Can I Map CSV Columns to MySQL Table Columns When Importing Data?. For more information, please follow other related articles on the PHP Chinese website!