Importing/Linking an XML spreadsheet into Access

Hi Experts,

What is the best way to import/Link an XML spreadsheet into Access?

If I try importing as an XML file it takes forever.
I can convert it to Excel & then it imports fine, but is that the best way?

I mainly work with Office 2003,  but it seems that also in 2010 importing as XML is very slow

Thanks,

Gary
LVL 46
tbsgadiAsked:
Who is Participating?
 
tbsgadiConnect With a Mentor Author Commented:
I think it's more relevant to the Access/Excel zones.
It looks like I'll be sticking to converting it first to Excel.
0
 
SiddharthRoutCommented:
The following example imports an XML file into a new table named XMLFILE in the current database.

Application.ImportXML DataSource:="XMLFILE.xml", ImportOptions:=acStructureAndData

Sid
            
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How are you importing now? XML is nothing more than a strucutred text file, and you can use the XML libraries to do this, but in some cases that's slower than doing it with the code suggested by Sid.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
tbsgadiAuthor Commented:
I'm trying to import an Excel XML spreadsheet containing 3 sheets.
As I wrote the only success I'm having, is first to convert it to normal excel & then link or import it.

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not clear what you mean by "xml spreadsheet". Is this a spreadsheet saved in XML format, or something along those lines?
0
 
tbsgadiAuthor Commented:
Yup..It looks the same as excel but with less functionality.
http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats
0
 
Jeffrey CoachmanMIS LiasonCommented:
Hey, buddy...

Define "Forever" in chronological terms...
;-)

How big are these files?
Any special XML formatting?

I just saved the Northwind sample file "Orders" table  (830 records) as an XML file in Excel, then imported it into Access, ...it took no longer than a straight xls file import...

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
what's the code you are using for the import?
0
 
tbsgadiAuthor Commented:
Forever is less than eternity but more than a minute!

To import the same excel takes a second.

No special formatting...regular excel 3 sheets.
0
 
tbsgadiAuthor Commented:
The same happens both with the above code & manually.
0
 
Jeffrey CoachmanMIS LiasonCommented:
"Code above"?
0
 
tbsgadiAuthor Commented:
Application.ImportXML DataSource:="XMLFILE.xml", ImportOptions:=acStructureAndData
0
 
Jeffrey CoachmanMIS LiasonCommented:
How about something like this:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "TargetTableName", "C:\yourFolder\YourFile.XML", True
0
 
tbsgadiAuthor Commented:
Thanks, not supported in Access 2003

In 2010 it only works if I first open the workbook, otherwise get error "External Table not in expected format."
0
 
Jeffrey CoachmanMIS LiasonCommented:
Sorry, I forgot about the Acc03 requirement...

...I never really had the time to work with XML, but I do know that all the Formatting/shcema/template stuff is a bit over my head...

You may want to ask the mods to link this Q to the appropriate XML zone(s)
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.