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.

Recommended Answers

Elaborate some more please.

How are importing the csv file into mysql?

Jump to Post

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

Jump to Post

All 5 Replies

Elaborate some more please.

How are importing the csv file into mysql?

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.

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

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;

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.