Importing dates from fixed-width text files to Access 2007

  • Thread starter Thread starter VickiH
  • Start date Start date
V

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!
 
This is actually a known bug. It been reported, and in fact fixed by
installing office sp2.....
 
Albert D. Kallal said:
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?
Click to expand...
 
Thankyou very much for this information. I am on SP2 but still have this
issue. Any other suggestions?
Click to expand...


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 said:
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!
Click to expand...
 
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....
 
Albert D. Kallal said:
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....
Click to expand...

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.
 
Back
Top