Importing dates from fixed-width text files to Access 2007

[VickiH]

In the text file I am using, the dates are in the format DDMMYYYY with no
delimeters eg 25102008 = 25 Oct, 2008. In Access 2003, I could import this
data directly to a field designated as Short Date. Now I get blank fields
for each date field when I import and an error table is created listing each
attemp to import a date as a "Type Conversion Error". I can import the data
into text fields without problems but can't change the field type after
importing without losing the data. The text file contains thousands of
records and about 6 date fields so I can't alter the text file
manually....help!

 

[Albert D. Kallal]

This is actually a known bug. It been reported, and in fact fixed by
installing office sp2.....

 

[VickiH]

This is actually a known bug. It been reported, and in fact fixed by
installing office sp2.....

 

[VickiH]

This is actually a known bug. It been reported, and in fact fixed by
installing office sp2.....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)

Thankyou very much for this information. I am on SP2 but still have this issue. Any other suggestions?

 

[Albert D. Kallal]

Thankyou very much for this information. I am on SP2 but still have this
issue. Any other suggestions?

Hum, that is strange.

Have you tried using an import spec? When the wizard starts up, you want to
select the advanced button, and select 4 year date, and set the date format.
I quite sure you had to do this in 2003 to get these types of imports to
work.

Regardless, even if you did not have to do this 2003, try using an import
spec and setting the year to 4 digits. When I do this, I am able to import
text files with 4 digit years.

 

[VickiH]

Hum, that is strange.

Have you tried using an import spec? When the wizard starts up, you want to
select the advanced button, and select 4 year date, and set the date format.
I quite sure you had to do this in 2003 to get these types of imports to
work.

Regardless, even if you did not have to do this 2003, try using an import
spec and setting the year to 4 digits. When I do this, I am able to import
text files with 4 digit years.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)


Thanks again....I have also done this. Set the date delimeter to null, selected 4 digit years and allow leading zeroes. I don't think the problem is with the 4 digit year, it seems more to be that there is no delimeter in the text date format...ie 04052001. When I test the import with sample data that includes the delimeters...eg 04/05/2001 there is no problem at all! It is very frustrating!

 

[Albert D. Kallal]

Create a new notepad text document on your desktop.

I type into the document:

"MyDate","MyName"
04052001,"Albert"
04052002,"Vicki"

Try importing the above....

I set the date formate to MDY, I erases the date delimter (set it to blank),
and checked the 4 digit year box..

I set the collum type as date.

Try importing into a new test table....

The above file imports just fine for me....

 

[VickiH]

Create a new notepad text document on your desktop.

I type into the document:

"MyDate","MyName"
04052001,"Albert"
04052002,"Vicki"

Try importing the above....

I set the date formate to MDY, I erases the date delimter (set it to blank),
and checked the 4 digit year box..

I set the collum type as date.

Try importing into a new test table....

The above file imports just fine for me....

Thanks very much. I tried it and it works as well. I then tried the file
below which is fixed-with and it worked too so obviously the problem must be
in the file I'm working with....will go back to basics and start looking at
that end.

02031996Kevin
04052001Albert
04052002Vicki

Thanks so much for your time.