如何在大量插入之前将.csv文件中的日期转换为SQL格式 [英] How to convert date in .csv file into SQL format before mass insertion

问题描述

我有一个带有几千个游戏日期的csv文件，但它们都是MM/DD/YYYY格式

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

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

(26和14是团队ID#)，并试图将其放入SQL中，就像那样，导致将通讯录放入表格的日期字段中.这是我尝试使用的命令:

(26 and 14 are team id #s), and trying to put them into SQL like that just results in 0000-00-00 being put into the date field of my table. This is the command I tried using:

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 again, it wouldn't do the dates right. Is there a way I can have it convert the date as it tries to insert it? I found another SO question similar to this, but I couldn't get it to work.

推荐答案

您是否尝试过以下方法:

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子句中的用户变量")

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)

这篇关于如何在大量插入之前将.csv文件中的日期转换为SQL格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！