Convert string to date/time MS Access will accept at import
in [Perl]
|
Prev: FAQ 5.11 How can I set up a footer format to be used with write()?
Next: FAQ 9.24 How do I fetch a news article or the active newsgroups?
From: Thomas Andersson on 4 Aug 2010 20:17 I have my data collector script finished now and it works fine. Now my problem is that MS Access throws a hissyfit when I try to import my data. At first I thought it was the AutoNumber field that caused it but it isn't as I found the report saying that I get "Type Conversion Failure" on every filed containing time info. All my collected data have the time info stored in this format "Jul 28 18:34" (Tab delimitered) and now I guess I need to convert all those on the fly before they are stored to something Access will accept. Is there any (hopefully easy) way to do this? Best Wishes Thomas
From: Ben Morrow on 4 Aug 2010 20:53 Quoth "Thomas Andersson" <thomas(a)tifozi.net>: > I have my data collector script finished now and it works fine. Now my > problem is that MS Access throws a hissyfit when I try to import my data. At > first I thought it was the AutoNumber field that caused it but it isn't as I > found the report saying that I get "Type Conversion Failure" on every filed > containing time info. > > All my collected data have the time info stored in this format "Jul 28 > 18:34" (Tab delimitered) and now I guess I need to convert all those on the > fly before they are stored to something Access will accept. > > Is there any (hopefully easy) way to do this? You will need to find out what format Access will accept. I'm afraid I can't help you there. You will then need to parse those dates and convert them into the appropriate format: as usual with Perl, there are lots of ways to do this. For a simple case like this, the Time::Piece module (bundled with Perl) is probably sufficient. You use Time::Piece->strptime to parse the dates, then $date->strftime to reformat them in a manner Access will understand. Alternatives to Time::Piece include DateTime, an extremely complete set of modules for doing all sorts of things with dates and times. Ben
From: Martijn Lievaart on 6 Aug 2010 02:35
On Thu, 05 Aug 2010 02:17:33 +0200, Thomas Andersson wrote: > I have my data collector script finished now and it works fine. Now my > problem is that MS Access throws a hissyfit when I try to import my > data. At first I thought it was the AutoNumber field that caused it but > it isn't as I found the report saying that I get "Type Conversion > Failure" on every filed containing time info. > > All my collected data have the time info stored in this format "Jul 28 > 18:34" (Tab delimitered) and now I guess I need to convert all those on > the fly before they are stored to something Access will accept. Not really a Perl question, but an Access one. Fortunately, been there, done that. The only way to get Access to reliably parse datetimes, is to offer them as YYYY-MM-DD HH:MM:SS. I think you can leave of the seconds. Note that this is still assumed to be in the current timezone, you'll neet to STFG if this is a problem for you. HTH, M4 |