Jump to content


Photo

CSV Import Driving me crazy!!!


  • Please log in to reply
3 replies to this topic

#1 HawkCode

HawkCode
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 15 December 2005 - 10:02 PM

Hi,

I'm trying to import a CSV from an Access query and keep getting the error:

[Err] 1292 - Incorrect datetime value: '' for column 'DateEntered' at row 1

here is a sample of the data:

[!--fonto:Courier New--][span style=\"font-family:Courier New\"][!--/fonto--]1,"Mr.","Donald","F.","Dickerson",
"P.E., CIPE","Estimator",,1/23/1997,"Import"
DATE ENTERED IS--------^^^^^^
[!--fontc--][/span][!--/fontc--]
I've tried it delimited with "," TABs all same result. Anybody got any ideas.

TIA

Rich

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 15 December 2005 - 10:31 PM

it should probably be quoted and conform to the formats discussed [a href=\"http://dev.mysql.com/doc/refman/4.1/en/datetime.html\" target=\"_blank\"]here[/a].
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 23 December 2005 - 11:22 PM

If you're just doing it once (or infrequently), the best way to do this is to "cheat' -- first, import the access dates into new CHAR field. Then, set the desired date column using the STR_TO_DATE() function, and drop the temporary CHAR column.

UPDATE table1 SET date_field=STR_TO_DATE(temp_date_field, '%m/%d/%Y');


Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 HawkCode

HawkCode
  • Members
  • PipPip
  • Member
  • 26 posts

Posted 24 December 2005 - 05:59 AM

Thanks,

I ended up writing my 1st php script to do it... I'vmade the summit and the rest is down hill! :)


Rich




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users