How to Import Large CSV Files into MySQL via Command Line, Handling Header Rows?

Loading CSV Data into MySQL via Command Line: Overcoming File Size Limitations

Importing large CSV files into MySQL can be challenging, especially if they exceed the file size limit for PHPMyAdmin. For such scenarios, resorting to the command line is a suitable solution.

To import a CSV file and specify the first row as column names, you can utilize the mysqlimport utility, as described in the documentation. However, to execute this with the desired specification, consider the following command:

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

In this command, the following options are essential:

• file.csv: Replace this with the actual CSV file path.
• table: Specify the target table name in your database.
• fields terminated by ',': This option sets the field delimiter within the CSV file.
• enclosed by '"' (optional): If your CSV file contains double-quote enclosed columns (e.g., Excel exports), this parameter helps identify them.
• lines terminated by 'n' (optional): If necessary, specify the line termination sequence in your CSV file.
• ignore 1 lines: This option tells MySQL to skip the first row in the CSV file, which you want to use as column names.
• (column1, column2, column3,...): Replace this with the actual table structure, including the column names.

By utilizing this modified command, you can import your large CSV files into MySQL while also ensuring that the first row is treated as the column names. For more detailed information, refer to the official MySQL manual.

The above is the detailed content of How to Import Large CSV Files into MySQL via Command Line, Handling Header Rows?. For more information, please follow other related articles on the PHP Chinese website!