Consolidation of several Excel files and the addition of the source column

[alex1368]

Hello
I have a folder containing several excel files that I want to merge together.
I want the name of each Excel file to be inserted as source in a separate column in the consolidated file and displayed in the output.
Please guide me in this regard
Sample file that I have

Sample file in my mind

[rfeigel]

See if this helps.

Merge multiple CSVs into one and add the file name as column

I can merge CSVs now but my question is how do i add the source file name as a column on the consolidated file?

[alex1368]

This is exactly my question

[rfeigel]

Look at the response which shows how to configure the reader.

[tqAkshay95]

Hello @alex1368 ,

You design a workflow like the following attached image and configure each node

also, I attached a workflow sample for you and try it.

input data:

Workflow:

output:

workflow:

merge_multiple_file.knwf (17.5 KB)

[takbb]

Hi @tqAkshay95 and @alex1368, the workflow you proposed would work, but just to clarify… similar to the post referenced by @rfeigel, which related to CSV files but which applies equally to the Excel Reader, there is no need to perform the loop as the Reader can do the whole job by itself, if there isn’t additional functionality required.

You may also be interested to know an alternative loop if you were going to use a loop (perhaps because there was some other functionality needed on a “per file” basis), or maybe you want to introduce error handling around the Excel Reader so you can handle problems with specific files (which is certainly a very good reason for using a loop instead), the Table Row to Variable loop would perform the operation of the Chunk Loop + Table Row to Variable combination, and a Variable To Table Column could take the place of the Cross Joiner if it were needed

But… for this specific case, the Excel Reader alone should do the job (and this is what @rfeigel was referring to)

The “folder” option on the “File and Sheet” tab replaces the need for the loop as it can read all files in a folder, and the filter options can restrict the list if necessary:

On the “Advanced” tab (KNIME 5.2), you’ll find the ability to “Append file path column” as shown below.

In KNIME 4.7, this is the “Advanced Settings” tab, and this tab looks quite different, but the option appears as “Append path column” just above the preview pane.

Turning on “Support changing schemas” means that if on an execution of the workflow, you’ve changed the layout of the Excel files, it will work with the new layout (schema). If you are reading multiple files, then “Fail if schemas differ” may be a useful option, if you are expecting all files to match the same format, but if your workflow can correctly handle the situation if they don’t (e.g. some files have missing/additional columns), then untick that option.