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.
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
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.