Append Excel column from multiple files in a folder into a single sheet

[adesinoh]

I have over 600 Excel files that look the same in a folder with just one column in all the files. I need to merge this single column from all 600 files into an excel sheet and make the file name for each file appear as the appended column name in the new sheet

[victor_palacios]

I think there are several solutions here: Concatenate data from multiple xls files - #7 by ScottF

[MichaelRespondek]

I moved this topic to Analytics Platform as it is not regarding our Big Data Extensions.
Best,
Michael

[adesinoh]

This has not helped me. I was able to create a work flow to compute the file name into column headers. But the file content does not append into the newly named column header. Only the first file appends into the first column. I need all the columns from each file to append under their file name which has been arranged in column headers.

[victor_palacios]

Please attach a workflow so I can see what you tried and also attach 3 example excel sheets (real or fake data is fine as long as the format is exactly the same as what you would like to accomplish). Looking forward to helping you.

[adesinoh]

I have been struggling to append columns from different excel files into a single worksheet . The only thing I have got right is taking the filename and appending it as column headers.
I have uploaded my workflow together with the workflow I tried to follow but I still could not resolve my column append problem. I also can’t view the settings on the file reader node that I am trying to follow because it is deprecated.
All I need is for the column from every sheet to append under the column name that has been created for each file on the single worksheet. but when it runs it reads the initial file selected in the reader node over and over for as many files as listed in the list file node.

Thanks @victor_palacios

These are 3 examples of the files I have;
A0A3Q5ACI7.xlsx (122.5 KB)
B2RXH2.xlsx (124.6 KB)
O00311.xlsx (126.2 KB)

This is my workflow;
Add filename to column headers and append columns.knwf (1.2 MB)

[victor_palacios]

Add filename to column headers and append columns.knwf (1.2 MB)

Thank you, please review my solution and let me know if this is what you wanted. (Let’s avoid using deprecated nodes whenever possible~)

[adesinoh]

Bravo!!! This is exactly what I wanted. I have tried to replicate your workflow, but I am not getting the configuration right for some of the nodes. Will you be able to share your workflow with me? I will really appreciate this

[adesinoh]

Good morning, Please can you share your KNIME file with me.

[victor_palacios]

Hello, it’s the orange/yellow text (you can download it) from my earlier post.

[adesinoh]

Hello @victor_palacios , You must have attached my initial file back in error. Can you please download the file you attached to your previous post, so you are sure you sent the right file.

[victor_palacios]

Apologies,

filename to column headers and append.knwf (343.3 KB)