how to split csv by month

KNIME Analytics Platform
1

We'd like to split a csv files(day by day) into several ones per month, any advice would be appreciated, please also take a look at the attached ...

2

see if this helps

For my example you need to create a c:\temp  directory or adjut the filenames to suit you.

The ideia is, read your file, create a string with year and month, create a filename with that, start a grouploop by filemane, save a csv in each iteration.

Jorge Canelhas

 

 

3

Hi, sure.

Node 1 - you import the node

Node 2 - join(substr($Date$,0 , 4),substr($Date$,5 , 2)) takes the year and month parts of the date string and creates a column named yearmonth

Node 6 - join("c:\\temp\\",$yearmonth$,".csv") Creates a new foeld file name composed of the path you ant and the yearmonth part, note that this part could be done at once, but this way is a bit more readable

Node 3 - group loop, you choose the filename as grouping and also pass it as a variable to node 5.

Node 4 - takes out the yearmonth and finename columns, we dont want them on the excel file.

Node 5 - in the config, you have a switch on the side of the filename, use the variable tyou passed before, see the red line connecting them (right click, show variable port)

Node 4- Simple closes the loop

 

now, every run, it will create a group for each different filename, and save only the related data.

4

yes, it works, could you explain each configure setting once you get a chance, Thanks

.jOe

5

@JCanelhas, Thanks for your tips, it did save me lots of time...  

 

6

201601.csv(data from 2016/1/1 to 2016/1/31)

201602.csv(data from 2016/2/1 to 2016/2/28)

...

201705.csv(data from 2017/5/1 to 2017/5/31)

Thanks