0

I am using AWS's data pipeline to import a CSV file on S3 into my MySQL RDS instance and am having trouble with the STR_TO_DATE it seems. I am getting a:

"Parse error: expected field to be terminated by endOfRecord or endOfField but was endOfFile"

using this statement: 

insert into introtest (pk,id,deld) values (?,?, str_to_date(?, %m/%d/%Y));

I have tried these as well:

insert into introtest (pk,id,deld) values (?,?, str_to_date('?', %m/%d/%Y));
insert into introtest (pk,id,deld) values (?,?, str_to_date(?, '%m/%d/%Y'));
insert into introtest (pk,id,deld) values (?,?, str_to_date('?', '%m/%d/%Y'));

all with the same error

the CSV data =

0,740453,12/01/2010

thanks in advance!

user3511424
  • 11
  • 6
  • I believe that you need to quote your date formatting strings. and you're missing quotes on one of the ? in the str_to_date functions. – Sloan Thrasher Jun 22 '18 at 23:13
  • I have tried 4 different single quotes around the date mask, and question mark. Are you talking about double quotes? Or is there an iteration of quotes I haven't tried? – user3511424 Jun 23 '18 at 15:59
  • Are you calling this from PHP? Another language? Show the code you're calling it from. For instance, if you're using mysqli parameterized queries then the line ```insert into introtest (pk,id,deld) values (?,?, str_to_date(?, '%m/%d/%Y'));``` should be correct., if it's enclosed in double quotes in the PHP code. – Sloan Thrasher Jun 23 '18 at 21:51
  • I am not sure. I am using the AWS S3 to MySQL RDS pipeline template. I don't know how to tell and can't find any documentation on it other than: https://docs.aws.amazon.com/datapipeline/latest/DeveloperGuide/dp-object-copyactivity.html is there something wrong with my dummy file I created in notepad? I simply opened notepad typed 0,740453,12/01/2010 and saved it as a .csv. Uploaded it to S3 and am running the pipe. – user3511424 Jun 24 '18 at 01:23
  • One possible issues is the date in the csv file should be a string. Try editing your data file and put quotes around the date. My question above is asking you to clarify the command you show in your question. It seems to be missing something. Where do you tell AWS the name of the csv file? Where do you tell it what the "?" stands for? What is the complete code? – Sloan Thrasher Jun 24 '18 at 15:16
  • I am not sure how to see the complete code. I am merely using the AWS template "Load S3 data into RDS MySQL table" The ? stand for place holders for the data it is reading from the CSV file in my S3 bucket. – user3511424 Jul 09 '18 at 15:37
  • Did you try quoting the dates in the data? – Sloan Thrasher Jul 09 '18 at 15:42

1 Answers1

0

If you still didn't clear the issue...

For the data to be loaded from S3 to RDS using AWS Data pipeline the csv file should be a linux file, so if you created the file in Windows then it needs to be converted as in Linux or create the file in Linux environment and use that file.

monty
  • 13
  • 5