Home > Database > Mysql Tutorial > LOAD DATA vs. mysqlimport: Which is the Best Way to Import CSV Files into MySQL?

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

Linda Hamilton
Release: 2024-12-16 09:20:11
Original
797 people have browsed it

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

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

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!

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
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template