在导入到mysql时修改CSV字段 [英] Modify CSV field on import to mysql
问题描述
我正在尝试将CSV文件导入到mysql数据库。
I'm trying to import a CSV file to a mysql database.
CSV文件包含以下格式的日期:
The CSV file contains (amongst other things) dates in the following format:
"2010-04-31 17:43:12"
我的第一种方法是使用以下.sql脚本:
My first approach was to use the following .sql script:
USE test;
LOAD DATA INFILE '/tmp/test.cvs'
replace INTO TABLE test_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(...,mydate,...);
这不起作用,因为双引号使字段2010-04-31 17:43 :12一个字符串。
所以我想,我可以使用
which doesn't work because the double quotes make the field "2010-04-31 17:43:12" a string. So i figured out i can convert it to DATETIME format using
select STR_TO_DATE("2010-04-31 17:43:12",'(%Y-%c-%e %H:%i:%S)') AS NewDateTime
这个查询在它自己上工作正常,但我想知道是否有一种方法在导入时即时转换字符串。以下内容之一:
That query works fine on it's own but I was wondering if there's a way of converting the string on the fly while importing. Something amongst the following:
...
LINES TERMINATED BY '\n'
(...,STR_TO_DATE(mydate,'(%Y-%c-%e %H:%i:%S)') AS NewDateTime,...);
推荐答案
您需要指定列列表,然后使用SET命令应用STR_TO_DATE:
You need to specify the column list, and then use the SET command to apply the STR_TO_DATE:
(@date,column2,column3,column4,column5,column6,)
SET mydate = STR_TO_DATE(@date, '%Y-%c-%e %H:%i:%S')
相关:
- http://forums.mysql.com/read.php?10,136269,136269#msg-136269
这篇关于在导入到mysql时修改CSV字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!