Importing dates from fixed-width text files to Access 2007

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!
 
A

Albert D. Kallal

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

VickiH

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?
 
A

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.
 
V

VickiH

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!
 
A

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....
 
V

VickiH

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top