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:
<?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">
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>
<!-- This "Row" is a header row -->
<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>
<!-- This row is a data row, followed by more than just this one -->
<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
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: