Can GrapeCity Spread for BizTalk Handle This?

One of the questions I often receive regarding GrapeCity Spread for BizTalk is, "How does the component handle "this"?" or "What would the schema look like in "this" scenario?" My gut reaction is to state that Spread for BizTalk is an Excel XLS Parser that is designed to work with tabular data in Excel files, but the real answer is that yes, Spread for BizTalk can handle "this", and you tell me what you want the resulting schema to look like.

Spread for BizTalk is, indeed, an Excel XLS Parser that disassembles Excel XLS files. The schema that it parses to at runtime is created at design-time using the FarPoint Spreadsheet Schema Wizard. And, yes, the component is designed to work with tabular Excel XLS workbooks. Tabular data is rows or records of data where the data in each cell or a row is based on the definition of the column. FarPoint Spread for BizTalk currently supports one selectable data range on each Excel sheet. What do you do if you receive Excel XLS workbooks that contain multiple data areas per sheet? Or how about the case where a sheet may or may not have multiple data ranges? All of these questions are valid, and none of them are explicitly supported by Spread for BizTalk; however, none of them are unsupported either. Confused yet? Well, the answer goes back to my gut reaction. FarPoint Spread for BizTalk does what it does, and BizTalk provides a variety of tools to allow you to take the XML results from Spread for BizTalk and do some pretty cool things with it.

I was recently sent an Excel XLS file that included a sheet that looked like this:

Account Number:123
Account Name: Company XYZ
Posting Date raw cost production cost retail cost
1/2/2007 15 11 50
1/2/2007 4 22 40
1/3/2007 76 22 200
1/5/2007 4 17 40
Account Number:456
Account Name: Company ABC
Posting Date raw cost production cost retail cost
1/2/2007 25 34 80
1/2/2007 32 8 60

As you can see, this sheet contains two data areas: one for Company XYZ with Account Number: 123, and another for Company ABC with Account Number: 456. To further complicate matters, the first column contains the Account information, and Posting Date. The schema created by the Spreadsheet Schema Wizard for this Excel file contains four columns (Column0-Column3). By default, the records are parsed from the Excel file as one record per row. So the records are:

Account Number: 123
Account Name: Company XYZ
Posting Date raw cost production cost retail cost
1/2/2007 15 11 50
.
.
.
Account Number: 456
Account Name: Company ABC
Posting Date raw cost production cost retail cost
1/2/2007 25 34 80
.
.
.

This is clearly NOT the desired output record format, but hey, the Excel file is disassembled into XML that matches a schema. At this point, the power of BizTalk Mapping is a solution to get us where we need to go. The first thing we need to to create a schema to represent what we want. We want to preserve the existing record information for records that make sense

{"1/2/2007", 15, 11, 50}
.
.
.
{"1/2/2007", 25, 34, 80}
.
.
.

Except we want to add the account information for each record:

{123, Company XYZ, 1/2/2007, 15, 11, 50}
.
.
.
{456, Company ABC, 1/2/2007, 25, 34, 80}
.
.
.

To achieve this, we need to map:

Column0
Column1
Column2
Column3

to:

Account Number
Account Name
Posting Date
Raw Cost
Production Cost
Retail Cost

We add a map file to our BizTalk application and map the "Cost" elements directly. To handle the conditional nature of the Account Number, Account Name, Production Date we add three Scripting functoids to the map.

All three Scripting functoids are of type "Inline C#". The first functoid handles the Account Number.

If the value in Column0 starts with the string "Account Number:", then set the static variable to the value. Always return the static Account Number value so that it can be used by each record.

If the value in Column0 starts with the string "Account Name:", then set the static variable to the value. Always return the static Account Name value so that it can be used by each record.

If the value in Column0 starts with neither "Account Number" or "Account Name", then return the value and map it to Posting Date.

This sample has illustrated how to map the Spread for BizTalk XML from a complex tabular Excel XLS sheet into a schema that represents the XML data in a format that other pieces of your business process integration solution can consume. There are other ways to achieve this through BizTalk, this is simply the way I chose to illustrate our answer to the question ,"Can GrapeCity Spread for BizTalk handle this?"

Spread for BizTalk parses Excel XLS tabular data. That's what it does, and all that it does, but it does it very well! ;-)

Can GrapeCity Spread for BizTalk handle "this"? You bet.

-Robby