CSV File to MySQL Database

Question

I have a CSV file in the following format:

"id","code","date","address","complete_url"

Example data from the CSV:

"144003286","ICXT","20100104","NJ","http://www.orhancanceylan.com"
"144003286","SEED","20100104","NY","http://www.erbileren.com"
"144003286","ANX","20100104","CA","http://www.foursquareme.com"
"144003286","AIG","20100104","NJ","http://www.orhancanceylan.com"

I want to load all of the data into MySQL. I tried the following command:

LOAD DATA LOCAL INFILE 'C:\\Users\\pb\\Documents/data.csv' 
INTO TABLE new_table
FIELDS TERMINATED BY '","' ENCLOSED BY '"'
LINES TERMINATED BY '\n'

And I received the following error:

1265 Data truncated for column 'id' at row 1
 1261 Row 1 doesn't contain data for all columns
 1261 Row 1 doesn't contain data for all columns
 1261 Row 1 doesn't contain data for all columns
 1261 Row 1 doesn't contain data for all columns
 Records: 1  Deleted: 0  Skipped: 0  Warnings: 5    3.620 sec

What is wrong with my code or CSV?

Joe · Accepted Answer · 2012-02-11 03:12:06Z

3

Changed FIELDS TERMINATED BY from '","' to ','

answered Feb 11, 2012 at 3:12

Joe

15.8k4 gold badges52 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CanCeylan Over a year ago

Thank you very much ! I have a question about the output. It says 1265 Data truncated for column 'code' at row 1 1265 Data truncated for column 'complete_url' at row 2 1265 Data truncated for column 'complete_url' at row 3 1265 Data truncated for column 'complete_url' at row 4 1265 Data truncated for column 'complete_url' at row 5 What does it mean ?

Joe Over a year ago

The max-length of the field in your database is too short, so not all the data could be inserted (hence, some was truncated)

CanCeylan Over a year ago

How shold I inspect the records that are not fitting my schema, because I have thousands of data I couldn't see them by looking

Collectives™ on Stack Overflow

CSV File to MySQL Database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related