|
Home > Archive > BizTalk Server Applications Integration > September 2004 > BiZTalk2004 and Excel Integration
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
BiZTalk2004 and Excel Integration
|
|
| Tomms 2004-06-22, 12:00 pm |
| Hi All,
How can we Configure Biztalk to accept Excel Spreadsheets and convert them into XML for Mapping
Thanks
Tomms | |
| Gilles [MSFT] 2004-06-26, 5:47 pm |
| Hello,
>How can we Configure Biztalk to accept Excel Spreadsheets and
>convert them into XML for Mapping
It really depends on how your excel files will be saved. Excel 2003 allows you to save as XML
so if you can assume all Excel files to be in XML, you can just define a schema (or dowload the Office
schema from Microsoft's web site) and start receiveing messages in XML.
If your Excel files are csv (comma speparated values), the flat file disassembler allows you to convert them to XML.
Look at http://msdn.microsoft.com/library/e....asp?frame=true
You want to look at "delimited records" since csv files are best parsed with the "delimited record" mode.
If your Excel files are binary files (.xls) then you will have to write a custom pipeline component to convert them to XML.
You might be tempted to call Execl's object model from this custom pipeline component (to load the file and manipulate it).
While this is possible, remember that Excel is a client side program and was never designed to operate as part of a server
component. As a result, you may or may not run into problems when doing this.
In a nutshell, try to get excel files expressed in XML (yes, this is dodging the probl;em, I know ;-) ). If you cannot, try to get them exported
as csv file and use the out of the box flat file disassembler to create XML message in BizTalk. If you really can't do any of the above,
then try the custom disassembler loading excel object model. This might be reliable enough for you if your mesasge stream is not
too large and if you are careful in the imlementation. In that case, you should stress test your solution heavily to be sure. Sometimes,
problems with Office OBject Model used on the server only occur under load ...
Thanks.
-Gilles.
| |
|
| Hi,
here is my expereince on that. I had a client who had the same requirements.
first, it really depends on who and how sends the excel file into the system and what you would like to to with it.
alternative solutions for processing of excel files:
- If you are able to create excel file then you can, similiar to the SDK-Sample, create an excel temple that creates XML for you as input. This is the easy way.
- In my project we were receiving excel files from external resources and it was not possible to predefine Excel templates:
solution:
1- write your own pipeline component to extract the data
2- buy a third party solution:
a) Itemfield is a very powerful adapter, where you can read data from excel and also store data into excel file (2-way). Great tool but expensive if you just use it for excel.
b) there is an XML converter from a company www.rustemsoft.com, is a very powerful tool and it cost ca. 140 USD, it worths to try it.
So, it really depends on what you would like to do with it. In one of my project requirments was to receive both Excel and Access database files (.xls and .mdb). In that case the solution was either to write your own component or to use a tool like rustem
soft.
I hope it helps,
Metin
"Gilles [MSFT]" wrote:
> Hello,
>
>
>
> It really depends on how your excel files will be saved. Excel 2003 allows you to save as XML
> so if you can assume all Excel files to be in XML, you can just define a schema (or dowload the Office
> schema from Microsoft's web site) and start receiveing messages in XML.
>
> If your Excel files are csv (comma speparated values), the flat file disassembler allows you to convert them to XML.
> Look at http://msdn.microsoft.com/library/e....asp?frame=true
> You want to look at "delimited records" since csv files are best parsed with the "delimited record" mode.
>
> If your Excel files are binary files (.xls) then you will have to write a custom pipeline component to convert them to XML.
>
> You might be tempted to call Execl's object model from this custom pipeline component (to load the file and manipulate it).
> While this is possible, remember that Excel is a client side program and was never designed to operate as part of a server
> component. As a result, you may or may not run into problems when doing this.
>
> In a nutshell, try to get excel files expressed in XML (yes, this is dodging the probl;em, I know ;-) ). If you cannot, try to get them exported
> as csv file and use the out of the box flat file disassembler to create XML message in BizTalk. If you really can't do any of the above,
> then try the custom disassembler loading excel object model. This might be reliable enough for you if your mesasge stream is not
> too large and if you are careful in the imlementation. In that case, you should stress test your solution heavily to be sure. Sometimes,
> problems with Office OBject Model used on the server only occur under load ...
>
> Thanks.
> -Gilles.
>
>
| |
|
| quote:
You might be tempted to call Execl's object model from this custom pipeline component (to load the file and manipulate it).
Do you think it would be more appropriate to use ADO.NET access to Excel instead of OM? | |
| Yossi Dahan 2004-07-23, 5:50 pm |
| Itemfield's ContentMaster can convert any Excel document to any text format
(xml or not) and are integrated with BTS 2004
have a look at www.itemfield.com
Yossi
"Tomms" <Tomms.18gsij@mail.webservertalk.com> wrote in message
news:Tomms.18gsij@mail.webservertalk.com...
>
> Hi All,
>
> How can we Configure Biztalk to accept Excel Spreadsheets and
> convert them into XML for Mapping
>
>
> Thanks
> Tomms
>
>
>
> --
> Tomms
> ------------------------------------------------------------------------
> Posted via http://www.webservertalk.com
> ------------------------------------------------------------------------
> View this thread: http://www.webservertalk.com/message269267.html
>
| |
| Gilles [MSFT] 2004-07-23, 5:50 pm |
| Hello,
> You might be tempted to call Execl's object model from this custom
> pipeline component (to load the file and manipulate it).
>
>Do you think it would be more appropriate to use ADO.NET access to
Excel instead of OM?
In the specific case of Excel, ADO would work. You can use the ODBC driver or the OLEDB provider for JET
to read and write data to an Excel workbook.
It would also work in the case of Access, as I am sure you already knew.
Thanks.
-Gilles.
| |
| ChrisTucker 2004-09-10, 2:47 am |
| If you will please lower the cost of your software from over $20K, or package
the excel system seperately, I think more people would be inclined to
purchase it.
"Yossi Dahan" wrote:
> Itemfield's ContentMaster can convert any Excel document to any text format
> (xml or not) and are integrated with BTS 2004
> have a look at www.itemfield.com
>
> Yossi
>
>
> "Tomms" <Tomms.18gsij@mail.webservertalk.com> wrote in message
> news:Tomms.18gsij@mail.webservertalk.com...
>
>
>
| |
| Todd Sussman 2004-09-22, 9:56 pm |
| Chris,
Itemfield is a great product if you deal with a large amount of different
schemas. If all you are dealing with is one of two different files that
need to be parsed, ContentMaster is not for you. Of course, if you do need
lots of various parsers, there is no better product that Itemfield. We have
been using if for many years, and it saves us a lot of time and money.
Todd
"ChrisTucker" <ChrisTucker@discussions.microsoft.com> wrote in message
news:7258C309-2CFE-4E59-8E4A-17A96DD12595@microsoft.com...[vbcol=seagreen]
> If you will please lower the cost of your software from over $20K, or
> package
> the excel system seperately, I think more people would be inclined to
> purchase it.
>
> "Yossi Dahan" wrote:
>
|
|
|
|
|