LOAD DATA vs. mysqlimport: Which is the Best Way to Import CSV Files into MySQL?

Importing CSV files into MySQL: Delving into LOAD DATA and mysqlimport

The challenge of importing a CSV file into a MySQL table can arise when dealing with unnormalized data and multiple columns. To resolve this, it's crucial to consider the nuances of the import process.

The LOAD DATA Approach

LOAD DATA provides a straightforward method to import data from a CSV file. However, it's important to specify additional parameters to ensure proper handling of text blurbs that span multiple lines. Here's an example that addresses potential issues with line breaks:

LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

This revised statement addresses the potential problem by specifying line termination characters and ignoring the first line for column names.

The mysqlimport Utility

Another option for importing CSV files into MySQL is mysqlimport, a command-line tool specifically designed for this purpose. It offers a powerful and efficient way to import large datasets:

mysqlimport --ignore-lines=1 \
            --fields-terminated-by=, \
            --local -u root \
            -p Database \
             TableName.csv

In this command, "--ignore-lines=1" skips the first line of the CSV file (containing column names), "--fields-terminated-by=," specifies the field delimiter as a comma, "--local" ensures that the import occurs on the local machine, and "-u root -p" prompts for a root password before connecting to the database.

Additional Considerations

To customize the field delimiter, use the "--fields-terminated-by='t'" option for tab-delimited files. Furthermore, if the CSV file contains special characters that need escaping, employ the "--lines-terminated-by='r'" option for Windows line endings and "--lines-terminated-by='n'" for Unix line endings.

The above is the detailed content of LOAD DATA vs. mysqlimport: Which is the Best Way to Import CSV Files into MySQL?. For more information, please follow other related articles on the PHP Chinese website!