0

Hello,

I am importing a .csv file in mysql. I am facing problem when it comes to date. Let me elaborate.

CaptureDate
20071101
20071101
20071101
20071101
20071101

This is data. But when i Load the file,
it displays only last record correctly but not the rest 4. Please help. Thanks in advance.

3
Contributors
5
Replies
8
Views
8 Years
Discussion Span
Last Post by rajuchacha007
0

Hello,

I am importing a in.csv file in mysql. I t contains one variable called CaptureDate.

load data infile 'C\in.csv' into table table name
lines terminated by '\n'
ignore 1 lines;

Once I executed, I got errors like row 1 doesn't contain data for all columns. I changed the data types from date to varchar,long and numeric but still not able to resolve he problem. Later I tried to use str_to_date(). But still not able to get it executed. Please advise. Thanks in advance.

0

I think your problem has to with with how the date is formatted in your csv file:

20071101

Ideally that should be:

2007-11-01

0

work around would be to

Create a column CaptureDate of type varchar or char
once import is done u can update the data and get it in date time format

alter table table1 add column CaptureDate_1 date;
update table1 set CaptureDate_1=date_format(CaptureDate,'%Y-%m-%d')
alter table table1 drop column CaptureDate;

0

Thanks Varmadba. I got the result with the same technique. What I did was
load data infile 'c:\in.csv' into table riamp_in_stage
-> fields terminated by ','
-> lines terminated by '\n'
-> (@CaptureDate)
-> set CaptureDate=str_to_date(@CaptureDate,'%Y%m%d');
Thank you very much for your response.

work around would be to

Create a column CaptureDate of type varchar or char
once import is done u can update the data and get it in date time format

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.