Split data into separate Excel with one column, then to separate sheet with another column?

[billknguyen]

Hi, I want to split my data table into many Excel with multiple sheets in it based on two different column. For example, If I have something as follow:

I want to split the data first by Vendor into separate Excel, so the Excel name would be the Vendor name (1, 2, 3, …). Then for each Excel, each tab would be based on each Country group. So for Excel sheet “1”, there is a tab for Canada and a tab for USA.

I’m not sure how it can be done, and stuck at Group Loop Start. Please advice how I can make this possible. My data is around 120,000 rows with over 500 vendors so it might be a lot.

[elsamuel]

Can you upload a portion of the data for us to work with?

I’d recommend taking a look at the Solutions to Just Knime It Challenge 2 for some pointers. The challenge was to split a CSV file into several different CSV files and store them in different folders.

The thread is here. My solution is here

Your problem is similar. You’d need to use flow variables to control tab creation.

[billknguyen]

Hi @elsamuel. Thanks for linking me up to the Challenge.

What I’m trying to do is very similar to the challenge, but instead splitting them into Excel, then to Sheet in Excel. I’m not sure how this can be done. Could you elaborate on the flow variables to control tab creation?

If you are looking of the data you can just use the data for the challenge, and split Year into Excel, months into Excel sheet.

[elsamuel]

Try this @billknguyen:

elsamuel/Public – Excel Export with variable control

Pay key attention to the Excel Writer settings and the way the loops are nested.

[billknguyen]

This is exactly what I needed, thank you so much! Just for learning purpose since I am very new, how did which part in the Excel Writer indicated what the Excel name would be? I see that for Sheet it is through the Flow Variable part. And also what does Create File/Folder Variable do?

[elsamuel]

The Create File/Folder Variables node creates a path variable using the Year variable generated by the first GroupBy node. In this workflow, this new path variable is called base_folder and contains the filepath, filename, and extension.

In the Excel Writer node, you can specify that you want to use this base_folder variable for the path by clicking on the v icon next to the Browse button and choosing the appropriate variable:

[mlauber71]

@elsamuel

Create File/Folder Variables

@billknguyen tried recently to illustrate how this works with a screenshot

Write multiple CSV file to a loop

@VJAM the creation of Flow Variables for Paths is maybe not 100% straightforward. Maybe this example might help to sort it out. The “path_values” is maybe not perfectly named. I think this is constructed that you could pass several Folder variables as collection strings. Also: CHunk Loop Start might have to be set to 1. Often I prefer the Table Row to Variable Loop Start node. [image] [image]

[billknguyen]

Thank you guys for the explanation!

Just another question, when splitting the Excel, I’m trying to keep the formatting of the input Excel (ex: highlighting color of certain cells, etc.). Seem like it’s not currently doing that right now. Do you know if there are any solution to this?

[mlauber71]

@billknguyen it might be best to open a new thread for this question. But there has been a thread about excel and keeping formats here. Maybe you check the suggestions there.

Writer Node Issues

@helpmeplease if you want the structure of your Excel file unchanged and just change values there are these options, if you must change data on a pre-formatted sheet (otherwise you might just replace this sheet). The Excel Cell Updater can change values in specific cells. You can use a community node to write to an Excel file without changing the structure or formats: You can the use Python and OpenPyxl to do more complex editings of excel files. You will have to plan ahead what this d…

With the Excel Cell Updater – KNIME Community Hub there is a dedicated node for that.