Combine excel files into single workbook in sheets

KNIME Analytics Platform
1

Hi Guys,

I want to combine all excel files(xlxs, xls) in a folder into single workbook but in different sheets(not combined in single sheet).
image

I have gone through the example and I am able to merge all the files in one workbook but in single sheet.

  • Is it possible to merge the files in 1 workbook but with different sheets?

  • Is it also possible to preserve the formatting which is present in the sheet?

  • I Intend to take the sheet names based on the row 2 or row 3 of the files.

    • If row 2 or 3 has a Text “Deferred Balances Report”. I want to use the name DT for the sheet.
      image
    • If the text in row 2 or 3 is “Effective Rate Report” I want to use the name ETR.
      image
    • If none of the criteria match and use the sheet name which it has in the excel workbook. Like in this case AP, AR,AT
      image
      -if this is complex to setup I would just like to use the default sheet name in the present in the source file. If sheet already exist with the name just add sheet1(1) like it is done in VBA.

  • Also, if this is done then loop into the next folder and do the same exercise(which is basically combining them on the same logic) for files present in the Test - 1 folder which would also have the same kind of files

image

I am uploading the test data. Thanks

preTest.zip (109.7 KB)

2

Hi,

Here is a base on which you can work

Knime test.knar (125.7 KB)

Kind regards
Andrej

3 Likes
3

Thanks for providing the response. I am a new user for Knime. Could I ask you couple of questions?

  • Is it not possible in Knime to preserve the cell formatting or the formula’s that was present worksheet?
    Input
    image

    Output

    image
    image566Ă—559 11.2 KB

  • What is the difference between the Excel Reader(XLS) (deprecated) and and Excel Reader (XLS)
    image

  • What is the purpose of making the three connectors and interconnecting them also. I am trying to understand the logic so that I can also create workflows like this in future
    image

Thanks for taking the time to understand and create the workflow.

At the moment it combining everything in 1 file with different sheets but I need to create 1 file per folder(meaning combining only files of 1 folder and then combining the files of the other folder).

4

For preserving formatting and formulas I think that this is not possible (but as I am new to KNIMe as you I am not sure)

Excel Reader(XLS) is the updated version with more options.



For reference about nodes you can read the Node description or consult https://hub.knime.com/ where you can learn a lot about nodes and sample workflows.

Red lines are used to “transport” the variables between nodes (so I understand them) in this case the variable is the filename. In the Excel reader node the variable is used to read specific filename in the Excel sheet appender is used to name the sheet.
The red line from loop start to loop end can be deleted.
The connections from Excel reader to Excell sheet appender is used to send the data for writing. The connection to Loop end is to run the loop.

Maybe there is a more elegant way to do this but I do not know for it.

If you want one file per folder you can put this loop inside another loop (extract folder name and then use group loop node for example)

Kind regards
Andrej

2 Likes
5

Hi @Ankit_smart,

i think it is possible with the Continental Nodes
https://www.knime.com/community/continental-nodes-for-knime-xls-formatter

There you have layouting and formatting options (however never used them myself - so cannot help in detail)

Another solution to keep the formatting is using the POI Library in combination with a java snippet, however you would need to a bit of code for that:
https://poi.apache.org/

We use the POI Library to just write to a template excel instead of overwriting the sheet.
So you can copy a knime table to an existing excel file and change the formatting there
(would be nice if they could add that feature to the excel writer)

5 Likes
6

Hello there!

couple of notes:

  • red line do “transport” flow variables but also control the execution order. Additionally flow variables are carried to all connected downstream nodes so it is not necessary to use 3 red lines but rather one will be sufficient :slight_smile:
  • currently there is no option to preserve Excel formatting while writing data to Excel using KNIME nodes. Continental Extension doesn’t provide this option either to my understanding but using it you can define your format and if always same create once and use as many times as needed
  • if no workbook present Excel Sheet Appender (XLS) will create one so no need to use both Writer and Appender nodes :wink:

Br,
Ivan

3 Likes
7

hmmm I thought that would be possible with the continental nodes :frowning:
Maybe here a small working example how to write into an Excel with POI (without changing the existing layout)

With small tweaks you could write to any row (e.g. change the starting row and column positions with flow variables)
Most likely the code could be done nicer :see_no_evil: :hear_no_evil: :speak_no_evil:

2 Likes
8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.