cancel
Showing results for 
Search instead for 
Did you mean: 

Consolidate Header file and Data file

darshthakkar
Valued Contributor

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)
image

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

Used a UNION snap and got the below output:
image

Output required:
image

Thanking in advance for your time and assistance.

Regards,
Darsh

2 ACCEPTED SOLUTIONS

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.

image

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.

View solution in original post

darshthakkar
Valued Contributor

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

image

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

Regards,
Darsh

View solution in original post

9 REPLIES 9

darshthakkar
Valued Contributor

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:
image

And the file written would be as below:
image

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.

image

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.

Thank you @AleksandarAngelevski, trying this right away and will keep you posted with the findings.

darshthakkar
Valued Contributor

@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.