How to convert XLS files to XML?

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.

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.

Moreover, 'Spread for BizTalk' automatically detects whether there was a header on the Excel sheet and then it automatically includes this header as Field name for the respective columns. You can modify these headers, if you want. It shows you the Field type, Data type and data as well. The 'Field Types' are the standard XML field types that are 'Element' and 'Attribute'. The wizard automatically determines an appropriate data type and sets it for each column. Now, finally click on the 'Finish' button to end the process.

When the wizard closes, a schema file is generated. Have a look at the generated schema in the figure below. Suppose you had two sheets in the Excel file and you did not select anything from the other sheet, even then you see that it is mapped. Why?

This is because the spread does not allow you to do destructive reading of the incoming data, and so it needs to persist data throughout the process. And thus, its element name is given as unmapped data.

Now, look at the property of the schema that we have just created. It shows you the input instance filename that was used to create the schema. Right click on the schema file in the solution explorer, and select 'Validate Schema.' In the output window you can find the path of the XML file that is generated during the validation process. What actually happens, while validating an instance is that an XLS file is parsed and XML is created. Then the XML is validated against the schema.

Now go back to the receive pipeline, and open the property of the 'Spreadsheet Disassembler', which is in Disassembler stage. Now add the document schema that we have created and rebuild the solution. And finally, deploy the solution to 'BizTalk Server'.