Consolidate Header file and Data file

[darshthakkar]

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

[Aleksandar_A · Accepted solution]

Hello @darshthakkar,

If I’m understanding correctly you can achieve it by using the sl.zipObject function.

Before that you have to turn the keys and the values in two separate arrays.

In the router we are separating the keys (the first object) and the values by using the snap.in.totalCount function.
Then, we append the keys to each object values and finally we zip them together.

Please refer to the attached pipeline below:
sl-comm-excel-headers_2023_06_12.slp (9.5 KB)

Let me know if this helps.

Regards,
Aleksandar.

[darshthakkar]

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]

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

[darshthakkar · Accepted solution]

Utilizing the solution provided by @AleksandarAngelevski with a minor tweak (of removing Router snap) as below:

Thank you @AleksandarAngelevski for your time and assistance on this one, it is much appreciated.
Closing this thread now.

Regards,
Darsh

[Aleksandar_A]

Glad to be of help Darsh! 😀

[darshthakkar]

Thank you as always 😊