CODEWITHSUNDEEP
mysqlcsv
Twitch
Twitch

Reputation: 693

Difficulty importing CSV into MySQL

I have a CSV file that I'm trying to import into MySQL but one of the columns is being split into several columns.

Here is a sample of the CSV: 

Code,SpecificIssue,Issues_Id
"MEDICARE/MEDICAID","State Medicaid waivers",0
"EDUCATION","Distance Education, Accreditation, and Issues Affecting Access to Title IV Loans in Higher education Act reauthorization; issues affecting veterans access to education benefits.",1
"LAW ENFORCEMENT/CRIME/CRIMINAL JUSTICE","Issues related to law enforcement funding; S. 3182.",2
"TRANSPORTATION","Issues related to transportation improvements funding.  S. 3261.",2
"HEALTH ISSUES","Issues related to funding for health care outreach, S. 3230.",2

The first record is being split between "Distance Education" and "Accreditation", and the last record is being split between "outreach" and "S.".

I'm using the instructions provided at http://dev.mysql.com/doc/refman/5.1/en/load-data.html as a reference. The SQL import query is like so: 

load data local infile '/path/to/csv/example.csv' 
into table lobby 
fields terminated by ','
enclosed by '"'
lines terminated by '\n';

It seems to me that the enclosed by quotes instruction is being ignored, but I have no idea why or how to get around it.

Upvotes: 0

Views: 83

Answers (2)

thanhtd
thanhtd

Reputation: 391

My guess is your third column has number 0 without '"' enclosed. You may put '"' around them and try again.

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71384

Try to use OPTIONALLY ENCLOSED BY '"' since you do not have quotes on all values.

Without this you are going to get unexpected behavior.

Here is commentary from manual around CSV import:

LOAD DATA INFILE can be used to read files obtained from external sources. For example, many programs can export data in comma-separated values (CSV) format, such that lines have fields separated by commas and enclosed within double quotation marks, with an initial line of column names. If the lines in such a file are terminated by carriage return/newline pairs, the statement shown here illustrates the field- and line-handling options you would use to load the file:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

If the input values are not necessarily enclosed within quotation marks, use OPTIONALLY before the ENCLOSED BY keywords.

Note the last line.

Upvotes: 2

Related Questions