CODEWITHSUNDEEP
phpmysqlcsv
xnnr
xnnr

Reputation: 13

Import CSV separated by semicolons and delimited by quotes

I need your help to solve a problem of importing .cv local files to a MariaDB 10.3 database. On my server I am running PHP7.2.

To import use a PHP script with the following statement:

exec("mysql -u Myuser -pMySrtongPassword -e \"USE MyDatabase;
TRUNCATE MyTable;
LOAD DATA LOCAL INFILE '/var/www/html/upload/file.csv' IGNORE  INTO TABLE MyTable FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES ;\"; ");

This works well for most of the .csv files that I need to import. However, I have some .csv files that have the following format:

"field1"; "field2"; ..... "fieldN";

They are separated by semicolons and delimited by quotes.

Does anyone have any ideas on how to import these files correctly?

Upvotes: 1

Views: 399

Answers (1)

nbk
nbk

Reputation: 49373

Add a OPTIONALLY ENCLOSED BY '\"' to the `LOAD DATA

TRUNCATE MyTable;
LOAD DATA LOCAL INFILE '/var/www/html/upload/file.csv' IGNORE  INTO TABLE MyTable FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES ;

Upvotes: 1

Related Questions