Problem with CSV import

[beaker]

Hi all,

I've done a search for this but not managed to find anything of relevance.

I've imported a CSV file using SQLyog but when I view the imported data the date field has been interpreted incorrectly. In the CSV file the date is “dd:mm:yy” but this is interpreted as “yyyy-mm-dd” so for example the date “01:05:04” (1st May 2004) is shown after import as “2001-05-04”. Is there a setting somewhere I've missed?

Thanks for any help you can give me.

[Ritesh]

Please cut-n-paste the “CREATE TABLE…” statement for the table.

Sample data in CSV will be helpful.

[beaker]

Hi Ritesh,

Here's the 'CREATE TABLE' statement from SQLyog:

CREATE TABLE `clocking_data` (

`mcid` tinyint(4) default NULL,

`inout` tinyint(4) default NULL,

`clockno` text,

`time` time default NULL,

`date` date default NULL,

`weekday` tinyint(4) default NULL

) TYPE=MyISAM

And here's some sample data from the CSV file:

“1”,”1″,”10650″,”05:57″,”01:05:04″,”6″

“1”,”1″,”10730″,”06:00″,”01:05:04″,”6″

“1”,”1″,”10460″,”06:24″,”01:05:04″,”6″

“1”,”1″,”10130″,”06:51″,”01:05:04″,”6″

“1”,”1″,”10440″,”07:01″,”01:05:04″,”6″

“1”,”1″,”10120″,”08:33″,”01:05:04″,”6″

“1”,”2″,”10130″,”11:15″,”01:05:04″,”6″

“1”,”2″,”50103″,”12:00″,”01:05:04″,”6″

“1”,”2″,”10730″,”12:00″,”01:05:04″,”6″

“1”,”2″,”10630″,”12:00″,”01:05:04″,”6″

“1”,”1″,”10650″,”05:58″,”02:05:04″,”7″

“1”,”1″,”10730″,”06:01″,”02:05:04″,”7″

“1”,”2″,”50103″,”12:00″,”02:05:04″,”7″

“1”,”2″,”10730″,”12:00″,”02:05:04″,”7″

“1”,”1″,”10340″,”05:55″,”04:05:04″,”2″

“1”,”1″,”10730″,”06:01″,”04:05:04″,”2″

“1”,”1″,”10220″,”07:18″,”04:05:04″,”2″

“1”,”1″,”10560″,”07:26″,”04:05:04″,”2″

“1”,”1″,”10110″,”07:39″,”04:05:04″,”2″

“4”,”2″,”10050″,”10:00″,”30:04:04″,”5″

“4”,”1″,”10220″,”13:35″,”30:04:04″,”5″

“4”,”1″,”10590″,”13:47″,”30:04:04″,”5″

“4”,”1″,”10030″,”13:53″,”30:04:04″,”5″

“4”,”1″,”10780″,”13:54″,”30:04:04″,”5″

“4”,”2″,”10650″,”14:00″,”30:04:04″,”5″

“4”,”2″,”10440″,”14:02″,”30:04:04″,”5″

“4”,”2″,”10210″,”14:04″,”30:04:04″,”5″

“4”,”2″,”10090″,”14:04″,”30:04:04″,”5″

“4”,”2″,”10310″,”15:00″,”30:04:04″,”5″

“4”,”2″,”10240″,”15:00″,”30:04:04″,”5″

“4”,”2″,”10130″,”15:00″,”30:04:04″,”5″

“4”,”2″,”10630″,”15:00″,”30:04:04″,”5″

“4”,”2″,”10460″,”15:00″,”30:04:04″,”5″

I'm new to MySQL and SQLyog so there may well be something wrong with the way I've set the table up. Apologies if it's something rudimentary 😉

Thanks

[Shadow]

Well, I would change the contents of that CSV file to resemble YYYY-DD-MM setting. “Although MySQL tries to interpret values in several formats, dates always must be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98').” (Manual)

In my v4.1.7, there is a date_format variable, though. Perhaps you could try to play around with it and see if that helps!

[beaker]

Thanks for your help. I've had a play with the date_format variable as you suggested and I've managed to get it to display the way I want. I think I'm going to have to write a little conversion routine to get the date in the correct format before I import to my table though.

Thanks once again 🙂

[Author]

Posts