How to Efficiently Import Large CSV Files into MySQL Using the Command Line?

Importing Large CSV Files into MySQL via Command Line

Importing vast .csv files, exceeding the limits of GUI tools like phpMyAdmin, necessitates the use of the command line. The following steps guide you through the process of importing a .csv file into a MySQL database using the mysqlimport utility.

Setting Column Names from the First CSV Row

To define the MySQL table's column names from the first row of the .csv file, follow these steps:

1. Execute the mysqlimport command using the --fields-terminated-by and --lines-terminated-by options to specify the delimiter and line separator for the CSV file.
2. Add the --skip-lines=1 option to skip the first row, as it is used for column names.
3. Specify the field names manually within parentheses in the fields clause after the into table keyword.

The command should resemble the following:

mysqlimport --fields-terminated-by=, \
--lines-terminated-by='\n' \
--skip-lines=1 \
-u username -p databaseName \
tablename restOfConfigFileOptions

Remember to replace username, databaseName, tablename, and restOfConfigFileOptions with the actual values. Refer to the MySQL documentation for additional command options.

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