Import XML Children as Columns in Excel

Question

I have several large XML files that I need to convert to Excel spreadsheets. Some of the data is nested and when I import to Excel it is creating extra rows for each child value. I would like to create a column to handle the children so I only have one row per item. Here is a sample of the XML:

<Items>
    <Item MaintenanceType="C">
      <PartNumber>ABC123</PartNumber>
      <BrandLabel>Brand X</BrandLabel>
      <Descriptions>
        <Description MaintenanceType="C" DescriptionCode="DES" LanguageCode="EN" Sequence="1">This is a short description</Description>
        <Description MaintenanceType="C" DescriptionCode="EXT" LanguageCode="EN" Sequence="1">This is a little longer description</Description>
        <Description MaintenanceType="C" DescriptionCode="MKT" LanguageCode="EN" Sequence="1">This is a reallllllllllly long description</Description>
        <Description MaintenanceType="C" DescriptionCode="ASC" LanguageCode="EN" Sequence="1">Another Description</Description>
        <Description MaintenanceType="C" DescriptionCode="SHO" LanguageCode="EN" Sequence="1">ShortDesc</Description>
      </Descriptions>
      <Prices>
        <Pricing MaintenanceType="C" PriceType="LST">
          <PriceSheetNumber>J16</PriceSheetNumber>
          <CurrencyCode>USD</CurrencyCode>
          <EffectiveDate>2016-01-05</EffectiveDate>
          <Price UOM="PE">191.0000</Price>
        </Pricing>
        <Pricing MaintenanceType="C" PriceType="RET">
          <PriceSheetNumber>J16</PriceSheetNumber>
          <CurrencyCode>USD</CurrencyCode>
          <EffectiveDate>2016-01-05</EffectiveDate>
          <Price UOM="PE">191.0000</Price>
        </Pricing>
        <Pricing MaintenanceType="C" PriceType="RMP">
          <PriceSheetNumber>J16</PriceSheetNumber>
          <CurrencyCode>USD</CurrencyCode>
          <EffectiveDate>2016-01-05</EffectiveDate>
          <Price UOM="PE">181.4500</Price>
        </Pricing>
      </Prices>
      <DigitalAssets>
        <DigitalFileInformation MaintenanceType="C" LanguageCode="EN">
          <FileName>itemimage.jpg</FileName>
          <AssetType>P07</AssetType>
          <FileType>JPG</FileType>
          <Representation>R</Representation>
          <FileSize>76</FileSize>
          <Resolution>300</Resolution>
          <ColorMode>RGB</ColorMode>
          <Background>WHI</Background>
          <OrientationView>ANG</OrientationView>
          <AssetDimensions UOM="PX">
            <AssetHeight>1367</AssetHeight>
            <AssetWidth>1500</AssetWidth>
          </AssetDimensions>
          <URI>http://www.itemimageassets.xcom/ImgVDHR/BrandX/ABC123.jpg</URI>
        </DigitalFileInformation>
      </DigitalAssets>
    </Item>
</Items>

The XSLT I have created is this (which I know is wrong, but don't know enough to know why):

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
    xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
 xmlns:msxsl="urn:schemas-microsoft-com:xslt"
 xmlns:user="urn:my-scripts"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" > 

    <xsl:template match="/">
        <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">
            <xsl:apply-templates/>
        </Workbook>
    </xsl:template>

    <xsl:template match="/*">
        <Worksheet>
            <Table x:FullColumns="1" x:FullRows="1">
                <Row>
                    <xsl:for-each select="/PIES/Items/Item">
                        <Cell><Data ss:Type="String">
                            <xsl:value-of select="ItemLevelGTIN/text()"/>
                        </Data></Cell>
                        <Cell><Data ss:Type="String">
                            <xsl:value-of select="PartNumber/text()"/>
                        </Data></Cell>
                        <Cell><Data ss:Type="String">
                            <xsl:value-of select="SubBrandLabel/text()"/>
                        </Data></Cell>
                        <Cell><Data ss:Type="String">
                            <xsl:value-of select="Descriptions/Description/text()"/>
                        </Data></Cell>
                        <Cell><Data ss:Type="String">
                            <xsl:value-of select="Descriptions/Description/text()"/>
                        </Data></Cell>
                        <Cell><Data ss:Type="String">
                            <xsl:value-of select="Prices/Pricing/Price/text()"/>
                        </Data></Cell>
                        <Cell><Data ss:Type="String">
                            <xsl:value-of select="Prices/Pricing/Price/text()"/>
                        </Data></Cell>
                    </xsl:for-each>     
                </Row>
            </Table>
        </Worksheet>
    </xsl:template>
</xsl:stylesheet>

Show source
| xml   | xslt   | excel   | vba   2017-01-03 19:01 2 Answers

Answers ( 2 )

  1. 2017-01-03 20:01

    This doesn't give you the exact output you suggested, but it's something a little more usable than raw xml:

    • Click Developer > Import.

    • In the Import XML dialog box, locate and select the XML data file (.xml) you want to import, and click Import.

    https://support.office.com/en-us/article/Import-XML-data-6eca3906-d6c9-4f0d-b911-c736da817fa4?ui=en-US&rs=en-US&ad=US#bmimport_an_xml_file_as_an_xml_list_wit

  2. 2017-01-03 23:01

    As an open-ended file structure, the XML tree can have many dimensions left to the designer. However, tabular structures such as spreadsheets or database tables are two-dimensional (row by column) and hence the corresponding XML must be one child level set for each row:

    <data> 
      <row>
        <col>data</col>
        <col>data</col>
        <col>data</col>
        <col>data</col>
        <col>data</col>
       </row>
    </data>
    

    Therefore, build your XSLT accordingly where you re-style the repeating element, Item, to a flattened one-level set of children. Below walks down the <Item> node using XPath expressions to select nested children according to attribute values. The <xsl:value-of> is used to retrieve text values only wrapped in new tag names while <xsl:copy-of> copies both existing tag name and text value.

    XSLT (save as .xsl file)

    <xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:output version="1.0" encoding="UTF-8" indent="yes" />
    
      <xsl:template match="Items">
        <xsl:copy>
          <xsl:apply-templates select="Item"/>
        </xsl:copy>
      </xsl:template>
    
      <xsl:template match="Item">
        <xsl:copy>      
          <xsl:copy-of select="PartNumber"/>
          <xsl:copy-of select="BrandLabel"/>
          <ShortDescription><xsl:value-of select="Descriptions/Description[@DescriptionCode='DES']"/></ShortDescription>      
          <LongDescription><xsl:value-of select="Descriptions/Description[@DescriptionCode='EXT']"/></LongDescription>
          <Price_LST><xsl:value-of select="Prices/Pricing[@PriceType='LST']/Price"/></Price_LST>
          <Price_RMP><xsl:value-of select="Prices/Pricing[@PriceType='RMP']/Price"/></Price_RMP>
          <xsl:copy-of select="DigitalAssets/DigitalFileInformation/*[local-name()!='AssetDimensions']"/>
          <xsl:copy-of select="DigitalAssets/DigitalFileInformation/AssetDimensions/*"/>      
        </xsl:copy>
      </xsl:template>
    
    </xsl:transform>
    

    VBA (Using MSXML library)

    Public Sub RunXSLT()
        ' REFERENCE Microsoft XML, v6.0
        Dim xmlDoc As New MSXML2.DOMDocument60, xslDoc As New MSXML2.DOMDocument60, newDoc As New MSXML2.DOMDocument60
    
        xmlDoc.Load "C:\Path\To\Input.xml"
        xmlDoc.async = False
    
        xslDoc.Load "C:\Path\To\XSLTScript.xsl"
        xslDoc.async = False
    
        xmlDoc.transformNodeToObject xslDoc, newDoc
        newDoc.Save "C:\Path\To\Output.xml"
    
        Set newDoc = Nothing: Set xslDoc = Nothing: Set xmlDoc = Nothing
    
        ' IMPORT XML INTO NEW WORKBOOK
        Workbooks.OpenXML "C:\Path\To\Output.xml", , xlXmlLoadImportToList          
    End Sub
    

    Output

    XML Transformed

    <?xml version="1.0" encoding="UTF-8"?>
    <Items>
        <Item>
            <PartNumber>ABC123</PartNumber>
            <BrandLabel>Brand X</BrandLabel>
            <ShortDescription>This is a short description</ShortDescription>
            <LongDescription>This is a little longer description</LongDescription>
            <Price_LST>191.0000</Price_LST>
            <Price_RMP>181.4500</Price_RMP>
            <FileName>itemimage.jpg</FileName>
            <AssetType>P07</AssetType>
            <FileType>JPG</FileType>
            <Representation>R</Representation>
            <FileSize>76</FileSize>
            <Resolution>300</Resolution>
            <ColorMode>RGB</ColorMode>
            <Background>WHI</Background>
            <OrientationView>ANG</OrientationView>
            <URI>http://www.itemimageassets.xcom/ImgVDHR/BrandX/ABC123.jpg</URI>
            <AssetHeight>1367</AssetHeight>
            <AssetWidth>1500</AssetWidth>
        </Item>
    </Items>
    

    Excel Table Import

    Excel Table Import

◀ Go back