Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

BizTalk Server 2006: FarPoint XLS File Pipeline Component Schema Wizard

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.

This entry was posted in BizTalk Server. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

12 Responses to BizTalk Server 2006: FarPoint XLS File Pipeline Component Schema Wizard

  1. Thiyagu says:

    Hi ,
    Is there any other options or third-party wizard available for import the XLS and csv file date into the database.

  2. betty says:

    I have seen this information and lots more at http://newfileengine.com/
    It is one of the best searches of the internet! very useful!

  3. jlynch says:

    I believe that FarPoint is working on a Excel assember component and should have a beta ready within a few months.

    Jeff

  4. oana says:

    Helo,

    do you have any idea how to convert an XML file to Excel, using BizTalk?

    thank you

    oana

  5. Ashwini says:

    Hi,
    I am not able to see XLS Flat File wizard in Add New Item inside BizTalk server 2006 undera Schema tab.Can you please help me how to add that template inside that.If possible can you also tell me how to activate template for BizTalk migration wizard inside BizTalk Sereer 2006.

    Note: Please write me at upadhyay.ashwini@satyam.com

    Thanks In advance
    Ashwini Upadhyay

  6. jlynch says:

    Tomas,

    This version is a parser (disassembler) only but we are working on the serializer (assembler) also. Right now it works with single sheet Excel files but we are also working on multi-sheet workbooks as well. FarPoint should have their beta signup and support forums up and running in a few days and I’ll be helping out in the forums.

    Keep those comments coming!

  7. Deepak says:

    Thanks Jeff. It makes sense :)

  8. Jeff,

    It looks way cool, and I’m sure this will be very useful for a lot of folks. Congratulations to the Farpoint team for putting it so nicely together :)

    I’m curious about a couple of things:
    1- I’m assuming the component is a disassembler? or is it more targetted as a decoding component?
    2- Does it deal with multi-page workbooks? if so, how?

  9. jlynch says:

    Deepak,

    Right now it’s a parser component to be used in the receive pipeline. Future versions may be able to serialize XML to an XLS format but it really depends upon the market demand. To begin with, we just wanted a way to get Excel data into an XML format for BizTalk to process.

    Jeff

  10. Deepak says:

    Great Stuff Jeff! Any idea whether the Excel Schema work on Serialization (xml -> Excel) too or is primarily used for parsing(Excel -> xml)?

  11. jlynch says:

    Richard,

    Ping me at jeff.lynch[at]gulfcoastseal.com and I’ll reply back. I can put you in touch with FarPoint. Pricing hasn’t been announced but it’s very, very reasonable and we’re looking for beta sites.

    Jeff

  12. rseroter says:

    Really liking this. I have a customer I spoke to yesterday who needs to do Excel input to a process. Any word on pricing? I know your previous post identified at release time as in the fairly near future.

Leave a Reply