How to Change String Date to MySQL Date Format at Time of Import of CSV Using MySQL's Load Data Local Infile

How to change string date to MySQL date format at time of import of CSV using MySQL's LOAD DATA LOCAL INFILE

You need to use the SET clause, along with a variable to reference the contents of the row at that column. In your column list, you assign your date column to a variable name. You can then use it in your SET statement. (Note, I haven't got MySQL in front of me to test this on.)

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(name, address, @var1)
set dateOfBirth = STR_TO_DATE(@var1, '%d-%b-%y')

See examples a way down the page at: http://mysql2.mirrors-r-us.net/doc/refman/5.1/en/load-data.html (Not sure why this page seems to differ from the main documentation in that it actually contains an example of SET usage.)

Change date format while loading with LOAD IN DATA FILE

You could convert the data like following:

SELECT STR_TO_DATE( '10/24/2013 5:27:05 PM', '%m/%d/%Y %I:%i:%s %p' )

and store it in a datetime field

edit:
LOAD data file can load pretty faster for 70K records.

LOAD DATA LOCAL INFILE '/filelocationfromroot' INTO TABLE `yourtablename` 
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(
`tablefieldforfirstcsvcolumn`,`tablefieldforsecondcsvcolumn`,...,@yourdatecolumn,
)
SET `datacolumnname`=STR_TO_DATE( @yourdatecolumn, '%m/%d/%Y %I:%i:%s %p' )

Does MySQL have a date parser that can guess at input format?

Does MySQL have a date parser that can guess at input format?

Unfortunately , it does not.

You are left with two choices.

1. Use string to store dates (which I highly don't recommend).
   You will have different date formats in the same column, which will be hard to convert to date using STR_TO_DATE() function.

2. Use Python utility called dateutil.parser.parse.

Even though you might have execution time issues I suggest using the Python utility.

How to convert date in .csv file into SQL format before mass insertion

Have you tried the following:

LOAD DATA LOCAL INFILE 'c:/scheduletest.csv' INTO TABLE game
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(@DATE_STR, `time`, `awayteam_id`, `hometeam_id`)
SET `date` = STR_TO_DATE(@DATE_STR, '%c/%e/%Y');

For more information, the documentation has details about the use of user variables with LOAD DATA (about half-way down - search for "User variables in the SET clause" in the page)

How can I change the date time format in my query

Check out the DATE_FORMAT and the STR_TO_DATE functions, it should do the trick.

---

Related Topics