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.
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.
Select the XLS data you are interested in, leaving the Header row, Spacer row, and the Trailer row |
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'.
To persist the data throughout the process, the 'Spread for BizTalk' wizard maps the data to the resulting XML stream |