Change date format when importing csv?

[TOA]

Hey guys, hoping you can help..

 

Here's my dilemna:

We get a dump from a vendor in csv format.

I use a mysql database, they don't; so, they format the date as MM/DD/YYYY and I obviouosly need it as YYYY/MM/DD to store in the db.

 

My original thought was to write a script that takes the date and reformats, then inputs into db instead of doing an import...

 

then I thought mysql might have an inherent function to do this so I looked and nothing jumped out at me as an obvious answer. So I thought I'd ask around and see if:

 

Is there a built in way (in mysql) to alter the format of a date while I'm importing?

 

Hope this makes sense.

[PFMaBiSmAd]

If you are using LOAD DATA [LOCAL] INFILE to do the importing, you can use a SET statement as part of that query to modify values being inserted.

 

You could also insert the MM/DD/YYYY value into its own column, then update the actual DATE column using the mysql STR_TO_DATE() function.

[TOA]

If you are using LOAD DATA [LOCAL] INFILE to do the importing, you can use a SET statement as part of that query to modify values being inserted.

I wasn't but I'll look into it right now.

 

You could also insert the MM/DD/YYYY value into its own column, then update the actual DATE column using the mysql STR_TO_DATE() function.

That's close to what I had thought of doing if I found no answer, except I hadn't thought of using the extra field. That's an interesting thought. I see how that could work.

 

Would the LOAD DATA [LOCAL] INFILE method be the better/more acceptable way?

Thanks for the leads

[TOA]

So I've done some reading and it looks like the SET option is what I'm looking for.

 

I also read that the set statement can't be used in phpmyadmin, but must be entered through the command line...is this correct?

I see no place to enter it or am I missing something 

 

Thanks for the help so far

[TOA]

Update:

 

I can use the LOAD DATA radio button on the phpmyadmin import tab to successfully import data, but without using the SET to change any data like I need to.

 

I can use the sql tab to write the full statement, but can't get my file to the right place. I get Error 2: File not found. For the life of me, I can't find where to put my file.

 

Suggestions? Am I doing something wrong?

 

Thanks for reading

 

Oh and here's the statement I'm working with: (table names/unused field names have been changed to protect the innocent)

LOAD DATA LOCAL INFILE 'Sample CSV File2.txt'
INTO TABLE CCReporting
FIELDS TERMINATED BY ','
(`a bunch of fields`, `Opened Date`, `Closed Date`, `a few more fields`)
SET `Opened Date` = DATE_FORMAT(STR_TO_DATE(`Opened Date`, '%m/%d/%Y'), '%Y/%m/%d');

I'll need to figure out how to change two fields, but I'll cross that bridge after I get it to work on one

[TOA]

Ok, so I got this to work from a script

 

LOAD DATA LOCAL INFILE 'Sample CSV File2.txt'
INTO TABLE CCReporting
FIELDS TERMINATED BY ','
(`a bunch of fields`, @open, `Closed Date`, `a few more fields`)
SET `Opened Date` = DATE_FORMAT(STR_TO_DATE(@open, '%m/%d/%Y'), '%Y/%m/%d');

 

So instead of using phpmyadmin to import I would use the script. But I would still like to learn how to do it in the admin section, just so I know. If anyone can help me with that, I'd love to know.

 

I'm going to leave this unsolved for just a bit longer until I get this working correctly for both values, and to see if anyone has an answer.

 

Thanks

[mikosiko]

.... But I would still like to learn how to do it in the admin section, just so I know. If anyone can help me with that, I'd love to know.

 

 

you have at least this options:

- Drop the file in the same directory where PhpMyAdmin is installed or

- Indicate the full path to the file in the command.... like :

  LOAD DATA LOCAL INFILE '<full path to the file>'... etc..etc  (I personally prefer this option)

 

[TOA]

Thanks for the reply

 

you have at least this options:

- Drop the file in the same directory where PhpMyAdmin is installed or

Wish I could find it  I'm not the admin

 

- Indicate the full path to the file in the command.... like :

  LOAD DATA LOCAL INFILE '<full path to the file>'... etc..etc  (I personally prefer this option)

so if the file was on my desktop for example, it would be "C:\Documents and Settings\my_comp_name\Desktop\my_file_name.ext"?

 

I tried that just now and got the same file not found error

[mikosiko]

In Windows I normally use either:

 

"C:\\Documents and Settings\\my_comp_name\\Desktop\\my_file_name.ext" 

  (double \ to scape the interpretation)  or

 

"C:/Documents and Settings/my_comp_name/Desktop/my_file_name.ext"

  both options work for me

[mikosiko]

wait a second... I just read this in your post:

so if the file was on my desktop for example, it would be "C:\Documents and Settings\my_comp_name\Desktop\my_file_name.ext"?

 

file should be in the server side

[TOA]

In Windows I normally use either:

 

"C:\\Documents and Settings\\my_comp_name\\Desktop\\my_file_name.ext" 

  (double \ to scape the interpretation)  or

 

"C:/Documents and Settings/my_comp_name/Desktop/my_file_name.ext"

  both options work for me

 

I get this error with both:

#2 - File 'C:/Documents and Settings/my_comp_name/Desktop/Sample CSV File2.txt' not found (Errcode: 2)

 

 

 

wait a second... I just read this in your post:

so if the file was on my desktop for example, it would be "C:\Documents and Settings\my_comp_name\Desktop\my_file_name.ext"?

 

file should be in the server side

Yeah, I tried earlier to find where to put the file locally, but couldn't, not sure where the admin has it.

 

I can use the sql tab to write the full statement, but can't get my file to the right place. I get Error 2: File not found. For the life of me, I can't find where to put my file.

 

I tried every level on my web server, so the mysql server must be somewhere else

 

Thanks for the help

[TOA]

Ok, well I didn't get a chance to talk to our admin today, but I think the remaining problem is that I need to get that file into the server somehow, which is what started me on this expedition in the first place. As I stated a few posts ago, I got things working through a script, but not the local admin area.

 

I'll mark it solved