Forum Discussion

darshthakkar's avatar
darshthakkar
Valued Contributor
3 years ago
Solved

Consolidate Header file and Data file

Hi Team,

I’m having 2 separate excel files; one containing the headers ONLY and the other one having the actual data. How can I MERGE these 2 files into a single entity so that further transformations can be applied and eventually sent to downstream system?

Sharing some screenshots of the sample data and solution achieved thus far:

(1) Payload (it will always have the data and NOT the headers thus unchecked the option of “contains headers” in excel parser)

(2) Header File (it will always have the header details ONLY, again unchecked the option of “contains headers” in excel parser)

Used a UNION snap and got the below output:

Output required:

Thanking in advance for your time and assistance.

Regards,
Darsh

pipeline
  • koryknick's avatar
    koryknick
    2 years ago

    kumar25 - I suggest you use Outer join type and use a Router to see which records matched based on missing key values

9 Replies

  • darshthakkar's avatar
    darshthakkar
    Valued Contributor
    3 years ago
    Copy LinkReport

    I used a UNION snap to merge the header and data file, after that I used the snaps you suggested from Router and below is the output:

    I have intentionally used a file writer at the end because the output preview is misleading many a times and this time it was no different. It took the actual header as HEADERS in output preview but as soon as the pipeline was executed, the headers changed and the output changed eventually.

  • darshthakkar's avatar
    darshthakkar
    Valued Contributor
    3 years ago
    Copy LinkReport

    Removed the Union and Router snaps if we are getting separate files from upstream and the required output can be achieved.

  • darshthakkar's avatar
    darshthakkar
    Valued Contributor
    3 years ago
    Copy LinkReport

    I’m not planning to write the file as I need to make some transformations after the header and data have been merged.

    If I had to write it off, it would have been achievable via excel formatter itself with the below settings:

    And the file written would be as below:

  • darshthakkar's avatar
    darshthakkar
    Valued Contributor
    3 years ago
    Copy LinkReport

    @AleksandarAngelevski - Firstly, Thank you for sharing the solution.
    Secondly, I do have some questions which needs some clarification, if you have any.

    a) I have header and data file coming as separate so how do I merge it? May be a Union snap OR Join Merge?
    b) If I merge the data using pointer a) above-mentioned, how do we ensure that the headers would be persistent in the first row itself?
    c) Reason behind asking b) is that the router condition would be separating the first object from the rest of the data.

    Thank you.