Splitting CSV into multiple CSVs by unique combination of two columns

Hi, I’m new to KNIME and was hoping to build this with some information online but I am need of a little help!

I’d like to separate a CSV file into multiple CSVs based on a the combination of perils and regions.

For example, I have columns Loss, Peril, Region and I’d like to split the CSV into 4 files (US windstorm, EU windstorm, US earthquake and US fire).
image

My current workflow looks like this:
image

My idea was to do the following but haven’t correctly created it in practice:

  1. Read in CSV
  2. Group by Region, Peril
  3. Group Loop Start (filtered by Region and Peril)
  4. CSV writer - was hoping to dynamically create file names and export
  5. End Loop

Hi @charlotteknime , welcome to the KNIME community,

You’re on the right lines. I wasn’t sure though what the GroupBy node was specifically doing unless you are performing an aggregation of rows (e.g. sum etc). It doesn’t need to be there for the Group Loop, so in my example here I’ve left it out. Add it back if you need it for some aggregations.

If I’m writing files to a specific output location, I tend to create a String variable containing the output folder, and use this when creating the individual file names. That way if I have a number of different file types being output, I can easily change the folder name in future without too much effort.

Dynamic CSV Filename creation.knwf (23.9 KB)

So as you can see, it does dynamically create a file name in String Manipulation (variable):

join($${Sbase_folder}$$,"/",$${SRegion}$$,"-",$${SPeril}$$,".csv")

where I’ve joined together a the base folder variable, the region and peril from the grouping and “.csv” file suffix. You’d adjust this to whatever file name output you are going to want.

I always use “/” as the folder separator as in KNIME, this works with all operating systems and using "" requires additional “escaping” effort in String Manipulation, documented elswehere, so just use “/” :wink:

This string variable is converted to a Path variable so it can be used by CSV Writer:

Finally, to complete the loop, as you discovered you cannot use the standard loop end, which requires table data, so just use a Variable Loop End instead.

1 Like