Home > Database > Mysql Tutorial > How to Import a CSV File with Headers into MySQL Using SQL?

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

DDD
Release: 2024-12-06 12:44:13
Original
706 people have browsed it

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)
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template