How to Import CSV Files into MySQL with Custom Column Names

Importing CSV Files into MySQL with Different Column Names

When importing CSV files into MySQL, it's possible to encounter a scenario where the column names in the CSV file differ from those in the database table. This requires a specific approach to ensure proper data mapping.

Load Data INFILE Syntax

To load a CSV file into a MySQL table, you can use the LOAD DATA INFILE statement. If you do not specify a column list at the end of the statement, the input lines are expected to contain fields for each table column.

Custom Column Mapping

However, when the column names in the CSV file are different from those in the database, you can specify a custom column list to map the input data to the correct columns. The LOAD DATA INFILE syntax allows you to specify which CSV column gets imported into which database column.

For example, consider the following CSV file with column names "name", "city", and "comments":

<code class="csv">uniqName,uniqCity,uniqComments
John,New York,Excellent programmer
Jane,London,Great designer</code>

And the following database table with column names "name", "location", and "skills":

<code class="sql">CREATE TABLE tblUniq (
  name VARCHAR(255),
  location VARCHAR(255),
  skills TEXT
);</code>

To import the CSV file into the table with the correct column mapping, you can use the following LOAD DATA INFILE statement:

<code class="sql">LOAD DATA LOCAL INFILE 'uniq.csv' 
INTO TABLE tblUniq 
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(name, location, skills);</code>

By specifying the column list (name, location, skills) at the end of the statement, you ensure that the "uniqName" column in the CSV file is imported into the "name" column in the database, the "uniqCity" column into the "location" column, and the "uniqComments" column into the "skills" column.

This approach allows you to import CSV files with different column names into MySQL tables, ensuring proper data mapping without manual intervention.

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