|
|
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.
|
|
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
"cew47" wrote:
[Quoted Text] > 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.
|
|
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" wrote:
[Quoted Text] > 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.
|
|
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" wrote:
[Quoted Text] > 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" wrote:
>
> > 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.
|
|
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...
"cew47" wrote:
[Quoted Text] > 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" wrote:
>
> > 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" wrote:
> >
> > > 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.
|
|
Hi,
So far, I am not able to get Access 2007 to import dates in that
format. Not sure why.
Clifford Bass
"cew47" wrote:
[Quoted Text] > 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.
|
|
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)
"Clifford Bass" wrote:
[Quoted Text] > Hi,
>
> So far, I am not able to get Access 2007 to import dates in that
> format. Not sure why.
>
> Clifford Bass
>
> "cew47" wrote:
>
> > 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.
|
|
All you need to convert your dates to mmddyyyy is
DOB_Text: Format([tblISAFlagged]![DOB], "mmddyyyy")
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Mark_E" <Mark_E[ at ]news.postalias> wrote in message
news:23A0154C-B963-4B3F-82D1-F3349BD1AE09[ at ]microsoft.com...
[Quoted Text] > 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)
>
>
> "Clifford Bass" wrote:
>
>> Hi,
>>
>> So far, I am not able to get Access 2007 to import dates in that
>> format. Not sure why.
>>
>> Clifford Bass
>>
>> "cew47" wrote:
>>
>> > 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.
|
|
|