5

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:

enter image description here

2) Select Date > Add expression

enter image description here

3) In the Expression window, add this code:

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

enter image description here

4) Get this result:

enter image description here

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!

Tim Elhajj
  • 835
  • 1
  • 9
  • 16

3 Answers3

6

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:

Global source values window

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

CSV ready to import

And then...

Success!

Yes!

I hope this helps someone.

Tim Elhajj
  • 835
  • 1
  • 9
  • 16
  • Thanks! But have you figured out how to do this once for a table that you continually truncate and re-upload? When I do this, it works -- but when I truncate the data and update the table, my date's go right back to varchar. Doesn't even save the expressions when I attempt to re-upload. – Ryan Mar 10 '17 at 17:59
0

for anyone with this problem, I found a post that has another solution

https://wpwhatnot.com/change-date-format-on-import-to-mysql-from-csv-file/

this worked for me

Limon
  • 1,772
  • 7
  • 32
  • 61
0

You need to enter STR_TO_DATE($14,'%m/%d/%Y') without @. (And the SQL checkbox should be checked.)

If you uncheck the 'Last edited value' checkbox, nothing will be added to the field so it won't throw an error. You either need to leave it checked or select your custom expression afterward.

akksi
  • 1