This post is going to be an illustrated walkthrough showing how to use the new FarPoint XLS File Pipeline Component to create a an XML schema from an existing Excel file. To make this process easier, FarPoint has designed this new component with a wizard interface which is very similar to the Flat File Schema Wizard.
The first thing to understand is that this component is designed to parse simple row and column Excel files into XML as illustrated in the walkthrough below.
To create a new schema from an existing XLS file you begin by selecting the XLS File Schema Wizard in the Add New Item dialog box as shown below.
This opens the BizTalk XLS File Schema Wizard and you begin by simply clicking on the Next button as shown below.
This begins the wizard and opens a dialog box where you can Browse for your instance Excel file and also where you enter your schema's Target Namespace.
This opens your instance Excel XLS file in a FarPoint Spread component and allows you to Select all the data in the XLS file used to define your records as shown below.
This is a pretty simple process and all you need to do is select all the rows that you want to include in your XML schema as shown below.
Once you've selected your data rows you can set each element's Name and Type (element centric or attribute centric) as shown below. You can mix and match but I always find it easier to stick with one type or the other.
This creates the XML schema for the Excel file instance as shown below. Note the component also took care of each "header" row (including blank rows if any) so that your Excel file parses correctly. In my Excel file I have two header rows and a blank row between the header and first data row.
The repeating record element is shown below and this is the part of the schema that you use to map to some other format later on.
As you can see from the images below, this schema validates properly in VS2005 as does the instance Excel XLS file.
In fact, the schema generated by this wizard looks and acts exactly like any other (relatively) complicated XML schema. You can use this schema in a pipeline, map or an orchestration just as you would any other schema.
In my next post I show you exactly how that's done and how I've used this cool new "tool" to simplify several business process automation tasks in the real world!
Please let me know what you think so far! We are looking for feedback and for beta testers.