How to Import CSV Data into MySQL with Custom Column Mapping?

Import CSV Data into MySQL with Custom Column Mapping

The LOAD DATA INFILE command is a convenient method for importing data from a CSV file directly into a MySQL table. However, what if the column names in the CSV file do not match those in the target table?

Programmatic Solution

To programmatically import a CSV file with mismatched column names, specify a column list in the LOAD DATA INFILE statement. The column list defines the order in which the CSV columns should be mapped to the database columns.

LOAD DATA INFILE 'uniq.csv' INTO TABLE tblUniq
(uniqName, uniqCity, uniqComments)  -- Specify the column list in parentheses
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

In the above example, the CSV file contains three columns, and the column list specifies that the first column should be imported into the "uniqName" column, the second into "uniqCity", and the third into "uniqComments".

Alternative Method

If you prefer a visual approach, consider using a graphical client like HeidiSQL. This tool allows you to manually map the CSV columns to the database columns and generate an accurate LOAD DATA INFILE statement that includes the appropriate column list.

The above is the detailed content of How to Import CSV Data into MySQL with Custom Column Mapping?. For more information, please follow other related articles on the PHP Chinese website!