How to Automatically Assign Columns When Importing CSV Data into MySQL using LOAD DATA INFILE?

Assigning Columns During CSV Import with LOAD DATA INFILE

Importing data from a CSV file into MySQL using LOAD DATA INFILE can become challenging when the columns in the CSV file are not in the same order as the table columns in MySQL. To automatically assign columns corresponding to MySQL table columns, follow these steps:

Syntax for Importing Data with Auto Column Assignment

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

Parameters

• LOCAL INFILE: Specifies that the CSV file is located on the local system.
• FIELDS TERMINATED BY ',': Specifies that the data in the CSV file is separated by commas.
• ENCLOSED BY '"': Specifies that data values are enclosed in double quotes.
• LINES TERMINATED BY 'rn': Specifies that the lines in the CSV file are terminated by both carriage return and line feed characters.
• IGNORE 1 LINES: Ignores the first line of the CSV file, which may contain headers or other information.
• (col1, col2, col3...): Lists the names of the columns in the MySQL table in the order corresponding to their appearance in the CSV file.

Example

Consider a CSV file named "abc.csv" with data in the following format:

name,age,occupation
John Doe,25,Engineer
Jane Smith,30,Doctor

And a MySQL table named "employees" with the following structure:

CREATE TABLE employees (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  age INT NOT NULL,
  occupation VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

To import the data from "abc.csv" into the "employees" table with automatic column assignment, execute the following query:

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(name, age, occupation);

This query will automatically assign the columns in the CSV file to the corresponding columns in the "employees" table based on their order.

The above is the detailed content of How to Automatically Assign Columns When Importing CSV Data into MySQL using LOAD DATA INFILE?. For more information, please follow other related articles on the PHP Chinese website!