Merge multiple csv created last week

Question

This script provided by raccoozie works as described:

@echo off
setlocal
set first=1
set fileName="combinedFiles.csv"
>%fileName% (
  for %%F in (*.csv) do (
    if not "%%F"==%fileName% (
      if defined first (
        type "%%F"
        set "first="
      ) else more +1 "%%F"
    )
  )
)

Now I want to add a criteria to only select the files created from the last 7 days.

The problem is that the program doesn't create 1 file daily, but could create 7, 5, 9 or 0 files at certain days.

How to start?

SachaDee · Answer 1 · 2016-02-14T18:43:31.577

1

To merge the 7 last created .csv :

@echo off
setlocal enabledelayedexpansion
set /a $c=1
set "fileName=combinedFiles.csv"

>"%filename%" (for /f "delims=" %%F in ('dir/b/a-d/od *.csv') do (
  if not "%%F"=="%fileName%" if !$c! LEQ 7 (
  type "%%F"
  set /a $c+=1)
  ))

EDIT : To merge all .csv files created the last 7 days and sorted by date

@Echo off
::Get current Date -7 days with VBS
echo wscript.echo DateAdd("d", -7, date(^)^) >GetDate.vbs
for /f "delims=" %%a in ('cscript //nologo GetDate.vbs') do set "$Limit=%%a"
for /f "tokens=1-3 delims=/" %%a in ('echo %$Limit%') do set /a $Limit=%%c%%b%%a
Del GetDate.vbs 2>nul

setlocal enabledelayedexpansion

set "fileName=combinedFiles.csv"

(for /f "delims=" %%a in ('dir/b/a-d/o-d "*.csv"') do (
     for /f "tokens=1-3* delims=/ " %%b in ('dir /TC "%%a" ^| find "/"') do set "$DateTest=%%d%%c%%b"
     if not "%%F"=="%fileName%" if !$DateTest! GEQ %$Limit% type "%%a"
)
)>%fileName%"

The date is transformed 11/02/2016 come 20160211 to have logic testing value (A later date will always be greater). So we can test all sorting (by dir /o-d) files for a value greater as $Limit.

Now we have the files sorted by date and not older than 7 days.

edited Feb 14 '16 at 18:43

answered Feb 11 '16 at 18:09

SachaDee

9,245
3
23
33

didn't work, it deleted my existing csv file, but didn't create a new one. If I look at the code it looks like you go to a specific directory. Please assume I run the `.bat` file within the directory where the files reside. – davejal Feb 11 '16 at 22:25
Sorry i forgot a closing bracket and two double quote test-it again – SachaDee Feb 11 '16 at 22:29
oke, now it works, takes 7 files, but takes the first created (by date) I need the last created by date. If possible it could be more then 7 files but take the files created in the last 7 days. already +1 for solution – davejal Feb 11 '16 at 22:40
Try with `dir/b/a-d/o-d` instead of `dir/b/a-d/od` to reverse the order – SachaDee Feb 11 '16 at 22:46
Check my Edit. Using forfiles to merge all csv files created the last 7 days – SachaDee Feb 11 '16 at 22:58
the edited version isn't sorted by date desc, where can I change that? – davejal Feb 11 '16 at 23:11
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/103243/discussion-between-davejal-and-sachadee). – davejal Feb 11 '16 at 23:13
I tried it and it seems to work, but because the data is to big I tried to lower the 7 to 3, but it keeps processing the same amount of files. – davejal Feb 12 '16 at 01:55
I think I found the error but can't fix it. I made the bat echo the process and I noticed I searching for 2016102, where it should be searching for 20160210. – davejal Feb 12 '16 at 02:18
The date are inverted ? – SachaDee Feb 14 '16 at 18:12

Magoo · Answer 2 · 2016-02-12T12:43:54.083

SET "filename1=alreadydone.txt"
IF NOT EXIST "%filename1%" ECHO x:>>"%filename1%"
FOR /f "delims=" %%a IN (
 'dir /b /a-d /o:d "*.csv" ^|findstr /x /v /g:"%filename1%'
 ) DO (
 rem do your combine-file [%%a] here
 ECHO %%a>>"%filename1%"
)

This part-routine (generating %%a instead of %%F because that's what my template does) takes the directory list and excludes files in filename1. The remaining files are delivered to the combine-file section of the code you've already produced, and the filename is recorded in filename1.

The if not exist line generates a dummy entry in filename1 so that findstr does not complain the file is empty.

With this scheme, once any file has been processed, its name is saved in filename1 and it won't be re-processed. Only new files will be combined using your combination code.

Result: No anchor about "7" or any other number of days. If you run it 7 days apart, those 7 days' processing will be combined and recorded. If you do it 5 days apart in anticipation of a public holiday, it will combine those 5 days. If you go on vacation for 2 weeks, then since no-one will follow your instruction to run it, it will combine the last 14 days. Or you could run it every day...

So here's the combined batch

@echo off
setlocal
set first=1
set fileName="combinedFiles.csv"
SET "filename1=alreadydone.txt"
IF NOT EXIST "%filename1%" ECHO x:>>"%filename1%"
>%fileName% (
for /f %%F in ('dir /b /a-d /o:d "*.csv" ^|findstr /x /v /g:"%filename1%"'
) do (
 ECHO %%F>>"%filename1%"
    if not "%%F"==%fileName% (
      if defined first (
        type "%%F"
        set "first="
      ) else more +1 "%%F"
    )
  )
)

[Edit:] removed sourcedir in for/f because no directory-specification is required (files are in current directory)

[Further edit:] re-add /f in for and add missing closing-rabbit's ears.

I like the explanation, but this doesn't create the merged file — davejal, Feb 12 '16 at 11:20
I got the following error `The system cannot find the file specified.` If I uncomment the echo and run it in prompt — davejal, Feb 12 '16 at 12:14
in the already done file I get the following x: 'dir /b /a-d /o:d |findstr /x /v /g:"alreadydone.txt' 'dir /b /a-d /o:d |findstr /x /v /g:"alreadydone.txt' 'dir /b /a-d /o:d |findstr /x /v /g:"alreadydone.txt' — davejal, Feb 12 '16 at 12:22

Merge multiple csv created last week

2 Answers2