Dec 132007
 

I’ve created a BizTalk Excel File decoder pipeline.  It accepts Excel 2003-2007 .xls files.  It will parse each worksheet into a child node under the root node you specify.

image 

Here are the Pipeline Component Properties.

image 

Here is an example XLS file.

image

 

The resultant XML file creates the Root Node “SHEETS” as specified in the pipeline properties.  The children of “SHEETS” are the worksheet names listed in alphabetical order.  I dropped 1200 Excel spreadsheets into a receive location and BizTalk parsed each perfectly in less than 10 seconds.

<Sheets xmlns="http://your.name.space.here"> <First> <RowData> <Column1>row1column1</Column1> <Column2>row1column2</Column2> </RowData> <RowData> <Column1>row2column1</Column1> <Column2>row2column2</Column2> </RowData> <RowData> <Column1>row3column1</Column1> <Column2>row3column2</Column2> </RowData> </First> <Fourth> <RowData> <Column1>Fourth</Column1> </RowData> <RowData /> </Fourth> <Second> <RowData> <Column1>Second</Column1> </RowData> <RowData /> </Second> <Third> <RowData> <Column1>Third</Column1> </RowData> <RowData /> </Third> </Sheets>

To learn more about obtaining this Pipeline Component, go here.

  • Matthew Wilson

    The big difference between this one and Matt Maleski’s ODBC Pipeline Component is that this one supports extracting all the worksheets, whereas the ODBC one pulls from only one worksheet. I’ve modified Maleski’s component to always pull from only the *first* worksheet appearing in the workbook (left-to-right, not alphabetically) using an OLEDB method to get the sheetnames.

    This one also differs from Farpoint Spread’s component in that Farpoint’s component has a Schema wizard, with which one can define headers to be separately loaded and also override Excel’s data types for each column. In Farpoint’s component, one can pull from multiple worksheets, but the names of the worksheets must not vary across workbook instances.

  • We are considering creating a wizard for this tool. We will keep you updated through this blog when we have made the change.

  • Eric

    No need to include a schema generation wizard, just take the resultant XML and bring it into VS and create the schema. The nice part about this one is that it pulls all the sheets in the workbook, not just the first. Very simple pipeline to implement and use. And that simplicity is not at the sacrifice of functionality.

  • khyati

    I have a similar requirement for a project.. Is it possible for u share the code..

    Thanks,
    Khyati

  • Ravinder

    Does your company has some expertise to execute EDI files from BizTalk and sending XMLs to Dynamics AX?
    The EDI files 309, 622 are in question

  • Aravind Anna

    Does it remove the header rows if the excel sheet has one?