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

Question

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

I know that I need to use STR_TO_DATE, but I can not understand the correct syntax.

I get a bunch of these errors for each line:

[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 

That's what I'm doing:

1 File> Import. The file appears, and the date field in CSV is line 14:

2) Choose 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 the error above. What is the correct syntax?

It may be helpful to give you an idea of ​​the table into which I am importing:

 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 the values ​​for the fields following the date ( country and currency ) on each line.

Thanks in advance!

Answer 1

I get it.

Importing a user interface requires two things.

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

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

So, let go of the SET date = and define only the line inside parens for STR_TO_DATE ().

2) Also you need to uncheck the Use the last edited value box.

It looks like this:

As soon as you click OK, the CSV import will look like this:

And then...

Yes!

Hope this helps someone.