XML Woes (2003)

Topic

New Reply

I received 11 XML files I need to import to Access. In the past, I would open them in Excel, convert them then import them to Access. The XML files are now too large for XL (too many records) so I need to import them to Access.

I have a sample file, Otter.xml I opened in Excel and it opens fine. I import the Otter.xml file to Access and it results in blank tables. I want to have one table that looks like the layout in the Excel table. I trust (hope) there’s a simple setting in Access or in the XML files I can modify to accomplish this. All 3 files (XML, XLS, MDB) are included in the zip file for anyone who wants to take a look.

Thanks!

Reply | Quote

Replies

I have Office XP (2002) at home, which has more limited XML capabilities than Office 2003. Excel 2002 can open the XML file, but although some data are recognizable, many columns are blank. Are you sure the XML is well-formed?
I won’t be able to look at the files in Office 2003 before Monday.

Reply | Quote

Hans,
Is there any way to save an XML file via Excel into another format if it exceeds 65,536 rows?
Thanks!

Reply | Quote

I’m sorry, I have very little experience with XML.

Reply | Quote

I opened the xml in visual studio.net and looked at it, but It is very odd, so I’m not surprised Access had a problem with it. It appears to contain multiple tables. Was that your intent?

Reply | Quote

In XL it opens with a single table and is well formatted. If I could get it to come over in 1 table as it appears when opened in XL, I would be fired up. If not, then multiple tables would do.

Reply | Quote

There’s a difference between well formed xml and well-formatted. Access recognizes the tables described in the XML file, but it can’t read the fields names, which are captured as attributes rather than as elements, so it can’t pull in the data.

Reply | Quote

What are my options?

Reply | Quote

Further to what Charlotte mentioned, regarding well formed xml, I ran your xml file through the STG XML Validation Form, with the attached results. Don’t know if it will help or not though

Alan

Reply | Quote