Import XML File Into Access Table Using VBA???

I am testing various options to import a XML file into a Access table.  I can open the XML file in Excel and save it as a workbook then import it but I would like to avoid the extra steps.  Have not worked with XML files very much but I think it's possible using VBA to load the XML file then walk through it looking for specific nodes, etc.  

Below is a sample of a demo customer xml file that I am working with.  It has three customer records for this sample.  The number of customer rows will vary in the actual files.  Since the table with the same field names as the heading rows will exist in the Access DB, I just need to move to each row in the xml file after loaded and write the data to the table using a recordset.  

The field names are;

Internal ID, Name, Phone, Billing Address 1, Billing City, Billing State, Sales Rep, Search Engine, Keyword Search, Medium
<?xml version="1.0" encoding="utf-8"?>
 <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>DemoCompany</Author>
<LastAuthor>DemoCompany</LastAuthor>
<Company>DemoCompany</Company>
</DocumentProperties>
<Styles>
<Style ss:ID="company">
<Alignment ss:Horizontal="Center"/>
<Font ss:Bold="1"/>
</Style> <Style ss:ID="error">
<Alignment ss:Horizontal="Center"/>
<Interior ss:Color="#d04040" ss:Pattern="Solid"/>
<Font ss:Bold="1"/>
</Style> <Style ss:ID="header">
<Alignment ss:Horizontal="Center"/>
<Font ss:Size="7" ss:Bold="1"/>
<Interior ss:Color="#d0d0d0" ss:Pattern="Solid"/>
</Style> <Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Arial" ss:Size="8"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s__TIMEOFDAY"><NumberFormat ss:Format="Medium Time"/></Style>
<Style ss:ID="s__DATETIME"><NumberFormat ss:Format="General Date"/></Style>
<Style ss:ID="s__DATETIMETZ"><NumberFormat ss:Format="General Date"/></Style>
<Style ss:ID="s__DATE"><NumberFormat ss:Format="Short Date"/>
</Style><Style ss:ID="s__text"></Style><Style ss:ID="s__currency"><NumberFormat ss:Format="Currency"/></Style>
<Style ss:ID="s1_b_text"><Alignment ss:Indent="1"/><Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8" ss:Color="#000000" ss:Bold="1"/></Style>
<Style ss:ID="s_b_text"><Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8" ss:Color="#000000" ss:Bold="1"/></Style>
<Style ss:ID="s2__text"><Alignment ss:Indent="2"/></Style>
<Style ss:ID="s_b_currency"><Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8" ss:Color="#000000" ss:Bold="1"/><NumberFormat ss:Format="Currency"/></Style>
<Style ss:ID="s_currency_nosymbol"><Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8" ss:Color="#000000" /><NumberFormat ss:Format="#,##0.00_);[Red]\(#,##0.00\)"/></Style>
<Style ss:ID="s1__text"><Alignment ss:Indent="1"/></Style>
<Style ss:ID="s_b_currency_X"><Font ss:FontName="Verdana" x:Family="Swiss" ss:Size="8" ss:Color="#000000" ss:Bold="1"/><Interior ss:Color="#f0e0e0" ss:Pattern="Solid"/><NumberFormat ss:Format="Currency"/></Style>
<Style ss:ID="s__currency_en_US"><Alignment ss:Vertical="Center" ss:Horizontal="Right"/><NumberFormat ss:Format="&quot;$&quot;#,##0.00_);(&quot;$&quot;#,##0.00)"/></Style>
<Style ss:ID="s__currency_en_GB"><Alignment ss:Vertical="Center" ss:Horizontal="Right"/><NumberFormat ss:Format="&quot;£&quot;#,##0.00_);(&quot;£&quot;#,##0.00)"/></Style>
<Style ss:ID="s__currency_en_CA"><Alignment ss:Vertical="Center" ss:Horizontal="Right"/><NumberFormat ss:Format="&quot;Can$&quot;#,##0.00_);(&quot;Can$&quot;#,##0.00)"/></Style>
<Style ss:ID="s__currency_fr_FR_EURO"><Alignment ss:Vertical="Center" ss:Horizontal="Right"/><NumberFormat ss:Format="&quot;€&quot;#,##0.00_);(&quot;€&quot;#,##0.00)"/></Style>
</Styles>
<Worksheet ss:Name="DemoCustomerViewResults">
<Table><Row>
<Cell ss:StyleID="header"><Data ss:Type="String">Internal ID</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Name</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Phone</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Billing Address 1</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Billing City</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Billing State/Province</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Sales Rep</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Search Engine</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Keyword Search</Data></Cell>
<Cell ss:StyleID="header"><Data ss:Type="String">Medium</Data></Cell>
</Row>
<Row><Cell ss:StyleID="s__text"><Data ss:Type="Number">1397</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">3M</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">526 8426 1095</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
</Row>
<Row><Cell ss:StyleID="s__text"><Data ss:Type="Number">1542</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">Aaron Abbott</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">(303) 464-4122</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">1500 Broadway</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">Denver</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">CO</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">Mary Redding</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
</Row>
<Row><Cell ss:StyleID="s__text"><Data ss:Type="Number">1249</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">AB&amp;I Holdings</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">(916) 831-7299</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String"></Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">Mary Redding</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">Google</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">Desk Accessories</Data></Cell>
<Cell ss:StyleID="s__text"><Data ss:Type="String">Print</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>

Open in new window


Thanks,

ET
LVL 19
Eric ShermanAccountant/DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Hey buddy, just to be clear, ...are you saying you have already tried importing this file using Access automation and it does not work?
    Application.ImportXML

This is available in Access 2010 and 2013 (not sure if this existed in 2007 or lower)
example:
    Application.ImportXML "C:\YourFolder\yourtable.xml", acStructureAndData

Or am I not understanding your request?


JeffCoachman
1
Eric ShermanAccountant/DeveloperAuthor Commented:
Hi Jeff ... Thanks for the reply.  Well, I looked at the ImportXML functionality in Access 2010 first but that option creates 4 separate tables (Cell, DocumentProperties, Style and Styles).  Most of the date is in one column in the table Cell.  Just looking at it seems like it will be more difficult to walk down this column using code to try and place the data in their proper columns (see screenshot below).

XML Import

ET
0
Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks aikimark....

ET
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
ET,

Your XML file is in Excel workbook format. That i why Excel can easily read it while Access does not understand the Workbook XML format.

You can use Excel Automation to convert the XML formatted workbook into a standard xls/xlxs file that Access can easily import.  That will be much easier to code then trying to write code that understands a Workbook's XML format.
0
Eric ShermanAccountant/DeveloperAuthor Commented:
TheHiTechCoach ...

>>>>>You can use Excel Automation to convert the XML formatted workbook into a standard xls/xlxs file that Access can easily import.  That will be much easier to code then trying to write code that understands a Workbook's XML format.<<<<<

Yes, that was my second choice but this application my run on a machine for which Excel may not be installed.  I was looking at options that would not include opening the XML file in Excel first.

ET
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Curious, what is generating the XML file are a Workbook?
0
Eric ShermanAccountant/DeveloperAuthor Commented:
>>>>>Curious, what is generating the XML file are a Workbook?<<<<<

It is a Cloud Based accounting system.


ET
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
ET,

Would you mind sharing which cloud based accounting system?

First I would see if it is possible to export in a different format that is more compatible with Access's import abilities?  A CSV file will be very easy to work with.



If not, you could still use Access to import the XML file. I would read through the table named Cell and write the data where you needed.

Just looking at it seems like it will be more difficult to walk down this column using code to try and place the data in their proper columns
That really is very easy code to do.  In my experience, that would be a lot easier to code than the code to parse the XML.  I have written code to parse XML and other formats. It takes a lot of time and code.

FWIW:
I find it best to import into. a staging tables in a staging back end.  That allows me to run VBA code and/or queries to clean up and validate the data. That way I can be sure the data is in the correct format  before the final import in the live/production back end tables.
0
Eric ShermanAccountant/DeveloperAuthor Commented:
The HiTechCoach ... I think it's Netsuite which is one of the cloud apps the client is currently evaluating.  The sample file that I am working with is their CSV exported file.  They call it CSV but it's really a Excel XML file.

>>>>That really is very easy code to do.  In my experience, that would be a lot easier to code than the code to parse the XML.  I have written code to parse XML and other formats. It takes a lot of time and code.<<<<<

Yes, and I agree.  The four tables created by the XML import threw me off for a while because of the format but looking at it more closely, some of the sample records have no data in a couple of fields thus yielding the blank rows but each customer record does have 10 rows representing the 10 fields.

Thanks,

ET
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
ET,

I was curious how fast I could do this.  Took me less than 15 minutes to write the basic code to do what you need.  I would still add some more error handling.

Here ya go ...
XMLimport.zip
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eric ShermanAccountant/DeveloperAuthor Commented:
Yep, TheHiTechCoach ... That's kind of what I was thinking but I have some other considerations as well ....

1.) This will be an unattended application connected to MySQL server backend data tables.  I just need to test and see if the Access XML import will dump the data into the 4 tables if they already exist and and linked MySQL tbls.

2.) I prefer using linked tables to avoid the front-end application from bloating as each XML file could vary in size.   Don't want to get into compacting/repairing the application to minimize this since it will run unattended.

3.) This import will basically run say every hour, etc.

Again, thanks for your sample code.

ET
0
Eric ShermanAccountant/DeveloperAuthor Commented:
I think you are correct ... using the recordset and looping through the Cell data once imported using the Import XML option is Access will be more efficient to work with than Parsing the XML file with VBA.

Thanks,

ET
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
ET,

You're welcome.

Good luck with your project.

Boyd aka HiTechCoach
Microsoft Access MVP
0
Jeffrey CoachmanMIS LiasonCommented:
E,

No need to give points to me if Boyd actually posted a solid solution.

My post was just in case you were not aware of the relatively "new" import XML command.
(I was only made aware of this last week or so...)

;-)

Jeff
0
Eric ShermanAccountant/DeveloperAuthor Commented:
As it turned out the XML Import option in Access will be the best route for this project and you mentioned it first so I gave your suggestion a few points as well.  Sometimes it's better to go with a proven method that works even if it involves a few additional steps.  In this case consistency will be the key and driving factor since everything involving the web will be some type of XML data.

Thanks,

ET
0
Jeffrey CoachmanMIS LiasonCommented:
Great,...glad I was able to help too.

;-)

Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.