使用Sequel Pro在导入期间如何将字符串更改为日期? [英] How to change a string to a date during import using Sequel Pro?
问题描述
我试图使用Sequel Pro将文件导入MySQL表。
I'm trying to import a file into a MySQL table using Sequel Pro.
我知道我需要使用STR_TO_DATE,但是我找不到正确的语法。
I know I need to use STR_TO_DATE, but I can't figure out the right syntax.
我每一行都收到以下错误:
I'm getting a bunch of these errors for each row:
[ERROR in row 1] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET date = STR_TO_DATE(@'11/1/11', '%m/%d/%Y');,'Amazon','USD')' at line 2
这是我在做什么:
1文件>导入。文件出现,CSV中的日期字段是第14行:
1 File > Import. The file comes up and the date field in the CSV is row 14:
2)选择日期>添加表达式
2) Select Date > Add expression
3)在表达式窗口中,添加这段代码:
3) In the Expression window, add this code:
$14, SET date = STR_TO_DATE(@$14, '%m/%d/%Y');
4)获取此结果:
5)获取上面的错误。什么是正确的语法?
5) Get error above. What is the right syntax?
给您一个导入我的表的想法可能会有帮助:
It may be helpful to give you an idea of the table I'm importing into:
CREATE TABLE `Amazon_copy4` (
`key` int(11) unsigned NOT NULL AUTO_INCREMENT,
`Title` varchar(255) DEFAULT NULL,
`Author` varchar(255) DEFAULT NULL,
`ASIN` varchar(255) DEFAULT NULL,
`Units Sold` int(11) DEFAULT NULL,
`Units Refunded` int(11) DEFAULT NULL,
`Net Units Sold or KU/KOLL Units**[1]` int(11) DEFAULT NULL,
`Royalty Type[2]` varchar(255) DEFAULT NULL,
`Transaction Type*[3]` varchar(255) DEFAULT NULL,
`Avg. List Price without VAT` decimal(19,2) DEFAULT NULL,
`Average File Size` float(5,2) DEFAULT NULL,
`Avg. Offer Price without VAT` varchar(255) DEFAULT NULL,
`Average Delivery Cost` varchar(255) DEFAULT NULL,
`Royalty` decimal(19,2) DEFAULT NULL,
`date` date DEFAULT NULL,
`country` varchar(255) DEFAULT NULL,
`currency` varchar(255) DEFAULT NULL,
PRIMARY KEY (`key`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
在错误消息中, Amazon
code> USD 是以下字段的值( country
和 currency
)。
In the error message, Amazon
and USD
are values for the fields that follow date (country
and currency
) in each row.
提前感谢!
推荐答案
我想出来了。
需要两件事获取UI进行导入。
It takes two things to get the UI to do the import.
1)在表达式窗口中,这是要使用的语法:
1) In the expression window, this is the syntax to use:
STR_TO_DATE(@$14,'%m/%d/%Y')
SET date =
部分,只定义STR_TO_DATE()的括号内的行。
So drop the SET date =
part and only define the row inside the parens for STR_TO_DATE().
2)另外你必须清除使用上次修改的值的复选框。
2) Also you have to clear the checkbox for Use last edited value.
看起来像这样:
一旦你单击确定,CSV导入如下所示:
Once you click OK, the CSV import looks like this:
然后...
是的!
我希望这有助于某人。
I hope this helps someone.
这篇关于使用Sequel Pro在导入期间如何将字符串更改为日期?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!