How to Import a Large CSV into MySQL Using the Command Line and Specify the First Row as Column Names?

Importing a Massive CSV into MySQL via Command Line

In an attempt to import a colossal CSV file into MySQL, it becomes evident that PHPMyAdmin's limited upload size of 2MB presents a hurdle. To circumvent this restriction, the command-line approach emerges as the preferred solution.

The question arises: how to specify the first row of the CSV table as the column names in the MySQL table, a capability offered by PHPMyAdmin. Raj seeks guidance in finding the corresponding MySQL command-line equivalent.

To import the CSV file, Raj can utilize the following command:

load data local infile 'file.csv' into table table
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(column1, column2, column3,...)

Here, 'column1', 'column2', 'column3', and so on represent the actual table fields where the data should reside. The 'enclosed by' and 'lines terminated by' parameters are optional, particularly useful for handling columns enclosed within double quotes, as encountered in exports from applications like Excel.

To designate the first row as column names, Raj can exclude this row from the data being read by using the "ignore 1 lines" parameter. The modified command would look like this:

load data local infile 'file.csv' into table table
ignore 1 lines
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
(column1, column2, column3,...)

By utilizing this command-line approach, Raj can seamlessly import his large CSV file into MySQL, setting the first row as column names and tailoring the data loading process to his specific requirements.

The above is the detailed content of How to Import a Large CSV into MySQL Using the Command Line and Specify the First Row as Column Names?. For more information, please follow other related articles on the PHP Chinese website!