Forum Discussion

MikeP's avatar
MikeP
New Contributor II
3 years ago

Excel Parser not producing empty document view

Hello Community, I have a problem where my excel parser snap is not producing an output document view when I’m parsing a file with only headers present and no actual data (this is a valid business scenario we have to deal with). I have the same pipeline logic when dealing with CSV parser (with empty data and only headers file), and I at least get an empty document view to check against (using an expression like $.isEmpty()).

Is this a bug in the Excel Parser snap? In the views tab on the excel parser I have output min of 1, so I should get a document output to check against (like the csv parser does), empty or not.

There is nothing of any help in the documentation for this Excel Parser snap I can see to help me, so any help or workarounds to this would be appreciated.

Thanks
Mike

mapper

2 Replies

  • ptaylor's avatar
    ptaylor
    Employee
    5 years ago

    @acesario I think what I’m understanding is that one of your files might have an array with more than one element, but the default parsing turns each element into a separate output document. You can change that by setting “Process array” to false on the JSON Parser. That will produce a single output document whose data is the full array. Before you can feed that into the Join, you’ll have to use a Mapper to map the array to the field of an object. I’ve attached a sample pipeline.

    Community 8107_2020_08_27.slp (9.8 KB)

    Here are the inputs…

    fileA.json:

    [
    {
        "file": "A"
    }
]

    fileB.json:

    [
    {
        "file": "B",
        "id": 1
    },
    {
        "file": "B",
        "id": 2
    }
]

    Output of the Join:

    [
  {
    "fileA": {
      "file": "A"
    },
    "fileB": [
      {
        "file": "B",
        "id": 1
      },
      {
        "file": "B",
        "id": 2
      }
    ]
  }
]

    That combines all of the data from both input files into a single document. You can modify the Mappers to move things to the right places.

    • acesario's avatar
      acesario
      Contributor II
      5 years ago

      @ptaylor Thank you for the sample pipelien and for the info on “Process array”… that is very helpful.
      I very much appreciate your patience and persistence as I learn this.

  • acesario's avatar
    acesario
    Contributor II
    5 years ago

    What if I were to transform the first input file based on name?

    Transform: Instance”:“Transaction Name" to Instance”:“TRANSACTION" so that
    Input0
    “Instance”:“TRANSACTION”
    “ID”:“bae77”

    Input1
    “TRANSACTION”:“AC00014623”

    Still, trying to use input0 as a a mapping in this case.

  • ptaylor's avatar
    ptaylor
    Employee
    5 years ago

    Try a Join snap with a “1 = 1” join path as shown here:

    Followed by a Mapper:

  • acesario's avatar
    acesario
    Contributor II
    5 years ago

    @ptaylor Thanks for the response. This seems to work fine for one row, but not for multiples, especially since I have some unmatched.

    Can I ask a few questions on use of a join?

    If I want to combine 2 documents (one with values, one with IDs) into one as follows:

    Desired Output:
    [
    {
    “TRANSACTION”: “AC00014623”,
    “FIRST_NAME”: “Jorge”,
    “LAST_NAME”: “HereorThere”,
    “TRANSACTION_WID”: “bae771d54a9d018123ac687fea12800c”,
    “FIRST_NAME_WID”: “bae771d54a9d01f0a3b4687fea12810c”,
    “LAST_NAME_WID”: “bae771d54a9d011c59ba687fea12820c”
    }
    ]

    or, in the case of multiples, using the same WIDS:
    [
    {
    “TRANSACTION”: “AC00014649”,
    “FIRST_NAME”: “Sarah”,
    “LAST_NAME”: “Whereever”,
    “TRANSACTION_WID”: “bae771d54a9d018123ac687fea12800c”,
    “FIRST_NAME_WID”: “bae771d54a9d01f0a3b4687fea12810c”,
    “LAST_NAME_WID”: “bae771d54a9d011c59ba687fea12820c”
    },
    {
    “TRANSACTION”: “AC00014649”,
    “FIRST_NAME”: “Collette”,
    “LAST_NAME”: “PorterBeer”,
    “TRANSACTION_WID”: “bae771d54a9d018123ac687fea12800c”,
    “FIRST_NAME_WID”: “bae771d54a9d01f0a3b4687fea12810c”,
    “LAST_NAME_WID”: “bae771d54a9d011c59ba687fea12820c”
    }
    ]

    I have uploaded my slp file with what I have so far.Sub - WID Joiner Test_2020_08_25.slp (24.4 KB)

    • ptaylor's avatar
      ptaylor
      Employee
      5 years ago

      I’m really unclear on what your use case is. You said what the Desired Output is, but what’s the input?

      Also, please read the documentation for the Join snap if you haven’t yet.

  • acesario's avatar
    acesario
    Contributor II
    5 years ago

    @ptaylor Apologies that I only put the input files in the attachment. Here they are for your reference:
    Input file 1
    [{“TRANSACTION”: “AC00014623”,
    “FIRST_NAME”: “Jorge”,
    “LAST_NAME”: “Whoever”}]

    Input file 2:
    [{“TRANSACTION_WID”: “bae771d54a9d018123ac687fea12800c”,
    “FIRST_NAME_WID”: “bae771d54a9d01f0a3b4687fea12810c”,
    “LAST_NAME_WID”: “bae771d54a9d011c59ba687fea12820c”
    }]

    The use case at this point is basically an APPEND the WID values to each input1 document. File 2 does not change. File 1 may have many rows.

    Note: I was able to accomplish this via hard coding the values to variables in a mapper, but the reference id’s may change from time to time, and it is better practice to pull from a file, or web service call. I hope this helps explain what I’m trying to accomplish.

    • ptaylor's avatar
      ptaylor
      Employee
      5 years ago

      Sorry, I’m still not following. There are no values in common between the two files, so how would you expect a join to work?

  • acesario's avatar
    acesario
    Contributor II
    5 years ago

    @ptaylor Correct. There are currently no matched fields. In other tools, I have been able to append one file with another.

  • koryknick's avatar
    koryknick
    Employee
    5 years ago

    @acesario - From what you are describing, I think @pataylor provided the correct solution with the Join snap using a 1=1 condition. Assuming that file 1 has an arbitrary number of records, and file 2 has only 1 record, it would successfully append the fields in file 2 for each record in file 1. This would match what you did with the hardcoded values in the Mapper that you describe earlier.