BizTalk World

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):

Sample Output (Flat File):

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

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

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.

//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.

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