in Search

Do you Twitter? Follow us @CodeBetter

Jeff Lynch [MVP]

Everything E-Commerce!

BizTalk Server 2004 - Excel Parser News

Today we receive a large number of B2B transaction documents in both XML and flat-file formats but some of our trading partners would like to send us Excel files. Since BizTalk Server 2004 doesn't come with a native Excel Receive Adapter or Parser, I usually use the new Excel XML Tools Add In or the new Excel XML Toolbox for Microsoft Office Excel 2003 and manually export the data into an xml file.

What I'd really like is a BizTalk Server 2004 Receive Adapter or Parser that will allow me to drop an Excel file into a folder and parse the Excel data into an XML format. I know there are a few "highly priced" bolt-ons for BTS2004 that may do this but I'm really looking for a less expensive and simple way to accomplish this without a lot of programming!

Anyone know of an existing adapter or parser that will do this?

UPDATE: GOOD NEWS IS ON THE WAY!

I've been speaking with a well known .NET component development company and they are beginning development of a BizTalk Excel Parser (Disassembler Pipeline Component) that will parse an Excel workbook into XML for further processing by BizTalk. I can't give you the details yet but after speaking with their developer it looks like the BizTalk Community will FINALLY have a good way to work with Excel data! I'll post more information as development and testing proceeds.

I'm trying to get a feel for how many other folks are interested in an Excel parser for BizTalk so post a comment and let me know!

Published Dec 15 2005, 04:45 PM by jlynch

Filed under: BizTalk Server

Comments

Adam said:

Try saving the exel document as xml.. Biztalk should have no problems with that.

# December 15, 2005 6:53 PM

bizdevSwe said:

We have been waiting a long time for this one... Is it both for BTS2004 and BTS2006, any release dates?

# December 16, 2005 4:19 AM

jlynch said:

First comment:

I'm not trying to be a smartass but I'd like to "automate" the processing of Excel documents using BizTalk. Having a user save the Excel file as XML hardly qualifies as "automation" or "integration".

Second comment:

No release dates yet but I believe the plan is to have versions for both BTS2004 and BTS2006 available in early 2006.

Jeff

# December 16, 2005 11:03 AM

Sam said:

It's worth learning a scripting language. Python, Perl, Ruby, any of these should let you build a directory monitoring conversion utility in about 20 lines of code. You could goto XML, CSV, whatever you want.

Is it a "beautiful" or "pure" solution? Not really. Does it work? Reliably? Day in and day out, for only about an hour's effort? Yup. I know I myself tend to get caught up in "purity" arguments with myself, but if you're curious about what such a solution might look like shoot me an email (ssmoot is my gmail account name) and I'll see if I can't whip somethin' up and answer a few questions for ya.

If not, good luck, it's not an insurmountable problem in c# either (it'll just take 5 times the code ;)

If you want a more "pure" solution, can't help ya, but I'm sure you'll figure something out, and I'll be looking forward to a future blog post where you clue me in a bit more on the inner workings of BizTalk. :-)

# December 16, 2005 7:52 PM

jlynch said:

Sam,

Thanks for the comment. I agree that I could write the code necessary to convert an Excel file to XML and then pass it to BizTalk for further processing IF I had six months of free time to devote to it. The component development company currently working on this has already spent months and months of programming time working out how to convert Excel to XML and I'm working with them on integrating this into BizTalk. I'm from the old school of end-user development and really do believe "only build what you can't buy or what you can't afford to buy".

# December 17, 2005 1:25 PM

Sam said:

I agree with your sentiments, and if that's the way you want to go, more power to ya.

I think "6 months" is a pretty big exagerration though as long as you have access to an actual Excel installation for Office Automation.

require 'win32ole'

class Excel
end

excel = WIN32OLE.new('Excel.Application')
excel.Visible = false

WIN32OLE.const_load(excel, Excel)

workbook = excel.Workbooks.Open(ARGV[0])
workbook.SaveAs(ARGV[1], Excel::XlFileTypeXml) # wild guess
excel.Quit

: ruby xls2xml.rb input.xls output.xml

Of course that might not be an option for your particular scenario for one reason or another.

# December 17, 2005 8:58 PM

Deepak said:

Personally I think It would be of immense use for companies who receive thier data is excel sheet. It's just pain to have a manual or semi-auto process somewhere where you know you could automate it.

It's just great to have more adapters like this!

# December 20, 2005 3:18 PM

mj said:

We have been waiting for a very long time and am still waiting. We have many B2B customers that would like to send us excel spreadsheets. It would really be great to be able to automate the process of converting the excel spreadsheets into an xml file using
BizTalk 2004 and BizTalk 2006 etc. Please if you can, put a rush on this adapter as we are in competition with gaining future business!

Thanks :)

# January 19, 2006 12:40 PM

TerryG said:

we could use an excel parser for BT2K4 asap.

# January 26, 2006 2:41 PM

Jeff Lynch said:

As many of you will remember, I've been on a quest to find a reasonably priced Excel parser for BizTalk Server 2004 and 2006. I'm currently working with a very well known .NET component development company to create an Excel Disassembler Pipeline Component

# January 29, 2006 8:42 PM

Jeff Lynch said:

# January 29, 2006 9:04 PM

Raclede said:

Hi Jeff,

I've already develop a Excel Disassembler and its working see my blog.

http://weblogs.sqlteam.com/randyp/archive/2006/02/09/8990.aspx

# February 8, 2006 9:51 PM

Jeff Lynch [MVP] said:

I have some great news for BizTalk Server 2006 developers. FarPoint Technologies and I have been working together for the past six months on a new (Excel) XLS File Pipeline Component for BizTalk Server 2006 and it's almost ready for public beta.

# May 6, 2006 2:14 PM

Brian Kinser said:

Where is the Excel Parser, i have some very tricky stuff and i am having to use flat file parser to get into this worksheet, grrrr Matts pipeline is a great starter but unless you have nice records that arent complex you are kinda stuck http://objectsharp.com/cs/blogs/matt/archive/2006/01/29/3778.aspx

# January 8, 2007 2:33 PM

jlynch said:

Brian,

FarPoint has developed a full featured Excel disassembler component for BizTalk Server 2006.

http://www.fpoint.com/biztalk/default.aspx

Jeff

# January 8, 2007 2:53 PM

Jason Stott said:

I created a folder watcher that will take an excel spreadsheet, or even multiple sheets and put them into XML. Check out my blog. http://blog.biztalk-info.com/archive/2008/02/22/Convert_Excel_spreadsheets_to_XML_.XLS_to_.XML.aspx

# March 14, 2008 2:43 AM

Name (required)

Your URL (optional)

Comments (required)

Remember Me?

Enter the numbers above:

Add

This Blog

Syndication

News

Jeff Lynch Development, Ltd.

Jeff Lynch [MVP]

BizTalk Server 2004 - Excel Parser News

Comments

Adam said:

bizdevSwe said:

jlynch said:

Sam said:

jlynch said:

Sam said:

Deepak said:

mj said:

TerryG said:

Jeff Lynch said:

Jeff Lynch said:

Raclede said:

Jeff Lynch [MVP] said:

Brian Kinser said:

jlynch said:

Jason Stott said:

Leave a Comment

This Blog

Syndication

Recent Posts

Tags

News

BizTalk Bloggers

Commerce Server Bloggers

Microsoft Heros

Team System Bloggers

Archives