How Can I Programmatically Import CSV Data into MySQL, Mapping CSV Columns to Database Columns?

Importing CSV Data into MySQL Programmatically

When importing a CSV file into a MySQL database, it is common to encounter differences between the CSV column names and the database table column names. If you wish to import the data programmatically, you can specify which CSV column maps to which database column.

LOAD DATA INFILE Syntax

The LOAD DATA INFILE syntax supports specifying a column list in the target table:

LOAD DATA INFILE 'file.csv' INTO TABLE table_name (column1, column2, ...);

This allows you to map CSV columns to specific database columns, regardless of their order or alignment.

Using a Graphical Client

Alternatively, you can use a graphical client like HeidiSQL to simplify the process. HeidiSQL can generate a SQL query that handles the column mapping based on a graphical preview. Simply arrange the CSV columns in the desired order and copy the generated query into your program.

Example

To import a CSV file with columns named "key", "value", and "timestamp" into a database table named "my_data" with columns named "id", "data", and "created_at", you would use the following query:

LOAD DATA INFILE 'file.csv' INTO TABLE my_data (id, data, created_at)
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

By specifying the column list, you ensure that the data is imported correctly and aligned with the database table schema.

The above is the detailed content of How Can I Programmatically Import CSV Data into MySQL, Mapping CSV Columns to Database Columns?. For more information, please follow other related articles on the PHP Chinese website!