1

i'm importing 1m+ records into my table from a csv file. Works great using the load data local infile method.

However, the dates are all different formats. A quick google lead me to this function:

STR_TO_DATE

However, when I implement that, I get nothing, an empty insert. here's my SQ cut down to include one date (I've 4 with the same issue) and generic column names:

load data local infile 'myfile.csv' into table `mytable`
fields terminated by '\t'
lines terminated by '\n'
IGNORE 1 LINES

( `column name 1`
, `my second column`
, @temp_date
, `final column`)

SET `Get Date` = STR_TO_DATE(@temp_date, '%c/%e/%Y')

If I do:

SET `Get Date` = @temp_date

The date from the csv is captured in the the format it was in the file. However when I try the first method, my table column is empty. I've changed the column type to varchar (255) from timestamp to captre whatever is going in, but ultimatly, I want to capture y-m-d H:i:s (Not sure if STR_TO_DATE can do that?)

I'm also unsure as to why I need the @ symbol.. google failed me there.

So, my questions are:

  1. Why do I need the @ symbol to use this function?
  2. Should the data format ('%c/%e/%Y') be the format of the inputted data or my desired output?
  3. Can I capture time in this way too?

sorry for the large post!

Back to Google for now...

Beertastic
  • 691
  • 1
  • 9
  • 27

2 Answers2

2

Why do I need the @ symbol to use this function?

The @ symbol means that you are using a variable, so the read string isnt put right away into the table but into a memory pice that lets you operate with it before inserting it. More info in http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Should the data format ('%c/%e/%Y') be the format of the inputted data or my desired output?

Its the format of the inputted data, more info in http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

Can I capture time in this way too?

You should be able to as long as you chose the correct format, something like

STR_TO_DATE(@temp_date,'%c/%e/%Y %h:%i:%s');
Jimmy
  • 1,115
  • 1
  • 9
  • 21
  • VERY informative.. thanks so much! to make sure I could see all the correct output, I made my date columns varchar.. but they're not capturing ANY data. The SQL above should make a date and put it in the column, the fact that my column is not a date field should not matter should it? (Naturally once I get this working, I'll make it a date field) – Beertastic Jul 28 '12 at 01:50
  • Any ideas why my data capure might be blank with the SQL above? :) – Beertastic Jul 28 '12 at 01:52
  • If the string doesnt match the format pattern then STR_TO_DATE returns NULL, try toying around with the STR_TO_DATE function and its pattern until you get the non null return. It would be helpful if you paste a sample of the datetime format you are trying to read. – Jimmy Jul 28 '12 at 01:56
  • Sample Date: 4/29/2012 My MySql date format to read above date: %c/%e/%Y – Beertastic Jul 28 '12 at 15:53
  • Ah... I've just realized it's not working because my import was screwing up the character encoding. Which leads me here.. http://stackoverflow.com/questions/11703258/csv-import-into-mysql-using-load-infile-causes-major-character-encoding-issues – Beertastic Jul 28 '12 at 17:50
0

I had this problem. What solved it for me was making sure I accounted for whitespace that weren't delimiters in my load file. So if ',' is the delimiter:

..., 4/29/2012, ...

might be interpreted as " 4/29/2012"

So should be ...,4/29/2012,...

dsf
  • 145
  • 3
  • 8