B2B Integration

 View Only
Expand all | Collapse all

ITX - Excel adapter

  • 1.  ITX - Excel adapter

    Posted Wed June 24, 2020 12:09 PM
    Hi Team,

    I am working on excel adapter. My Source is Excel and Destination is JSON. I facing data related issues. Please help me out.

    Issue1:
    My input file is having mathematical functions (mainly addition + and subtraction -). I have one to one mapping. When I execute the map, it is printing the same data. 

    Input: 100 + 200 - 50
    Output: 100 + 200 - 50

    I want the final value i.e 250 after calculation. Please help me with this.

    Issue2:
    My input cell having value 0 and it is formatted with $, I am getting $ 0 in the output. I don't want to print $ in my output. My output should be "0".

    ------------------------------
    Hemachandra G
    ------------------------------

    #SupplyChain
    #B2BIntegration


  • 2.  RE: ITX - Excel adapter

    IBM Champion
    Posted Thu June 25, 2020 03:32 AM
    Hemachandra,

    Did you try putting an equals sign in front of the formula?

    Thanks.

    ------------------------------
    Paul Brett
     
    IBM Sterling Transformation Extender (ITX) Client Support
    ------------------------------



  • 3.  RE: ITX - Excel adapter

    Posted Mon June 29, 2020 08:46 AM
    Hi Paul, For formulas we have equal sign in front of data. (=100+100-50).

    ------------------------------
    Hemachandra C G
    ------------------------------



  • 4.  RE: ITX - Excel adapter

    Posted Thu June 25, 2020 02:20 PM
    Is this happening when you run the map in Design Studio?   If it is, is it possible for you to upload the following artifacts: .mms, .mtt, json data (how the output should look like) and the excel spreadsheet?  So there should be 4 items all together.

    ------------------------------
    Rex Chan
    ------------------------------



  • 5.  RE: ITX - Excel adapter

    Posted Thu June 25, 2020 02:23 PM
    Is this producible in Design Studio?  Can you upload the .mms, ,.mtt, json file (expected output) and Excel?

    ------------------------------
    Rex Chan
    ------------------------------



  • 6.  RE: ITX - Excel adapter

    Posted Mon June 29, 2020 08:48 AM
    Hi Rex Chan,

    I will share artifacts today.

    ------------------------------
    Hemachandra C G
    ------------------------------



  • 7.  RE: ITX - Excel adapter

    Posted Wed July 01, 2020 07:22 AM
      |   view attached
    Hi Rex Chan,

    Please find artifacts as attachment.

    Input data:
    =750-250
    =1000
    1500
    0
    =0

    Expected Result: 
    500
    1000
    1500
    0
    0

    Actual results: 
    750-250
    1000
    $ 1,500.00
    $ - 0
    0

    As I specified above for Issue-1, I want final value after calculation. For Issue-2, I don't want to print $ in my output.

    ------------------------------
    Hemachandra C G
    ------------------------------

    Attachment(s)

    zip
    Excel_to_Json.zip   15 KB 1 version


  • 8.  RE: ITX - Excel adapter

    Posted Wed July 01, 2020 12:27 PM
    The Json type tree is incorrect.  Can you send me a copy of how the Json would look like if it's correct?  You have a few rows of data, your Json output should reflect that as well.

    ------------------------------
    Rex Chan
    ------------------------------



  • 9.  RE: ITX - Excel adapter

    Posted Wed July 01, 2020 01:06 PM
      |   view attached
    Hi Rex Chan,

    I have attached expected Json output file, how it should actually look like.

    ------------------------------
    Hemachandra C G
    ------------------------------

    Attachment(s)

    json
    ExpectedJsonOutput.json   248 B 1 version


  • 10.  RE: ITX - Excel adapter

    Posted Wed July 01, 2020 01:40 PM
    It's not a valid Json data.  I'll create one for you.  
    What version of ITX are you running?  You can tell by going to Help and then About

    ------------------------------
    Rex Chan
    ------------------------------



  • 11.  RE: ITX - Excel adapter

    Posted Wed July 01, 2020 02:04 PM
    This is modified in v10.  If you need v9, let me know.  ITX is downward compatible.  This means old version maps and type trees can be used in new version.
    The Excel spreadsheet adapter doesn't work on an equation level.  So when you have "=750-250" in your equation, it doesn't convert it to 500, not the calulated value.   I did removed the $ sign as JSON output is defined in text, not as a numeric value.

    Also to process JSON data, you should use the JSON data as your type tree, not the one you created.
    ITX handles XML and JSON without having to import them as type tree.  You simply point to the XSD or JSON data.

    For some reason, when I click Upload file, nothing happens.  I'll see if I can send them to you via email.


    ------------------------------
    Rex Chan
    ------------------------------



  • 12.  RE: ITX - Excel adapter

    Posted Wed July 01, 2020 02:11 PM
      |   view attached
    It's a browser issue.  I used Edge and nothing happens when I clicked Upload file.   I switched to Chrome and it works.  Here is the attached zip.

    ------------------------------
    Rex Chan
    ------------------------------

    Attachment(s)

    zip
    Excel_To_Json_Rex.zip   23 KB 1 version


  • 13.  RE: ITX - Excel adapter

    Posted Thu July 02, 2020 08:41 AM
    I am using 9.0.0.3 version

    You mean to say Excel adapter doesn't support for excel formulas right Rex ? So what ever the data (ex:=750-250) it will print as is.

    Coming to remove formatted $ sign, you have used LEAVENUM funtion to to remove $ sign.

    ------------------------------
    Hemachandra C G
    ------------------------------



  • 14.  RE: ITX - Excel adapter

    Posted Thu July 02, 2020 10:59 AM
    That's correct.  You would use ITX for the transformation/calculation. You may do something like COLA - COLC if they are both numbers.

    ------------------------------
    Rex Chan
    ------------------------------