AnsweredAssumed Answered

Creating XSL to import Office Spreadsheet XML

Question asked by mr_scott on Dec 3, 2018
Latest reply on Dec 5, 2018 by mr_scott

Like • Show 0 Likes0
Comment • 2

I've learned a lot about XML and XSL Style Sheets for exporting XML data from FileMaker and importing from a variety of sources, but I'm struggling how to make use of that knowledge for importing XML files that use Microsoft Office Excel Spreadsheet format:

Specifically, I'm uncertain of how to transform (map) multiple occurrences of "<Cell ss:StyleID="sText"><Data ss:Type="String"></Data></Cell>" within a row to the corresponding field in the FileMaker database.

Here's an example of what I'm trying to import, abbreviated to the first two <ROW> elements (first one is a header row, followed by multiple data rows.

<?xml version="1.0" encoding="utf-8"?>
<?mso-application progid="Excel.Sheet"?>
<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"
    >
     <Styles>
         <Style ss:ID="sHeader"><Font x:Family="Swiss" ss:Bold="1"/></Style>
         <Style ss:ID="sText"><NumberFormat ss:Format="@"/></Style>
         <Style ss:ID="sTextWrap"><Alignment ss:Vertical="Bottom" ss:WrapText="1"/><NumberFormat ss:Format="@"/></Style>
         <Style ss:ID="sNumber"><NumberFormat ss:Format="#0.00########"/></Style>
         <Style ss:ID="sInteger"><NumberFormat ss:Format="#0"/></Style>
         <Style ss:ID="sTextNumber"><Alignment ss:Horizontal="Right"/><NumberFormat ss:Format="@"/></Style>
         <Style ss:ID="sDate"><NumberFormat ss:Format="MM/dd/yyyy\ HH:mm:ss"/></Style>
         <Style ss:ID="sDateWithoutTime"><NumberFormat ss:Format="MM/dd/yyyy"/></Style>
     </Styles>
     <Worksheet ss:Name="Database Export">
         <Table>
             
             <Row>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">entity_ID</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Metal</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Standard_Name</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Country_Location</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Legitimate</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Business_Status</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Research_Notes</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Audit_Status</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">tbl_Feedback</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Company_Website_CM</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Company_Website</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Company_State_Province_Region</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Primary_Contact_Phone</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Related_Company_or_Trader</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Notes</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Name_Alt_25</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Date_Last_Updated</Data></Cell>
                 <Cell ss:StyleID="sHeader"><Data ss:Type="String">Member</Data></Cell>
             </Row>
             
             <Row>
                 <Cell ss:StyleID="sText"><Data ss:Type="String">ABC000001</Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String">Aluminum</Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String">A1 COMPANY.,Ltd</Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String">CHINA</Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String">Not Eligible</Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String"></Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String">Not legitimate - submitted</Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String">Not Applicable</Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String">Please obtain the correct identity.</Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String"></Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String">http://www.google.com/</Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String">Taipei, Taiwan</Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String">800-55-1212</Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String">A1 - Ford</Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String">" processing technology" provider. Following description taken from the manufacturer.</Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String"></Data></Cell>
                 <Cell ss:StyleID="sDate"><Data ss:Type="DateTime">2018-02-22T00:34:36</Data></Cell>
                 <Cell ss:StyleID="sText"><Data ss:Type="String"></Data></Cell>
             </Row>
         </Table>
     </Worksheet>
</Workbook>

Thanks in advance for anyone/everyone that can point me in the right direction on this.

- - Scott

No one else has this question

Outcomes

2 Replies

comment Dec 3, 2018 10:58 PM

What you have is known as the Microsoft Office Excel 2002/2003 XML Format file - sometimes also called SpreadsheetML (although this term is sometimes also used for the other Excel XML format inside .xlsx).

To import it into FileMaker, try the following stylesheet:

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
exclude-result-prefixes="ss">
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>

<xsl:template match="/ss:Workbook">
    <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
        <!-- CREATE FIELDS FROM THE FIRST ROW -->
        <METADATA>
            <xsl:for-each select="ss:Worksheet/ss:Table/ss:Row[1]/ss:Cell">
                <FIELD NAME="{ss:Data}"/>
            </xsl:for-each>   
        </METADATA>
        <!-- IMPORT DATA -->
        <RESULTSET>
            <xsl:for-each select="ss:Worksheet/ss:Table/ss:Row[position() > 1]">
                <ROW>
                    <xsl:for-each select="ss:Cell">
                        <COL><DATA><xsl:value-of select="ss:Data"/></DATA></COL>
                    </xsl:for-each>   
                </ROW>
            </xsl:for-each>
        </RESULTSET>
    </FMPXMLRESULT>
</xsl:template>

</xsl:stylesheet>

5 people found this helpful

Actions

mr_scott @ comment on Dec 5, 2018 9:24 AM
Mark Correct
Correct Answer
Thank you, Michael. Worked perfectly!
Like • Show 2 Likes2
Actions

Creating XSL to import Office Spreadsheet XML

Outcomes

Related Content

Recommended Content