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

Importing CSV Files into MySQL with Custom Column Mapping

When importing a CSV file into a MySQL table, you may encounter differences between the column names in the CSV and the database table. To handle this, you can use custom column mapping during the import process.

The LOAD DATA INFILE syntax allows you to specify which CSV column gets imported into which database column. By default, each field in the CSV line maps to a column in the table in the order they appear. However, you can override this by providing a column list at the end of the LOAD DATA INFILE statement:

<code class="sql">LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);</code>

For example, if you have a CSV file with columns name, address, and email, and you want to import them into a database table contacts with columns first_name, last_name, and email, you would use the following query:

<code class="sql">LOAD DATA INFILE 'contacts.csv' INTO TABLE contacts (first_name, last_name, email);</code>

This query will map the name column in the CSV to the first_name column in the table, the address column to the last_name column, and the email column to the email column.

You can use this technique to handle any discrepancies between the column names in your CSV file and the database table, ensuring that the data is imported correctly.

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