CODEWITHSUNDEEP
mysqlcsv
NicB
NicB

Reputation: 362

Mysql CSV Importing - Problems with Last Field

This is what the csv data looks like:

"COUNTRY_ALPHA2_CODE","COUNTRY_NUMERIC_CODE","COUNTRY_NAME","REGION_CODE","REGION_NAME"
"AD","020","Andorra","02","Canillo"
"AD","020","Andorra","03","Encamp"
"AD","020","Andorra","04","La Massana"
"AD","020","Andorra","05","Ordino"

When I try importing it using the following code:

LOAD DATA LOCAL INFILE 'COUNTRIES.CSV'
INTO TABLE countries
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
select * from countries;

I get very weird behavior on the last field, as if it was looking for the last field to have a comma.

+-------------------------+---------------------+---------------------+----------------------+
| COUNTRY_NAME            | COUNTRY_ALPHA2_CODE | COUNTRY_ALPHA3_CODE | COUNTRY_NUMERIC_CODE |
+-------------------------+---------------------+---------------------+----------------------+
| Afghanistan             | AF                  | AFG                 | 004"
"Ant           |
| Anguilla                | AI                  | AIA                 | 660"
"Alb           |
| Aland Islands           | AX                  | ALA                 | 248"
"Aze           |
| Andorra

I looked at examples of CSV importing and the data seemed to be in the same format without a comma at the end. Not sure how to tell mysql that the last field will not have comma.

Upvotes: 5

Views: 783

Answers (1)

NicB
NicB

Reputation: 362

OK, playing around with the "line terminated by" and removing the "escaped by" did the trick:

LOAD DATA LOCAL INFILE 'COUNTRIES.CSV'
INTO TABLE countries
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Returns

+-----------------+---------------------+---------------------+----------------------+
| COUNTRY_NAME    | COUNTRY_ALPHA2_CODE | COUNTRY_ALPHA3_CODE | COUNTRY_NUMERIC_CODE |
+-----------------+---------------------+---------------------+----------------------+
| Aruba           | AW                  | ABW                 | 533                  |
| Afghanistan     | AF                  | AFG                 | 004                  |
| Angola          | AO                  | AGO                 | 024                  |
| Anguilla        | AI                  | AIA                 | 660                  |

Upvotes: 3

Related Questions