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

All 5 Replies

Member Avatar for iamthwee

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.

Member Avatar for iamthwee

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.