You may already know my BizTalk Pipeline Components Extensions Utility Pack project available on GitHub for those who follow me. The project is a set of custom pipeline components (libraries) with several custom pipeline components that can be used in received and sent pipelines, which will extend BizTalk’s out-of-the-box pipeline capabilities.
This month my team and I update this project with another new component: ODBC File Decoder Pipeline Component.
ODBC File Decoder Pipeline Component
ODBC File Decoder Pipeline Component is, as the name mentioned, a decode component that you can use in a receive pipeline to process DBF or Excel files. Still, it can be possible to process other ODBC types (maybe requiring minor adjustments). The component uses basic ADO.NET to parse the incoming DBF or Excel files into an XML document.
If consuming DBF files is not a typical scenario, we can’t say the same for Excel files. Yet, we often find these requirements, and there isn’t any out-of-the-box way to process these files.
Honestly, I don’t know the original creator of this custom component. I came across this old project that I found interesting while organizing my hard drives. However, when I tested it in BizTalk Server 2020, it wasn’t working correctly, so my team and I improved and organized the structure of the code of this component to work as expected.
How does this component work?
If we take has an example and Excel File (.xls) that has a table with:
- FirstName
- LastName
- Address
- Position
We can use the ODBC File Decoder Pipeline Component to process these documents. First, we need to create a custom pipeline component and add this component to the decode stage. Once we publish this pipeline, we can configure it as follows to be able to process these types of Excel documents:
- ConnectionString: ODBC Connection String
- For Excel documents: Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;
- For DBF: Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;
- DataNodeName: Rows node name for the generated XML message
- For example: Line
- Filter: Filter for Select Statement
- Leave it empty
- NameSpace: Namespace for the generated XML message
- For example: http://ODBCTest.com
- RootNodeName: Root node name for the generated XML message
- For example: TesteXMLResult
- SqlStatement: Select Statement to Read ODBC Files
- For example: SELECT * FROM [Sheet1$]
- TempDropFolderLocation: Support temp folder for processing the ODBC Files
- For example: c:\Temp\odbcfiles
- TypeToProcess: Type of file being Processed
- 0 to process Excel
- 1 to process DBF
The outcome result will be an Excel similar to this:
<?xml version="1.0" encoding="utf-8"?><ns0:TesteXMLResult xmlns:ns0="http://ODBCTest.com">
<Line>
<FirstName>Fred</FirstName>
<LastName>Black</LastName>
<Address>187 Main Street</Address>
<Position>Sales Lead</Position>
</Line>
<Line>
<FirstName>John</FirstName>
<LastName>Smith</LastName>
<Address>182 Front Street</Address>
<Position>Marketing</Position>
</Line>
<Line>
<FirstName>Sally</FirstName>
<LastName>White</LastName>
<Address>183 Main Street</Address>
<Position>Marketing</Position>
</Line>
</ns0:TesteXMLResult>
Does it work with Xlsx files?
Honestly, I didn’t try it yet. I didn’t have that requirement, and I only remember this scenario now that I’m writing this post, but it should be able to process it. The only thing I know is that we need to use a different connection string, something similar to this:
- Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties=”Excel 12.0 Xml;HDR=YES”;
- or Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties=”Excel 12.0 Xml;HDR=YES;IMEX=1″;
Download
THIS COMPONENT IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.
You can download ODBC File Decoder Pipeline Component from GitHub here:
