Avatar of Eric Sherman
Eric ShermanFlag for United States of America asked on

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
Microsoft AccessMicrosoft DevelopmentMicrosoft Applications

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon
SOLUTION
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Eric Sherman

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
ASKER
Eric Sherman

Thanks aikimark....

ET
SOLUTION
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Eric Sherman

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

Curious, what is generating the XML file are a Workbook?
ASKER
Eric Sherman

>>>>>Curious, what is generating the XML file are a Workbook?<<<<<

It is a Cloud Based accounting system.


ET
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Eric Sherman

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
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Eric Sherman

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
ASKER
Eric Sherman

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

ET,

You're welcome.

Good luck with your project.

Boyd aka HiTechCoach
Microsoft Access MVP
Jeffrey Coachman

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
ASKER
Eric Sherman

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

Great,...glad I was able to help too.

;-)

Jeff