none
Reading xl file in biztalk RRS feed

  • Question

  • Hi,

    I am receving xl file from vendors and need to consume that file in biztalk application. MS office is not installed on my system so i can't use MS office dll's to achieve this.

    Right now i am using OLDEB to to read the xl file and converting into csv file and then reading csv file . I know it's not look but i am not finding any freeware dll to use here.

     

    Tuesday, October 18, 2016 6:39 PM

Answers

  • Dont use excel as a means of data transfer.

    Excels are not good for this. There are n number of possibilities that can go wrong. The format is one which is actually user system dependent and a number or a date that a user keyed in as MM-DD-YYYY can be read in a different system as YYYY-DD-MM and things like this. 

    Ask ur vendors to provide u CSV itself which will have defined format and structure. Ur vendors can save an excel as CSV and provide u that. 


    Pi_xel_xar

    Blog: My Blog

    BizTalkApplicationDeploymentTool: BizTalk Application Deployment Tool

    LinkedIn: LinkedIn

    Tuesday, October 18, 2016 6:54 PM
    Answerer
  • Well, here's the deal with this.  The first thing you and your employer/customer should do is say no.  I'm being totally, 100% serious.

    Excel is a terrible way to exchange machine data.  (Excel is an awesome app, but for people.)

    I guarantee, 100%, no doubts, you will have continuous problems with this approach with the very real chance it becomes a business liability.

    The only reliable way to use Excel is if the users agree to save as .csv with Headers, you then validate the document by the headers.

    I say this from experience.

    Tuesday, October 18, 2016 7:23 PM
    Moderator

All replies

  • Dont use excel as a means of data transfer.

    Excels are not good for this. There are n number of possibilities that can go wrong. The format is one which is actually user system dependent and a number or a date that a user keyed in as MM-DD-YYYY can be read in a different system as YYYY-DD-MM and things like this. 

    Ask ur vendors to provide u CSV itself which will have defined format and structure. Ur vendors can save an excel as CSV and provide u that. 


    Pi_xel_xar

    Blog: My Blog

    BizTalkApplicationDeploymentTool: BizTalk Application Deployment Tool

    LinkedIn: LinkedIn

    Tuesday, October 18, 2016 6:54 PM
    Answerer
  • Well, here's the deal with this.  The first thing you and your employer/customer should do is say no.  I'm being totally, 100% serious.

    Excel is a terrible way to exchange machine data.  (Excel is an awesome app, but for people.)

    I guarantee, 100%, no doubts, you will have continuous problems with this approach with the very real chance it becomes a business liability.

    The only reliable way to use Excel is if the users agree to save as .csv with Headers, you then validate the document by the headers.

    I say this from experience.

    Tuesday, October 18, 2016 7:23 PM
    Moderator
  • Hi john and Pi_xel_xar,

    yes i am already facing many issues here that's why i asked . I already asked my client to change the file to CSV but they are not ready to change it.

    I have to go with the xl so suggest any approach.


    • Edited by giri_x_w Tuesday, October 18, 2016 7:38 PM updated
    Tuesday, October 18, 2016 7:37 PM
  • Try to explain the risk and the issues. U r trying to build a business process that should be robust and stable . its not a POC or a school/college project i guess.

    It u have absolutely no way around, ask to spend some money and get MS office dll. 

    With that U can do a SaveAs of the excel file in .net code (pipeline) as CSV and then u feed that to biztalk process.

    Or try http://www.cdata.com/drivers/excel/biztalk/ , If this solves ur purpose then spend on this..

    Anything else would not be stable and robust I believe. OLEDB way I dont vote for..


    Pi_xel_xar

    Blog: My Blog

    BizTalkApplicationDeploymentTool: BizTalk Application Deployment Tool/

    Tuesday, October 18, 2016 7:49 PM
    Answerer
  • Unfortunately, there is no reasonable approach to this.

    So, the second thing you need to do is explain that you will have to spend a lot of time, like 4 weeks at least, writing a parser and validator for the Excel content.  And even if you do, they will still have to correct any rejected messages.

    The OLEDB approach is fine. There's really not much wrong it if you run the conversion code in a custom Pipeline Component where you can manage the lifecycle of the input file.

    Some more free advice, you make absolutely no assumptions or guesses about the data.  Your customer/employer has to define exactly how to handle every field, type/conversion/format everything.  If something is slightly off, reject.

    Tuesday, October 18, 2016 8:06 PM
    Moderator