Search code examples
mysqlsqlmysqlimport

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

Solution

  • 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