How Can I Easily Import a CSV File into a MySQL Table Using SQL?

Import a CSV File into a MySQL Table with Ease

Importing a CSV file into a MySQL table is a practical task that can often arise in various data manipulation scenarios. This question-and-answer thread delves into the process, providing a solution that goes beyond relying on a GUI interface.

To initiate the import, it's crucial to transform an Excel file into a CSV format. Remember to remove any headers or empty data from the resulting CSV file.

Now, the import process can be executed using the following SQL syntax:

load data local infile 'uniq.csv' into table tblUniq fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(uniqName, uniqCity, uniqComments)

This command reads the CSV file into the MySQL table named tblUniq, with each row representing a record and each field being separated by a comma (`,).

In cases where the CSV file contains a header row that should be used as column names, the following statement can be employed:

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

By skipping the first line (IGNORE 1 LINES), this command uses the data from the second row onwards to automatically create the table structure and assign column names.

Ultimately, this detailed explanation provides a comprehensive solution for importing CSV files into MySQL tables, addressing the user's specific requirement to use the first row of data as column names.

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