导入CSV到MySQL并转换日期 [英] import CSV to MySQL and convert date

问题描述

我知道这个问题之前已经提出，但由于某种原因，我还有运气。我试图导入一个CSV文件，并将日期字段转换为正确的mysql格式（YYYY-MM-DD）。我应该注意，csv中的一些记录对于日期值具有空值，。不知道这意味着什么。我还应该提到我已经在这个表中有记录。它不是空的。我尝试运行，但没有运气

I know this question has been asked before but for some reason I am having zero luck still. I am trying to import a CSV file and convert the date field to proper mysql format (YYYY-MM-DD). I should note that some of the records in the csv have have an empty value, '', for a date value. Not sure if that means anything. I also should mention that I already have recordd in this table. It is not empty. I tried running this but had no luck

load data local infile '/Users/Path/To/CSV/file.csv' 
into table donor fields terminated by ',' 
lines terminated by '\n' (@date,INSTRUCTIONS)
set date = STR_TO_DATE(@date, '%Y-%m-%d')

我所有的都返回NULL。任何想法？

I'm getting NULL returned for all of them. Any Ideas?

推荐答案

如果你这样做只有一次，并且表是空的，但首先更改您的表，以便日期列的类型为VARCHAR。然后运行 UPDATE表SET date = str_to_date（date，'％m /％d /％Y'），并将列转换回DATETIME或DATE。

If you're doing this only once, and the table is empty to start, you could run the import but first alter your table so the date column is of type VARCHAR. Then run UPDATE table SET date = str_to_date( date, '%m/%d/%Y'), and convert the column back to DATETIME or DATE.

您还可以添加第二个日期列，格式为DATE，导入到VARCHAR格式的第一个日期列，然后运行 UPDATE表SET date2 = str_to_date（date1，'％m /％d /％Y'），然后DROP varchar列。

You could alternatively add a second date column with format DATE, import into the first date column in format VARCHAR, and run UPDATE table SET date2 = str_to_date( date1, '%m/%d/%Y') and then DROP the varchar column.

第二个参数STR_TO_DATE（）是输入的格式，而不是生成的格式。所以在你的第二个参数，你指示MySQL，CSV中的日期格式是Y-m-d。如果它实际上是m / d / Y，你应该使用`STR_TO_DATE（@date_date，'％m /％d /％Y）'。

It's important to note that the second parameter of STR_TO_DATE() is the inputted format, not the resulting format. So in your second parameter, you're instructing MySQL that the date format in the CSV is Y-m-d. If it is in fact m/d/Y, you ought to be using `STR_TO_DATE(@date_date, '%m/%d/%Y)'.

希望这有助于...

这篇关于导入CSV到MySQL并转换日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！