Fail to import due to date format
in [Help]
|
Prev: I GOT $2500 FROM PAYPAL....
Next: Error Message in 2002
From: Thomas Andersson on 4 Aug 2010 23:03 I really need some help here. I'ce created a perl script that collect a lot of data for me and stores in a tab delimitered txt file that I want to import to my database. The problem is Access won't accept my data due to type conversion failure. All my time info is stored as "Jul 27 21:31" and I need to know what I can do to get Access to accept that and let me import all my data. Any help and suggestions would be wellcome.
From: Karl Hoaglund on 5 Aug 2010 00:40 Hi Thomas. You could import the date value into a field of type Text. This could be in a temporary import table. Once it is there, you could convert it into a Date value by using either an intrinsic conversion function or a function you've written yourself in VBA. Either type of function could be called in an update query, so you could zip through your newly-imported rows and update them quickly. The problem is that the import feature is not smart enough to convert the date values in the format they're starting from. Import them as text and then manipulate them. Hope this helps. Karl ---------------------------------------------- Karl Hoaglund, MCSD Microsoft Access Programmer http://www.nexuscgi.net
From: Salad on 5 Aug 2010 00:51 Thomas Andersson wrote: > I really need some help here. > > I'ce created a perl script that collect a lot of data for me and stores in a > tab delimitered txt file that I want to import to my database. The problem > is Access won't accept my data due to type conversion failure. All my time > info is stored as "Jul 27 21:31" and I need to know what I can do to get > Access to accept that and let me import all my data. > > Any help and suggestions would be wellcome. > > Expanding on what Karl wrote. I did not get an error like you descibed that but it may be due to me manually doing a simple File/GetExternalData/Import and importing the data from a text file. The result I get for a date field like yours is #Num in the column. However, if I change the text file to alsoinclude a year it imports fine. So if you wrote a script that can get the month, day, hours, minutes could you now also add a year? If you can't, Karl's advice is spot on, IMO.
From: John Spencer on 5 Aug 2010 08:02 If you import the data into a work table and import the date as a string, you can then manipulate it to a date with IIF(IsDate([TheStringField]),CDate([TheStringField]),Null) CDate("Jul 27 21:31") returns the following date 7/27/2010 9:31:00 PM The year will automatically default to the current year (assuming your system date is correctly set). John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Salad wrote: > Thomas Andersson wrote: >> I really need some help here. >> >> I'ce created a perl script that collect a lot of data for me and >> stores in a tab delimitered txt file that I want to import to my >> database. The problem is Access won't accept my data due to type >> conversion failure. All my time info is stored as "Jul 27 21:31" and I >> need to know what I can do to get Access to accept that and let me >> import all my data. >> >> Any help and suggestions would be wellcome. >> >> > Expanding on what Karl wrote. > > I did not get an error like you descibed that but it may be due to me > manually doing a simple File/GetExternalData/Import and importing the > data from a text file. The result I get for a date field like yours is > #Num in the column. However, if I change the text file to alsoinclude a > year it imports fine. So if you wrote a script that can get the month, > day, hours, minutes could you now also add a year? If you can't, Karl's > advice is spot on, IMO.
From: Thomas Andersson on 5 Aug 2010 10:06
Salad wrote: > Expanding on what Karl wrote. > > I did not get an error like you descibed that but it may be due to me > manually doing a simple File/GetExternalData/Import and importing the > data from a text file. The result I get for a date field like yours > is #Num in the column. However, if I change the text file to > alsoinclude a year it imports fine. So if you wrote a script that > can get the month, day, hours, minutes could you now also add a year? > If you can't, Karl's advice is spot on, IMO. Adding a year is simple so I'll try that (just concatenate it in front of the variable). One related question. My DB also ahve auto number and to let it be generated by DB I can select to skip that column during inport. Q though is. Should I leave the import rows as is (starting with the first non auto data) or insert a empty tab at start of each row so column numbers match? |