txt file field DDMMYYYY fails to import into Access'07, but OK in 2003

[Bungy Strap]

Hi there anyone and everyone.

My problem concerns importing a date from a txt file. I've been doing this for years with '97 and 2003 with no problem, but with 2007 I am now getting import errors which I can't resolve. The files, either fixed width or delimited, contain date information in the format ddmmyyyy (without any date separators). After importing I receive an import error message, and the relevant field in the table is blank.

In Access '97 or 2003 I can import date information directly into an Access date/time field by using an import specification where I have set the date delimiter to blank (by default this is set to "/" , so I simply delete this to leave the box empty). Using this method a file containing "25122007" (without the inverted commas) will be imported into Access as a date, and can be subsequently manipulated as date without having to resort to any fancy reformatting tricks. Obviously the import will not be sucessful if the date delimiter in the import spec is left as "/", or set to anything else, eg "." or ":".

But now that I've updated to Access 2007 this is just not possible no matter what I do or try. I can only import a date if the txt data has a separator, and that the same separator is set in the import specification.


In some new databases I have done a couple of workarounds, importing it as text into a text field, then using a CDate(Format(CLng([date]),"00/00/0000")) type expression in a subsequent query, but this is a pain having to do it, a really backward step.

But the real pain is older databases ('97 or 2003) which have been converted up to '07; they no longer work, and there is a lot of redesign to be done unless somone can tell me what I'm doing wrong.

Unfortunately I am not able to change the format of the dates in the data I receive, so the solution has to come from my end (ie Access). Surely Access 2007 should be able to recognise 25122007 as a date? All ideas gratefully received.

I've been happily using various versions of Access since 1994 and I've learnt lots along the way, and have always previously been able to sort out problems that I've come across without resorting to a forum, but I'm now really puzzled by a problem that I have come across in Access 2007. So although this is my first post I certainly wouldn't call myself a newbie. Hopefully I have described the problem accurately (it is realy simple in fact), but very happy to provide more information if required.

Many thanks (in anticipation)

Benjy

 

[Pat Hartman]

Did you import the import/export specs when you converted the database?

 

[Bungy Strap]

Thanks for your interest, Pat.

I wasn't quite sure about importing the specifications - I never thought that I would have had to.

So I've just done another test, created a 2003 databse containing an import spec (which imports these ddmmyyy dates OK), and converted it to 2007 without any other actions (ie I didn't specifically import the import spec etc).

Now I can see that the 2007 database contains the import spec which was present in the 2003 version, but I cannot import the txt file with the ddmmyyy dates, receiving the error as shown in the attachment.

What do you think - any ideas?

Benjy

 

[JOHNBS]

DID ANYOEN FIND AN ANSWER TO THIS? I HAVE TRIED LINKING DDMMYY AS DATE FROM A TXT FILE - TRIED VARIOSU PERMUTATIONS IN THE LINK SPEC BUT NO LUCK JUST GET FIELD SHOWING #NUM!. nEVER HAD A PROBLEM IN 2003. THIS EXAMPLE WAS A BRAND NEW DATABASE AND LINK I SET UP. BUT HAVE HAD SAME PROBLEM BEFORE AND NOT FOUND A SOLUTION.

 

[Bungy Strap]

Apologies, I should have posted the response I received from Microsoft...but since there had been wall of indifference on this forum I had presumed that no-one was interested.

Basically, Microsoft came back to me to say that this was a known problem, but with no idea of when it might be fixed. They offered 2 workarounds.

Their first idea was to firstly import the text file into Excel, making sure that the field was specified as date (it seems that Excel 2007 will correctly recognise a date in DDMMYY format) then to save the Excel file, and to import that into Acess 2007. OK as a one-off, but not a prectical solution for a regular process.

The other was just to import the field into Acess 2007 as text, and then to incorporate an expression like "Mid([Table1]![Date],1,2) & "/" & Mid([Table1]![Date],3,2) & "/" & Mid([Table1]![Date],5,4)" to force it into a recogniseable date format.

Their full (written) response was as below.

It doesn't really help when converting old Access 97/2003 databses into 2007 format, but at least you know where you stand.

Hope this helps

Benjy

The method I created allows the text to stay text and does not try to see it as a date at all. The Field2 values for Query 1 are formatted as a short date, but using the Mid function, it should NEVER encounter any issues as long as the input text strings have the expected length.
I understand the Mid function is technically more “arcane” that the more sophisticated CDATE function, however, I believe in this circumstance it may be more reliable from a “business” standpoint.


Answers to customer’s questions:
1. I would be very grateful if you could tell me whether you yourself have actually tried to import my txt file into Access 2007 (and 2003) and if you had the same results as me.
Yes, I have and yes the performance is the same as for you. As I mentioned above, this is a know issue.

2. Is Access 2007 able to import and recognise a text string like "14042008" and automatically interpret it as a date?
This depends on how you define “automatically”.
If you mean will it do it in the same way as Access 2003-No, Again this is a known issue.
If you mean via the “automatic” processing afforded by the CDATE function-perhaps yes, perhaps no (I would not personally trust it in this circumstance).
If you mean via the “automatic” processing forced on the text by the MID function-YES

2a. If the answer to the above is YES, could you please suggest what I am doing wrong, or what I should do differently to enable this to be done within Access?
See above.

2b. If the answer is NO, can you confirm whether Access 2007 is supposed to be able to handle this task correctly?
In theory, yes, it is supposed to do it, but it is not working correctly.

3. If Access 2007 IS supposed to be able to handle this task, would Microsoft be likely to introduce a fix to cure the problem?
Unfortunately, it is not possible for any level of support to predict what the Product Developers will do or when they will do it. They are aware of the issue. The best way for the customer to stay up to date is to regularly visit the Office Web site and get the most recent updates. Also from time to time, hotfixes are released and I am in the release notification group. When I notice a hotfix has been released for an Issue I have covered, I dig out the case information and alert the original SE to the available fix. This is not the normal procedure, but is an extra step The Frontier Team takes to ensure the best customer support possible (given the circumstances).

4. I don't want to think about the situation if Access 2007 IS NOT supposed to be able to handle this task - this would be such a strange and backward step, bearing in mind that Access 2003, Access '97 and Excel 2007 all can do so!
(see above answers).

 

[Pat Hartman]

If you are saying that A2003 automatically recognized the string "14042008" as a date, I have to admit that I am surprised. I always use import specs to define string dates so there would be no ambiguity. There are many strings where it would not be possible to determine the order of m,d,y.

Did you ever try recreating the import spec. Perhaps the conversion doesn't convert the spec properly.

 

[Bungy Strap]

Sorry, Pat, I wasn't using the correct words.

As you say, Access 2003 won't do it completely automatically. You have to to set up the import spec properly (date separator = [blank], 4 digit years, preceding zeros). Provided you have done that then it will do the rest - import a text string "ddmmyyyy" into a table, and set the field property to date/time.

But no matter what you do in Access 2007, it won't import the same string into a date/time field. You get an import error message, an import errors table is created showing a type conversion error, and the relevant field in the table which should contain the date contains a null. You can only import it into a text field.

It is the same regardless of whether you set up a brand new import spec (saved or unsaved), or used a pre-existing one.

As I said previously, Microsoft agree that this IS a problem, Access 2007 does not work as it should in this respect.

The most frustrating thing was that nowhere could I find any reference that this WAS a known problem. I spent ages and ages trying to work out what was going wrong, knowing that, surely, something so straightforward as this couldn't have been fouled up by Access 2007. I was slightly releived when they confirmed that it was a known bug. But if only they had published something to say so.....I would have saved myself hours of messing around and angst.

 

[stirlsa]

I have a similar problem trying to get a txt file with a YMD date field converted to a real date in ACCESS. I am glad to know that I am not totally crazy and that it is a known problem. I am now trying to do an ACCESS Update query, to convert the data.

I am having syntacs errors. In design view of update query:

Field: txtdt (this is the field with the text dates like:20081208)
Table: QTR (this is the name of the table)
Update To: "R2b" (this is the name of the blank date/time field I created to put the proper converted date data in via update query.)
Criteria: "UPDATE [QTR] SET [QTR].[txtdt]" DateSerial(Left([QTR]![R2b],4),Mid([QTR]![R2b],5,2),Right([QTR]![R2b],2))

 

[Bungy Strap]

Isn't it funny how it always seems better when you find out that it isn't only you who is having the same problem?

I'm not familiar with having to manipulate the format of date data (because previously Access used to handle it for me without me having to worry about it), but I think your problem is the use of the DateSerial function. I have used the CDate function as below, and it worked great.

UPDATE QTR SET QTR.R2b = CDate(Mid([QTR]![txtdt],1,2) & "/" & Mid([QTR]![txtdt],3,2) & "/" & Mid([QTR]![txtdt],5,4));

However, note that the advice from MS Access support was to leave the date as Text, and not to convert it into a Date/Time type. It seems that Access will recognise text as a date if it is in a recognisable date format - ie 15/04/2009, and many of the date functions will still work OK.

Good luck

Benjy

 

[Brianwarnock]

Let me say straight off that I do not have 2007, however the syntax of the update looks all wrong, and dateserial is agreat way to convert textdates to real dates as you do not have to worry about format, it uses your settings.

Given the info provided I think your update should be.

Field: R2b
Table: QTR (this is the name of the table)
Update To: = DateSerial(Left([QTR]![txtdt],4),Mid([QTR]![txtdt],5,2),Right([QTR]![txtdt],2))

no criteria required.

Brian

 

[Bungy Strap]

Fair cop Brian.

It works in 2007 as well as 2003, so that's now three ways (and counting?) how to handle it!

Benjy

 

[Brianwarnock]

Thanks for the feedback Benjy, I was a bit nervous that 2007 was about to make me look silly.

Brian

 

[Propellerhead]

Hey Guys,

I encountered the same problem. thank you for the advice.

I wonder if you could help me further by explaining how I get static text in efery row when I import a file. I need to capture the file name in a field as well as the date imported.

 

[Bungy Strap]

The Date bit is reasonably easy, but you will need to use an intermediate table. Your data is first imported into the intermediate table, and then you should run an append query to copy it into the main (cumulative) table: this table would have same fields as in the intermediate one, plus one for the import date. In the append query you would simply use the expression Now() for the relevant field.
I'm afraid I can't help with getting a file name into a field in every record. I'd suggest starting a new thread.
Good luck!