XLS to XML in Just a Few Steps

This is a very useful tool that converts ordinary Excel files into XML, so that it can be placed in a MS BizTalk orchestration. It consists of a Spreadsheet Schema wizard that integrates with Visual Studio 2005

Friday, June 29, 2007

Print

Comment

Email

Digg

Del.icio.us

Reddit

Twitter

This a really useful tool for organizations that have done Enterprise Application Integration using Microsoft BizTalk Server. It helps convert XLS files into XML, which can then be integrated into a BizTalk orchestration. A lot of data in organizations lies in Excel files, and it's a challenge to integrate them with other applications.So even if you've integrated multiple business applications using a product like BizTalk Server, taking data from Excel files and using them for these applications can be quite challenging. However, if that data is converted into XML, then it can be read not only by BizTalk, but even by other applications.

In this article, we'll talk about a tool called FarPoint Spread for BizTalk Server 2006.It has a 'Spread for BizTalk' component that gets into Visual Studio 2005 BizTalk project, allowing you to create a schema, which in turn determines mapping of the resulting XML stream. At run time itself the Excel data is parsed and disassembled into XML, and that is mapped with the schema that you have created with the 'Spread for BizTalk' wizard. Hence, the 'Spread for BizTalk server 2006' component has automated the parsing of XLS message stream into XML message stream. Before using the tool, it's assumed that you already have BizTalk Server running in your organization, and you also have Visual Studio 2005. Let's see how to use the 'Spread for BizTalk' wizard.

Creating a schema
Take any Excel file. Now, we will extract data from it and convert it into an XML stream. To start with, open Microsoft Visual Studio 2005 and start an empty BizTalk Server project. Add 'Receive pipeline;' this can be done by adding new item to your project. In the toolbox, under the BizTalk Server components, you will see the 'Spreadsheet Disassembler' component. Drag this component and drop it in the disassembler stage of your receive pipeline. Note, this is the component of 'Spread for BizTalk' and you can only set the 'Document Schema' property of this component. 'Document Schema' is used to define the schema that should be used to map the incoming stream.

Microsoft XLS data is converted to XML stream when parsed through 'Spread for BizTalk' component

Now, add new schema file to your project, which can be done by adding new item. Select 'Spreadsheet Schema Wizard', which will finally invoke the schema wizard. Browse the Excel file from the wizard that you want to use. You can also set target namespace and the code page, which is basically used to encode the resulting XML. Proceed to the next step. You can choose any additional settings and apply them to the resulting schema. For example, you can remove the hidden columns that are on the Excel sheet and proceed to the next step.

Now, the Excel file is loaded into the wizard and you can select the data that you want to use for conversion into XML stream. Exclude the Header row, Trailer row and the Spacer row, because we are not interested in them. Now, select the data that you want to use, and click on the 'Next' button to proceed further. In the next step, you will find that only the selected data is shown.

Page(s)   1  2