CODEWITHSUNDEEP
mysqlsqlmysqlimport
QGA
QGA

Reputation: 3192

Import dates in mysql

I want to import a csv file into a database

Header id,name,surname,date1,date2

Data 10001,Bob,Roberts,03/06/2007 15:18:25.10,03/06/2007 15:18:29.19

This file has millions of rows and in order to import it I used the following command:

mysqlimport  --ignore-lines=1 --fields-terminated-by=, --columns='id,name,surname,date1,date2' --local -u root -p Database /home/server/Desktop/data.csv

My problem is that when I try to import the file dates are not stored properly and they look like this:

'0000-00-00 00:00:00'

I tried many things but nothing works. I suppose the problem is given by the fact that the time has milliseconds and there is a dot rather than a colon at the end of the string.

My dates are stored in a timestamp variable

Can you help me please

Thanks

Upvotes: 2

Views: 1745

Answers (3)

QGA
QGA

Reputation: 3192

I have played with this issue for a bit and I solved it converting my dates with an awk script and upgrading mysql to version 5.6 which supports milliseconds

Thanks anyway

Upvotes: 0

Caffé
Caffé

Reputation: 1171

Your dates aren't in the MySql standard format, which is "YYYY-mm-dd HH:MM:SS".

Mysqlimport should be able to accept some date conversion function among the command line parameters, like you would do if you were using "LOAD DATA" command (which is used by mysqlimport utility), but at this time it isn't:

http://bugs.mysql.com/bug.php?id=55666

Here's some way to workaround this:

http://blog.dgaspar.com/2010/08/01/the-set-clause/

If you want to do this conversion at one step and don't want to use that workaround, you will need to directly use "LOAD DATA" command:

load data infile '/tmp/xxx.dat' 
into table xxx 
fields terminated by '|' 
lines terminated by '\n' 
(col1, 
col2, 
@col3, 
@col4, 
col5) 
set 
col3 = str_to_date(@col3, '%m/%d/%Y'), 
col4 = str_to_date(@col4, '%d/%m/%Y')

I got this code from: http://dev.mysql.com/doc/refman/5.1/en/load-data.html#c8828

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270793

I would suggest that you import the data into a staging table, where all the columns are strings (say varchar(255). Then do the type conversion in MySQL to the final form. This will make it much easier to debug the code and to figure out the exact conversion you need.

You will be able to use str_to_date() to do the conversion.

Upvotes: 1

Related Questions