找到你要的答案

Q:How to change a string to a date during import using Sequel Pro?

Q:如何改变字符串的日期在导入过程中使用续集Pro?

I'm trying to import a file into a MySQL table using Sequel Pro.

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

Here is what I'm doing:

1 File > Import. The file comes up and the date field in the CSV is row 14:

2) Select Date > Add expression

3) In the Expression window, add this code:

$14, SET date = STR_TO_DATE(@$14, '%m/%d/%Y');

4) Get this result:

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;

In the error message, Amazon and USD are values for the fields that follow date (country and currency) in each row.

Thanks in advance!

我想一个文件导入到MySQL表使用续集Pro。

我知道我需要用str_to_date,但是我不知道正确的语法。

我得到了一系列这些错误的每一行:

[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文件& gt;进口。该文件是在CSV的日期字段是14排:

2)选择日期& gt;添加表情

3)在表达式窗口中,添加此代码:

$14, SET date = STR_TO_DATE(@$14, '%m/%d/%Y');

4)得到这个结果:

5)获得以上错误。什么是正确的语法?

这可能有助于给你一个想法的表我进口到:

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和美元是在每个行中遵循日期(国家和货币)的字段的值。

先谢谢了。

answer1: 回答1:

I figured it out.

It takes two things to get the UI to do the import.

1) In the expression window, this is the syntax to use:

STR_TO_DATE(@$14,'%m/%d/%Y') 

So drop the SET date =part and only define the row inside the parens for STR_TO_DATE().

2) Also you have to clear the checkbox for Use last edited value.

It looks like this:

Once you click OK, the CSV import looks like this:

And then...

Yes!

I hope this helps someone.

我想通了。

需要两件事情才能让UI做导入。

1)在表达式窗口中,这是使用的语法:

STR_TO_DATE(@$14,'%m/%d/%Y') 

所以放弃设置日期=部分仅排在括号定义为str_to_date()。

2)也有明确的使用复选框,最后编辑值。

看起来像这样:

一旦你点击OK,CSV导入看起来像这样:

然后...

对!

我希望这能帮助某人。

mysql  string  date  sequelpro