MySQL loading data from a CSV file
Hello everybody,
I have a CSV file which looks like this:
ITIN_ID,"YEAR","QUARTER","ORIGIN","ROUNDTRIP","ITIN_FARE",
200731,2007,3,"ABE",0.00,108.00,
200732,2007,3,"ABE",0.00,199.00,
200733,2007,3,"ABE",0.00,259.00,
200734,2007,3,"ABE",0.00,329.00,
200735,2007,3,"ABE",0.00,433.00,
200736,2007,3,"ABE",0.00,597.00,
200737,2007,3,"ABE",0.00,989.00,
200738,2007,3,"ABE",1.00,5.00,
200739,2007,3,"ABE",1.00,227.00,
2007310,2007,3,"ABE",1.00,387.00,
2007311,2007,3,"ABE",1.00,467.00,
2007312,2007,3,"ABE",1.00,482.00,
2007313,2007,3,"ABE",1.00,507.00,
2007314,2007,3,"ABE",1.00,517.00,
2007315,2007,3,"ABE",1.00,527.00,
2007316,2007,3,"ABE",1.00,533.00,
Previously, I created the table ticket20071:
Create table ticket20071 (ITIN_ID varchar(20), YEAR int, QUARTER int, ORIGIN char(3), ROUNDTRIP double, ITIN_FARE double);
I want to load the data from my CSV file into the MySQL table ticket20071.
So for that, I wrote this:
Load data infile 'C:/Users/jocelyne/Desktop/DB1B_Ticket/2007_3_DB1B_TICKET.csv' into table ticket20071
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\r\n'
Ignore 1 lines
(ITIN_ID, YEAR, QUARTER, ORIGIN, ROUNDTRIP, ITIN_FARE);
I got this error:
ERROR 1406 (22001): Data too long for column 'ITIN_ID' at row 1.
what is the problem?
I thought perhaps I made a mistake in using Data load infile or it is my CSV file which has a problem.
Thanks in advance for your reply
Billie