 |  |  |  |  |  |  |  |
Mysql: importing .csv file with date.
Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
 |
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.
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.
•
•
Join Date: Aug 2005
Posts: 5,266
Reputation: 
Solved Threads: 377
Elaborate some more please.
How are importing the csv file into mysql?
How are importing the csv file into mysql?
*Voted best profile in the world*
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 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.
•
•
Join Date: Aug 2005
Posts: 5,266
Reputation:
Solved Threads: 377
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
20071101
Ideally that should be:
2007-11-01
*Voted best profile in the world*
•
•
Join Date: Jun 2008
Posts: 79
Reputation:
Solved Threads: 8
Junior Poster in Training
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
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;
:- Varma
We are Happy to inform launch of a new site with loads of database related information Site offers wide range of functionality Forums,Blogs,Articles,Editorials and much more
http://www.sqllibrarian.info/
We are Happy to inform launch of a new site with loads of database related information Site offers wide range of functionality Forums,Blogs,Articles,Editorials and much more
http://www.sqllibrarian.info/
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.
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.
•
•
•
•
Originally Posted by varmadba 
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
|
Similar Threads
- insert csv file into mysql through php (PHP)
- Importing csv file to SQL Server Using VB.Net (VB.NET)
- Problems with exporting from a mysql database to a .csv (PHP)
- CSV file uploading into a MySQL Database? (PHP)
- Populating a MySQL table with data from a csv file (PHP)
- csv, convert date, mysql, php (PHP)
Other Threads in the MySQL Forum
- Previous Thread: Image as an argument to the stored procedure
- Next Thread: Moving MySQL Data from Linux to Windows Computer
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Latest MySQL Posts
- Today's Posts
- Unanswered Threads
Related Forum Features
agplv3 alfresco amazon api aws bizspark breathalyzer changingprices cmg communityjournalism contentmanagement contractors copyright count court crm data database design developer development distinct drupal dui ec2 email enter enterprise eudora facebook form foss gartner gnu government gpl groklaw groupware hiring hyperic images innerjoins insert ip joebrockmeier join journalism keyword keywords kickfire laptop law legal license licensing linux maintenance managing mariadb matchingcolumns metron micromanage microsoft microsoftexchange mindtouch montywidenius mozilla multiple mysql mysqlcolumnupdating mysqldatetimeordermax() mysqlindex mysqlinternalqueries mysqlquery mysqlsearch news open-xchange opendatabasealliance opengovernment opensource oracle penelope php priceupdating query referencedesign reorderingcolumns resultset select sharepoint simpledb sourcecode spotify sql sugarcrm syntax techsupport thunderbird transparency virtualization
