This blog explains how to convert .xls to Xml file in BizTalk using Custom Pipeline.

Below pipeline component is generic one, which can be applied to any excel files for xml conversion.

Sample Input (Excel File):

SampleInput.png

Sample Output (Xml File):

SampleOutput.png

Please follow below steps.

  1. Take a class library project and keep the code mentioned in Download link below.

Download

2) Provide strong name key, build and deploy to GAC.

3) Take a BizTalk receive pipeline and add the pipeline component (ExcelToXmlFileConversion) to Toolbox

3.png

4) Drag and Drop the Pipeline component from toolbox to Decode stage of Receive pipeline.

4.png

5) Build BizTalk project and deploy.

6) In Admin Console, take one-way receive port and receive location.

7) In Receive pipeline, browse the pipeline “ExcelToXmlFile”

8) Provide the below information in pipeline properties.

8.png

//ConnectionString: Provide the same connection string as in the screenshot above

//DataNodeName: Root name of the repeating node

//NameSpace: BizTalk message namespace.

//SqlStatement: [Sheet1] is the sheet name in the excel sheet.

//TempDropFolderLocation: This is temporary path you need to provide to perform some internal operations by the pipeline code.

9) Take a send port and provide below properties.

9.1.png
9.2.png

10) Start the BizTalk application and restart host instances.

11) Drop an Excel file in Input location and you can see the Xml file in Output location

8 thoughts on “BizTalk Custom Pipeline to Convert Excel file to Xml file

  1. I have a question, Is it possible to consume an excel sheet in Orchestration whose columns are unknown. If yes, What will be the message type of it and the schema for it. (this is in reference to scenario where I have the Excel to XML pipeline already in place)

    Like

  2. While using the customer pipeline in UAT and DEV i am getting connection string issue like “provider not registered in local machine error” for both adm and jet.
    Please advise me for The resolution of issue asap.
    Thanks in advance.

    Like

  3. Hi team,
    I followed your process in Dec the components working fine. But in Q&A I am getting the same issue provider not found after fallowing the same process as you suggested. Please assist on this.

    Like

    1. Hi Pavan,
      we didn’t understand this line: process in Dec the components working fine. But in Q&A.
      Does it mean, it is working in development and not working in UAT or test server?
      if in that case you need to check the prerequisites software for MS Excel as suggested in above comments and does UAT or Test server have MS Excel on that server?

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s