How Can I Successfully Import a CSV with Multi-Line Text Fields into MySQL?

Importing CSV Files into MySQL Tables

Loading CSV files into MySQL tables can be a straightforward task, but it can sometimes encounter complications due to formatting issues. One such issue arises when the CSV file contains multiple lines of text in some of its fields.

In the scenario presented, the user created a MySQL table with corresponding columns for each field in the CSV file, including VARCHAR(256) fields for text blurbs. However, when attempting to load the data using the LOAD DATA INFILE statement, the entire table was filled with NULL values.

The underlying issue stemmed from MySQL's default behavior of parsing new lines as row delimiters. To resolve this, the user specified additional parameters in the LOAD DATA statement, including:

• COLUMNS TERMINATED BY ',': Delimiter for columns
• OPTIONALLY ENCLOSED BY '"': Delimiter for text fields
• ESCAPED BY '"': Escape character for quotes within text fields
• LINES TERMINATED BY 'n': Delimiter for rows
• IGNORE 1 LINES: Skips the header line

While this approach allowed the user to load the correct number of records, some of the data remained in the wrong order. To address this, the user explored alternative methods for importing the CSV file.

One recommended method involved using the mysqlimport tool:

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

This tool allows for specifying various parameters similar to the LOAD DATA statement, including line and field delimiters. By using mysqlimport, the user can ensure that the data is loaded in the correct format and order.

The above is the detailed content of How Can I Successfully Import a CSV with Multi-Line Text Fields into MySQL?. For more information, please follow other related articles on the PHP Chinese website!