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

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">&quot; processing technology&quot; 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

Outcomes