BizTalk Custom Pipeline to Convert Excel file to Flat file

This blog explains how to convert .xlsx to Flat file in BizTalk using Custom Pipeline.

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

Sample Input (Excel File):

Input.png

Sample Output (Flat File):

Output.png

 

Please follow below steps.

  1. Take a class library project and keep code which is present in below Download link.

Download

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

3) Take a BizTalk receive pipeline and add the pipeline component (ExcelToFlatFile) 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 “ExcelToFlatFile”

8) Provide the below information in pipeline properties.

8.png

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

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

//TargetFolderLocation: Path of the output flatfile

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

9) Take a send port to send the result file (Success/Failure) to file locations and provide below properties. Result file will be having Success message in case of Flat file conversion is successful, otherwise it will be having exception message.

9.png

Note: Actual flat file will be stored in the path which you have provided in Receive pipeline properties.

10) Start the BizTalk application and restart host instances.

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

Advertisements

2 comments

  1. Great explanation, followed each and every instruction and able to convert Excel(.xlsx) to txt file in first attempt. Thank you.
    Rgds, Raees

    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