Importing dates from fixed-width text files to Access 2007

9 Jul 2009 4:59 AM

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!

9 Jul 2009 5:25 AM

Albert D. Kallal

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
pleaseNOOSpamKallal@msn.com

9 Jul 2009 7:06 AM

VickiH

Show quote Hide quote

"Albert D. Kallal" wrote:

> 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
> pleaseNOOSpamKallal@msn.com
>
>
>

9 Jul 2009 7:07 AM

VickiH

Show quote Hide quote

"Albert D. Kallal" wrote:

> 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
> pleaseNOOSpamKallal@msn.com
>
> Thankyou very much for this information. I am on SP2 but still have this issue. Any other suggestions?
>

9 Jul 2009 7:44 AM

Albert D. Kallal

"VickiH" <Vic***@discussions.microsoft.com> wrote in message
news:C7D5D89B-816C-4B2C-BD43-35DD5B12C293@microsoft.com...
>
>
> "Albert D. Kallal" wrote:
>
>> This is actually a known bug. It been reported, and in fact fixed by
>> installing office sp2.....

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

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

9 Jul 2009 8:10 AM

VickiH

Show quote Hide quote

"Albert D. Kallal" wrote:

> "VickiH" <Vic***@discussions.microsoft.com> wrote in message
> news:C7D5D89B-816C-4B2C-BD43-35DD5B12C293@microsoft.com...
> >
> >
> > "Albert D. Kallal" wrote:
> >
> >> This is actually a known bug. It been reported, and in fact fixed by
> >> installing office sp2.....
>
> > 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.
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
>
>
> 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!

9 Jul 2009 8:26 AM

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

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

9 Jul 2009 11:15 PM

VickiH

Show quote Hide quote

"Albert D. Kallal" wrote:

> 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 (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com

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.
Show quoteHide quote

>
>
>