# Select files within a date range and merge them into one file
**URL:** https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701
**Category:** General
**Created:** [11 March 2022 10:47 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701 "2022-03-11T10:47:07Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@Gitsaibot](https://discourse.nodered.org/u/Gitsaibot)
#### Post date: [11 March 2022 10:47 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/1 "2022-03-11T10:47:07Z")
Hello,
I have a directory that contains a csv file for each day. I now want to select all files within a month e.g. May and merge them into one big csv file. The csv files all have the same structure.
I have already experimented a bit with red-contrib-fs-ops and can view all files in the directory and read their stats. Is there a node with parameterizable sorting functions or is this only possible via a function node with it's own code?
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [11 March 2022 11:08 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/2 "2022-03-11T11:08:02Z")
What operating system are the files under? And does each file have a column headings line?
---
### Author:  [@Gitsaibot](https://discourse.nodered.org/u/Gitsaibot)
#### Post date: [11 March 2022 12:17 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/3 "2022-03-11T12:17:34Z")
Linux and yes, they have a header line. But this line is the same in all files.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [11 March 2022 15:00 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/4 "2022-03-11T15:00:17Z")
Personally I'd do it with a shell script called from a Node-red exec node.
```auto
#! /bin/bash
INPUTDIR=/home/pi/data
OUTPUTFILE=/home/pi/data/result.csv
if test -e $OUTPUTFILE
then
echo $OUTPUTFILE already exists - exiting
exit 1
fi
if ["$INPUTDIR" == $(dirname "$OUTPUTFILE")]
then
echo warning creating output file in same directory. Please move it!
fi
CSVFILES=$(ls $INPUTDIR/*.csv) #Maybe need to sort list of files?
HEADERLINE=$(head -1 $(ls $CSVFILES | head -1))
echo $HEADERLINE > $OUTPUTFILE
for file in $CSVFILES
do
echo processing $file
grep -v $HEADERLINE $file >> $OUTPUTFILE
done
```
---
### Author:  [@Gitsaibot](https://discourse.nodered.org/u/Gitsaibot)
#### Post date: [11 March 2022 17:54 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/5 "2022-03-11T17:54:41Z")
I'm not good at shell programming and don't know how to add the date range selection. So i tried it with this function node but I get in the debug node always msg.combined -\> **undefined**
```auto
var combined = [];
for (var i = 0; i < msg.files.length; i++) {
var file = msg.files[i];
var stat = msg.stats[i];
var D1 = "01/03/2022";
var D2 = "31/03/2022";
var D3 = "15/03/2022";
D1 = new Date(D1);
D2 = new Date(D2);
D3 = new Date(D3);
if (D3.getTime() <= D2.getTime()
&& D3.getTime() >= D1.getTime()) {
combined.push({
path: msg.payload,
file,
...stat
})
}
}
msg.combined = combined;
return msg;
```
What am i doing wrong ? The node works without the date range check. So it must have something to do with it.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [11 March 2022 19:57 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/6 "2022-03-11T19:57:57Z")
I'm not good with javascript but your use of date strings looks iffy.
I think that if you want to pass a short date format to Date() it has to be in YYYY/MM/DD format.
`Date("31/03/2022")` is invalid.
That being said, I could only get `msg.combined == []` never `undefined`
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [11 March 2022 20:03 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/7 "2022-03-11T20:03:12Z")
If you can pass start and end dates as parameters in "YYYYMMDDhhmm" format, I think this variation of the script should work. I have not tested it.
```auto
#! /bin/bash
INPUTDIR=/home/pi/data
OUTPUTFILE=/home/pi/data/result.csv
FROMDATE=$1 # Should be YYYYMMDDhhmm eg 202212310000
TODATE=$2
if test -e $OUTPUTFILE
then
echo $OUTPUTFILE already exists - exiting
exit 1
fi
if ["$INPUTDIR" == $(dirname "$OUTPUTFILE")]
then
echo warning creating output file in same directory
fi
touch -t $FROMDATE /tmp/start_date
touch -t $TODATE /tmp/end_date
CSVFILES=$(find $INPUTDIR -type f \( -newer /tmp/start_date -a ! -newer /tmp/end_date \))
# ls -l /tmp/start_date /tmp/end_date
# echo $CSVFILES
# exit
HEADERLINE=$(head -1 $(ls $CSVFILES | head -1))
echo $HEADERLINE > $OUTPUTFILE
for file in $CSVFILES
do
echo $file
grep -v $HEADERLINE $file >> $OUTPUTFILE
done
```
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [11 March 2022 20:13 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/8 "2022-03-11T20:13:17Z")
Here is another way to do it ๐
Using just a function node.
You can use fs instead of `fs-extra` if you like but then you will end up with a more complex first line of the for block. `fx-extra` is used by Node-RED by the way. I'm using `fast-glob` here instead of using the fs-ops node.
```javascript
// Use Fast-Glob to get the list of files
const entries = await fg(['*/csv/*.csv'])
msg.entries = entries
// get the files
msg.files = []
let fline
for (const fname of entries) {
let f = await fs.readFile(fname, 'utf8')
f = f.split('\n')
fline = f.shift() // remove the first line
f = f.join('\n')
msg.files.push( f )
}
// Add the first line back and join all the headerless files together
msg.payload = `${fline}\n${msg.files.join('')}`
return msg
```
It uses a number of interesting modern JavaScript constructs so may be a useful learning exercise as well.
Obviously, this approach as with the others given has a problem. You can run out of memory. You can improve things by not keeping the individual files on the msg as well as the final output but you can still easily run out of memory if handling very large files.
So for large file handling, it is better to use a streaming approach whereby you stream in the files and stream them straight out to the output file.
The other limitation of this approach is that it only works if the columns are exactly the same for each input file.
Incidentally, both Python (via libraries) and PowerShell (no need for libraries) are better at CSV handling than JavaScript is.
Oh, and of course, you should look on [npmjs.org](http://npmjs.org) for libraries that will do CSV merge directly - there are plenty.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [11 March 2022 20:22 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/9 "2022-03-11T20:22:50Z")
Does that solution include the header line from each file? Is that a problem?
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [11 March 2022 20:28 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/10 "2022-03-11T20:28:19Z")
Nope ๐
It only adds the header again at the end, it removes it in the loop.
I "cheated" by letting each loop grab the header line. Though of course it doesn't need to. But to only grab it once would need more code and is more bother than it is worth. Maybe if you were dealing with thousands of files it might be worth doing some optimisation. But honestly, I'd use a library to do that and would certainly choose a streaming method.
I added a couple of comments to hopefully make things clearer. It was a fun brain exercise to do at the end of a long week.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [11 March 2022 20:41 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/11 "2022-03-11T20:41:32Z")
Ha! If I was a js native I could have answered my own question without the comments ๐
Fwiw I don't think my grep solution runs the risk of memory problems either. But I don't think the OP should use it if he is not a bash native.
---
### Author:  [@Gitsaibot](https://discourse.nodered.org/u/Gitsaibot)
#### Post date: [12 March 2022 10:02 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/12 "2022-03-12T10:02:50Z")
@TotallyInformation First of all, thanks for the help. In comparison my js knowledge is rudimรคntรคr ๐ When i try your suggested solution i always get undefined errors. Therefore a few questions ๐
Is it correct that the path to the csv file must be specified? Like:
`const entries = await fg(['/home/pi/csv/*.csv'])`
The date range selection part is not included in your solution or? My csv file looks like this:
I think the first line must be irgnored or?
```auto
sep=;
Datum/Uhrzeit;Verbrauchswert;Einheit;Verbrauch in Euro;Einheit;CO2-Ausstoss;Einheit;;Ansicht:;Datum;;24 Stunden;01.03.2022 23:52 Uhr
00:00;0,000;Wh;0,00;Euro;0,000;kg CO2
00:15;0,000;Wh;0,00;Euro;0,000;kg CO2
00:30;0,000;Wh;0,00;Euro;0,000;kg CO2
00:45;0,000;Wh;0,00;Euro;0,000;kg CO2
01:00;0,000;Wh;0,00;Euro;0,000;kg CO2
```
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [12 March 2022 11:34 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/13 "2022-03-12T11:34:55Z")
> [@Gitsaibot](#):
>
> Is it correct that the path to the csv file must be specified? Like:
Yes, that looks fine. That should select all of the CSV files in the CSV sub-folder in `/home/pi/` which, if you are logged in as the `pi` user will be your home folder and accessible as `cd ~/` from the command line.
You can always add a line to the function `node.warn(entries)` after that line and it will dump the list of entries to the debug panel.
> [@Gitsaibot](#):
>
> The date range selection part is not included in your solution
No, I didn't do that as I only had a few minutes spare.
However, because I used fast-glob to get the listing, it becomes fairly straight-forwards to change the call to `fg` to be an array of potential names instead of a single wildcard. But you might not even need that. If you want to get the files for the month of May this year and if your filenames use a sensible format such as `YYYYMMDD-something.csv`, you can build the glob pattern:
```auto
const month = (new Date()).toISOString().slice(0,7).replace('-','')
const entries = await fg([`/home/pi/csv/${month}*.csv`])
```
Note that I changed the simple single quotes to back-ticks so that I could embed the variable which will be done before the function gets called.
If you wanted more than 1 month, you could have a second entry in the fg array parameter.
I note that in your original code, your dates had `/` in them and this will not work for file names as the `/` and `\` characters are reserved as folder delineators.
> [@Gitsaibot](#):
>
> I think the first line must be irgnored or?
Yes, you will need to drop the first line for each file. Thats what the shift function will do for you so just add another one in the loop but don't bother to assign it to a variable as you don't need it.
---
### Author:  [@Gitsaibot](https://discourse.nodered.org/u/Gitsaibot)
#### Post date: [12 March 2022 12:58 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/14 "2022-03-12T12:58:39Z")
```auto
const month = (new Date()).toISOString().slice(0,7).replace('-','')
const entries = await fg([`/home/pi/csv/${month}*.csv`])
```
Stupid question. Where to set the month I want to select?
> Yes, you will need to drop the first line for each file
Now I get a long string output ๐ But the rows of the individual files were not added but appended one after the other. My goal is that the values of the columns add up and the rows stay the same. Merging was probably the wrong expression for it.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [12 March 2022 13:29 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/15 "2022-03-12T13:29:22Z")
> [@Gitsaibot](#):
>
> I now want to select all files within a month e.g. May and merge them into **one big csv file**.
> [@Gitsaibot](#):
>
> But the rows of the individual files were not added but appended one after the other. My goal is that the **values of the columns add up** and the **rows stay the same**. Merging was probably the wrong expression for it.
I can't tell what you are trying to achieve.
Maybe show us some sample data, a couple of files, each having just a few lines, and a file to show what the result should be?
---
### Author:  [@Gitsaibot](https://discourse.nodered.org/u/Gitsaibot)
#### Post date: [12 March 2022 15:31 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/16 "2022-03-12T15:31:51Z")
Yes, I have expressed myself a little misleadingly. Am not so good at English ๐
The picture should make it more understandable I hope.
The values in the second column are to be added. The other columns should remain the same. The number of rows should not change.
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [12 March 2022 15:43 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/17 "2022-03-12T15:43:58Z")
> [@Gitsaibot](#):
>
> Where to set the month I want to select
Do you want that to be a fixed month - that means you need to change and redeploy the flow to change it. Or do you want to send a message that changes the month to use?
You've also not actually said what format your filenames are in.
> [@Gitsaibot](#):
>
> The values in the second column are to be added
Well that puts a completely different aspect on the whole thing and you didn't mention that before I don't think?
We've been working on the assumption that by the word "merge", you meant that each file represented a different date and therefore you wanted them appended to each other.
To do a calculation on one of the columns, you have to break each line of each file into its component cells. It also requires you to specify one of the columns as a KEY.
Now you really need to tell us what you are trying to achieve because things have just taken 2 orders of magnitude more complex.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [12 March 2022 16:04 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/18 "2022-03-12T16:04:16Z")
It is starting to look like that data belongs in a relational database rather than csv files.
How do you acquire the data - does it arrive as a spreadsheet?
---
### Author:  [@Gitsaibot](https://discourse.nodered.org/u/Gitsaibot)
#### Post date: [12 March 2022 16:26 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/19 "2022-03-12T16:26:32Z")
> Do you want that to be a fixed month - that means you need to change and redeploy the flow to change it. Or do you want to send a message that changes the month to use?
A message/button to change the month would be perfect.
> You've also not actually said what format your filenames are in.
They look like this: 20220301\_235257\_id00016\_24h.csv
> Well that puts a completely different aspect on the whole thing and you didn't mention that before I don't think?
No, as I said, I unfortunately expressed myself incorrectly.
> Now you really need to tell us what you are trying to achieve because things have just taken 2 orders of magnitude more complex.
I get every day a csv file which shows me the consumption (column 2) in 15 minute steps over the day. At the moment I read the latest file and create a bar chart. But now I would like to show the consumption over a month(Maybe selectable via the dashboard) in 15 minute steps.
I thought I can read the csv files, add the values for column 2 and then pass the payload to my existing nodes for the bar chart display.
Is that more understandable now? Or is something like this not possible with node red?
---
### Author:  [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)
#### Post date: [12 March 2022 17:14 UTC](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701/20 "2022-03-12T17:14:44Z")
It is, though I don't know as I'd recommend trying to do it with just Node-RED since it is quite possible you will want to do more processing later on.
As JBudd say's, you should be looking at a database ideally. With Node-RED helping you get the data and push it to the DB. A timeseries DB such as InfluxDB would probably be a good choice. Or a DB that allows multi-dimensional "cube" type processing.
If you really don't want a db. I'd recommend reading each file using the file-in node and converting the data to JSON using the CSV node. Then as you read each file and after conversion, you can add the new data to a global or flow variable.
To be able to select the month, You will be able to do that using Dashboard with an input - you will save the month to another variable for use when you process the file(s).
You probably also need some method of marking each file as processed. Perhaps move them to another folder once you've processed them.
Frankly though, in my opinion (and doubtless others may disagree), you've now stepped beyond what Node-RED can currently do comfortably.
If I were to do this, I would use Microsoft Excel with PowerQuery. If you don't have a license for an up-to-date version of Excel, I'd use a Python Jupyter notebook using datatables with one or more of the common tabular processing libraries such as Pandas and NumPy.
[Next page](https://discourse.nodered.org/t/select-files-within-a-date-range-and-merge-them-into-one-file/59701.md?page=2)