Geek Answers Handbook

How to convert date in CSV file to SQL format before bulk attachment

I have a csv file with several thousand game dates in it, but they are all in the format MM / DD / YYYY

2/27/2011,3:05 PM,26,14

(26 and 14 are the identifier of the #s command), and trying to put them in SQL like this just causes the PDF to fit in the date field of my table. This is the command I tried to use:

LOAD DATA LOCAL INFILE 'c:/scheduletest.csv' INTO TABLE game
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(`date`, `time`, `awayteam_id`, `hometeam_id`);

but then again, that would not have given the correct dates. Is there a way I can convert the date when she tries to insert it? I found another SO question similar to this, but I couldn't get it to work.

+5
source share
6 answers

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');

LOAD DATA ( - " SET" )

+7

csv , :

LOAD DATA INFILE 'file.txt'
INTO TABLE t1
(@datevar, @timevar, awayteam_id, hometeam_id)
SET date = STR_TO_DATE(@datevar, '%m/%d/%Y'),
SET time = etc etc etc;
+1

, , - . STR_TO_DATE, .

0

  • , varchar,

  • CSV

  • mysql, - :

    UPDATE table SET field = STR_TO_DATE(field, '%c/%e/%Y %H:%i');
0

, Excel TEXT. , , A2, = TEXT (A2, "yyyy-mm-dd hh: mm: ss" ). , , .

0

More articles:


All Articles