<< Internet Explorer immediately closes when trying to open XML documents | Home | Having problems undeploying that pesky BizTalk Assembly? >>

BizTalk Excel (XLS) pipeline component

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.

posted @ Monday, March 17, 2008 2:20 PM

Print

Comments on this entry:

 re: BizTalk Excel (XLS) pipeline component

Left by Matthew Wilson at 3/22/2008 6:59 AM
Gravatar

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.

# re: BizTalk Excel (XLS) pipeline component

Left by Eric at 3/24/2008 9:43 AM
Gravatar

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

 Pipelinecomponent

Left by Eric at 6/10/2008 3:31 PM
Gravatar

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.

Your comment:



 (will not be displayed)


 
 
 
Please add 5 and 4 and type the answer here:
 

Live Comment Preview: