How Can I Efficiently Import Data from Excel or CSV Files into MySQL?

Importing Data from Excel Spreadsheets or CSV Files into MySQL

When dealing with complex Excel spreadsheets containing numerous fields, importing them into MySQL via CSV conversion and groovy scripts can become challenging due to field differentiation issues. To address this, consider employing the LOAD DATA INFILE statement.

LOAD DATA INFILE Statement

LOAD DATA INFILE allows direct data import from a CSV file into a MySQL table. Its syntax is as follows:

LOAD DATA INFILE '<file_path>'
INTO TABLE '<table_name>'
FIELDS TERMINATED BY '<delimiter>'
ENCLOSED BY '<enclosure>'
LINES TERMINATED BY '<line_delimiter>';

Example:

To import data from a CSV file named "data.csv" into a MySQL table "employee":

LOAD DATA INFILE 'data.csv'
INTO TABLE employee
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

In this example, ',' is the field delimiter, '"' is the enclosure character, and 'n' is the line delimiter.

Advantages of LOAD DATA INFILE

• Efficient: Bulk data loading is significantly faster compared to inserting records individually.
• Flexible: It supports various field delimiters, enclosure characters, and line delimiters.
• Compatible: LOAD DATA INFILE is supported in most MySQL versions.

Additional Considerations

• Ensure the CSV file is formatted correctly and matches the table schema.
• Handle special characters, such as apostrophes, correctly within the CSV file.
• Grant appropriate permissions to the user performing the import operation.

The above is the detailed content of How Can I Efficiently Import Data from Excel or CSV Files into MySQL?. For more information, please follow other related articles on the PHP Chinese website!