How to Efficiently Import CSV Data into MySQL Using PHP and Avoid Common Issues?

Importing CSV Data Using PHP and MySQL

Problem and Questions

A user faced issues while importing CSV data into a MySQL database and posed the following questions:

1. The imported text values appear as "0" in the database.
2. How to handle data enclosed in quotation marks?
3. Ignoring the first few lines of a CSV file containing headers.
4. Preserving data format (decimal point) when importing into the database.
5. An "execution time exceeded" error occurred during a large data import.

Solution

1. Text Values Appearing as "0":

The data is probably being imported as integers in the database instead of strings. Ensure the database columns are of the correct type (e.g., TEXT or VARCHAR).

2. Handling Quoted Data:

Use the OPTIONALLY ENCLOSED BY clause in the LOAD DATA INFILE query to specify the enclosure character (e.g., ").

3. Ignoring Header Lines:

Add a LINES TERMINATED BY clause in the query and specify the line separator character (e.g., n). Then, manually ignore the desired number of initial lines before importing.

4. Preserving Data Format:

MySQL stores numeric values in their original format unless specified otherwise. Decimals will remain decimals in the database.

5. Execution Time Exceeded Error:

Increase the max_execution_time setting in PHP's configuration (e.g., in php.ini) to allow for longer execution times. For large imports, consider using a more efficient method like MySQL's LOAD DATA INFILE directly.

Alternative Solution

Instead of using PHP loops to import the data, MySQL's LOAD DATA INFILE feature can directly import CSV files into tables with a single SQL query:

$query = <<<'eof'
LOAD DATA INFILE '$fileName'
INTO TABLE tableName
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(field1,field2,field3,etc)
eof;

$db->query($query);

This method is faster and more efficient than PHP parsing loops.

The above is the detailed content of How to Efficiently Import CSV Data into MySQL Using PHP and Avoid Common Issues?. For more information, please follow other related articles on the PHP Chinese website!