Importing Fixed Length File into Access 2007

S

SSW

I initially attempted to import the MS Access 2003 database to 2007. It
appears that the export was successful. However, when I attempt to export a
fixed length flat file, and using the saved import spec, I get a xml error
(The specification XML failed to validate against the schema. There is an
error in the following line of the XML document). The process to import a
fixed length flat file is a routine process the I do monthly. I cannot
change the format of the file to include delimiter due to the file comes from
a legacy system.

Next, I attempted to recreate the spec import file by importing the fixed
length text flat file into Microsoft Office Access 2007 as a table. I
created a spec under the advanced section of the text export wizard and
specified the start column and width of each field. I ensure that the date
order is set to MDY, Four digits years and Leading zeros in Dates is checked.
Also the Date delimiter is blank. When I export the data, the text does
export into the correct date format. I have checked the spec numerous times
and I cannot figure out why it is not exporting correctly.

I understand there is a bug with the 2003 to 2007 import spec, but no one in
Microsift have addressed the issue. Is there also a bug using the import
spec for date in fixed width? Is there a workaround?

Sample fixed width File:

01312006
09142007
01012008
 
D

Douglas J. Steele

I don't believe any version of Access would import dates that didn't have a
date separator in them.

Try importing the date fields as text, then running an update query to
populate a proper date field.

Something like:

UPDATE MyTable
SET TheDateField = CDate(Format(TheTextDateField, "00\/00\/0000"))
WHERE TheTextDateField IS NOT NULL
 

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

Top