How to Import a CSV File with Headers into MySQL Using SQL?

Importing CSV Data into MySQL with Column Headers

Importing data from a CSV file into a MySQL table is a common task. While there are tools that can execute this operation with a GUI, many users prefer a command-line approach. In this article, we will explore a method that utilizes SQL syntax to import a CSV file, including the first row as column names.

Importing CSV Data

To import a CSV file into MySQL, use the following modified LOAD DATA syntax:

LOAD DATA LOCAL INFILE 'uniq.csv'
INTO TABLE tblUniq
FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(uniqName, uniqCity, uniqComments)

Explanation:

• LOAD DATA LOCAL INFILE: Specifies the path to the CSV file on your local machine.
• INTO TABLE tblUniq: Specifies the name of the target table to receive the imported data.
• FIELDS: Configures the data delimiters and field enclosure characters. In this case, it assumes commas as delimiters and double quotes as enclosure characters.
• LINES: Defines the newline character ('n') as the line terminator.
• IGNORE 1 LINES: Skips the first line of the CSV file, which contains the column names.

Alternative Approach

If this method does not work for you, consider writing a script to extract the column names from the CSV file and create a SQL statement that dynamically imports the data with the correct column headers.

The above is the detailed content of How to Import a CSV File with Headers into MySQL Using SQL?. For more information, please follow other related articles on the PHP Chinese website!