Problems with importing a text file in Access 2007

[cew47]

Access 2007 will not allow me to import a text file that contains dates. I
get the following error message:

"The specification XML failed to validate against the schema. There is an
error in the following line of the XML document: ."

Of course Access 2003 worked brilliantly! No such luck with 2007. I've
done enough research on the web to see this has been a problem for others for
awhile now, but I'm hoping that there has been some fix along the way in
recent months.

 

[Clifford Bass]

Hi,

Is that one specific line or any line? What is the format of your
dates? mm/dd/yyyy or something else? Try a copy of the file with just the
first data row. Does that work? If not and if your dates are in another
format, try the mm/dd/yyyy format. If the dates are already in the
mm/dd/yyyy format, remove the date(s) from the record and try again. Does it
import now?

Clifford Bass

 

[NTC]

is the excel sheet field's property set to date?...can one change this to
text?? not sure as I'm not an excel junkie....

I would guess that Access is perceiving the excel sheet field property as a
date field. As a sanity check you could throw in some extra first rows of
data that truly are text fields....just nonsense characters strings...and see
if Access then defaults the entire import as a text field property...

If this is a one time import only then problem solved...just delete those
nonsense rows once inside Access.... if this is a recurring requirement then
maybe you should consider linking to the table and doing an append query...in
this method possibly it will be less judgemental of your excel field's
property and focus on the access table's field property...but not sure...

 

[cew47]

Actually, I'm importing a straight txt file, not Excel. I had import specs
set up in Access 03 and imported those into 2007. Other txt files without
dates import fine in 07. The date fields in the txt file are listed as
yyyymmdd. Not sure how to change that aspect of the field in either the txt
file or in the import specs.

 

[NTC]

You have attempted import without using that import spec?? and does it have
the same problem?

Have you added a few lines of certain dummy text and tried a fresh import
(without import spec)?

the key is first to get this file to import as text - - one way or the
other....just as a sanity check; if you can't get it to import under these
conditions above then it maybe is not a date issue...

 

[Clifford Bass]

Hi,

So far, I am not able to get Access 2007 to import dates in that
format. Not sure why.

Clifford Bass

 

[Mark_E]

In case it is of interest, in Access 2007 I got the same error "The
specification XML failed to validate against the schema. There is an error in
the following line of the XML document" when _exporting_ to a text file using
an export specification from Access 2000; date columns were included in the
export spec. I used the following workaround: instead of exporting any
dates, I created a query with logic like this to change the desired date
columns into mmddyyyy text strings, and then exported from this query instead.

Similarly, for your import task perhaps you will need an extra "staging"
step, importing your text file into a table where the "dates" get imported as
character strings, and from there you can probably get the data into the
destination table by doing the needed conversion of those character strings
like 12242008 to dates.

DOB_Text:
Mid(Month([tblISAFlagged]![DOB])*1000000+Day([tblISAFlagged]![DOB])*10000+Year([tblISAFlagged]![DOB])+100000000,2,8)

 

[Douglas J. Steele]

All you need to convert your dates to mmddyyyy is

DOB_Text: Format([tblISAFlagged]![DOB], "mmddyyyy")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

In case it is of interest, in Access 2007 I got the same error "The
specification XML failed to validate against the schema. There is an error
in
the following line of the XML document" when _exporting_ to a text file
using
an export specification from Access 2000; date columns were included in
the
export spec. I used the following workaround: instead of exporting any
dates, I created a query with logic like this to change the desired date
columns into mmddyyyy text strings, and then exported from this query
instead.

Similarly, for your import task perhaps you will need an extra "staging"
step, importing your text file into a table where the "dates" get imported
as
character strings, and from there you can probably get the data into the
destination table by doing the needed conversion of those character
strings
like 12242008 to dates.

DOB_Text:
Mid(Month([tblISAFlagged]![DOB])*1000000+Day([tblISAFlagged]![DOB])*10000+Year([tblISAFlagged]![DOB])+100000000,2,8)

Hi,

So far, I am not able to get Access 2007 to import dates in that
format. Not sure why.

Clifford Bass