CODEWITHSUNDEEP
phpmysqlcsv
Andurit
Andurit

Reputation: 5762

Import CSV file with LOAD DATA LOCAL INFILE

i have pretty big .csv file with data exported from one database (i'm not able to export it as anything else). And i try to import it to my mysql database.

But how ever i try I'm getting errors. First of all I don't have SSH on database server so I'm not able to change anything on apache site , for PHP my admin is file too big, if i try to use LOAD DATA INFILE I'm importing blanked rows.

Is there a chance somebody can help me with that?

CSV file example:

"Manufacturer","Main Type","Option Code","Option Description 1"
"JD" "50" "A1" "OD POČÁTKU VÝROBY"
"JD" "50" "A2" "OD VIN 340001"
"JD" "50" "A3" "OD VIN 440000"
"JD" "50" "A4" "OD VIN 440001"

Only possible option which work for me a bit:

LOAD DATA LOCAL INFILE 'solic2.txt' INTO TABLE solic_code FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

p.s. I was able to do that in my localhost via HeidySql but as soon as server have blocked ports I'm not able to do the same for server site..

Upvotes: 1

Views: 2002

Answers (1)

Giles
Giles

Reputation: 1667

Try this:

LOAD DATA LOCAL INFILE 'solic2.txt' INTO TABLE solic_code FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

This is a csv (comma separated) rather than tab separated, which is what you set with \t.

To make sure it will be using UTF-8:

LOAD DATA LOCAL INFILE 'solic2.txt' INTO TABLE solic_code  CHARACTER SET UTF8 FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

Upvotes: 3

Related Questions