Windows batch: Copy csv files into a specific folder and list author of csv files

I'd recommend to move from batch to PowerShell. Batch had its time, but it can't even remotely compete with PS (neither can VB Script), and there is more and more that can only be done in PS, so you might just as well get used to it.
This should do all you want; it will, just for the fun of it, optionally even restore the file's original owner (see lines 15-18), which will normally be replaced by the user copying the file.

$logFolder = 'C:\Log'
$inputFolder = 'C:\Temp'
$authorFile = "$($logFolder)\Owners_{0}.csv"	## {0} will be replaced by the monthly folder name

$yyyyMM = Get-Date -Format 'yyyy-MM'
$monthFolder = "$($logFolder)\$($yyyyMM.Split('-')[0])\$($yyyyMM)"
If (-not (Test-Path -Path $monthFolder)) {
	New-Item -Path $monthFolder -ItemType Directory | Out-Null
}
If ($csvFiles = Get-ChildItem -Path $inputFolder -Filter *.csv) {
	$csvFiles | ForEach-Object {
		Write-Host "Processing $($_.Name) ..."
		$owner = $_.GetAccessControl().Owner
		$copyItem = Copy-Item -Path $_.FullName -Destination $monthFolder -PassThru
		## Uncomment the following 3 lines to restore the owner of the original file
		# $acl = $copyItem.GetAccessControl()
		# $acl.SetOwner([System.Security.Principal.NTAccount]$owner)
		# $copyItem.SetAccessControl($acl)
		$copyItem | Select-Object -Property FullName, Name, @{n='Owner'; e={$owner}} | Write-Output
	} | Export-Csv -NoTypeInformation -Append -Path ($authorFile -f $yyyyMM)
} Else {
	Exit
}
## Java ...

Select allOpen in new window

Feel free to ask if something is not clear.

Thank you very much for this proposal.
The idea is to run this task with windows task scheduler. With powershell how should I proceed. Save the procedure as ps1 and then?
Java line used in .bat file is the following and finish with a del in folder in which are located csv file. How should I proceed to add this line in powershell:

set JAVA_ARGS=-showversion -Dlog4j.overwrite=true -Xmx4096m -Duse_description=true


CD C:\API\mstt-timesheet-import\bin

java %JAVA_ARGS% -jar "C:\API\mstt-timesheet-import\lib\mstt-timesheet-import.jar" "D:\MSTT_TIMESHEETS" "../conf/Project-IDs-List.txt"


DEL /Q "D:\MSTT_TIMESHEETS\*.*"

Select allOpen in new window

Thank you.

Use with Task Scheduler:

Program/Script:
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Arguments:
-File "C:\ScriptFolder\Whatever.ps1"

Select allOpen in new window

You can usually start executables mostly like you do in batch:
whatever.exe -Some arguments
or if the exe is in a location that requires quotes:
& "C:\Program Files\Acme\whatever.exe" -Some arguments
PowerShell parses the command line a differently than batch, though, and the java arguments tend to be sort of complex, so this uses Start-Process to start java.exe, because this allows you to generate an exact string command line where the parser doesn't interfere.
The file deletion at the end is in test mode; it will only show what it would delete, but not actually delete anything; remove the -WhatIf at the end of the line to run it for real.
Obviously can't test this, but it should work:

$logFolder = 'C:\Log'
$inputFolder = 'C:\Temp'
$authorFile = "$($logFolder)\Owners_{0}.csv"	## {0} will be replaced by the monthly folder name

$yyyyMM = Get-Date -Format 'yyyy-MM'
$monthFolder = "$($logFolder)\$($yyyyMM.Split('-')[0])\$($yyyyMM)"
If (-not (Test-Path -Path $monthFolder)) {
	New-Item -Path $monthFolder -ItemType Directory | Out-Null
}
If ($csvFiles = Get-ChildItem -Path $inputFolder -Filter *.csv) {
	$csvFiles | ForEach-Object {
		Write-Host "Processing $($_.Name) ..."
		$owner = $_.GetAccessControl().Owner
		$copyItem = Copy-Item -Path $_.FullName -Destination $monthFolder -PassThru
		## Uncomment the following 3 lines to restore the owner of the original file
		# $acl = $copyItem.GetAccessControl()
		# $acl.SetOwner([System.Security.Principal.NTAccount]$owner)
		# $copyItem.SetAccessControl($acl)
		$copyItem | Select-Object -Property FullName, Name, @{n='Owner'; e={$owner}} | Write-Output
	} | Export-Csv -NoTypeInformation -Append -Path ($authorFile -f $yyyyMM)
} Else {
	Exit
}

$javaArgs = '-showversion -Dlog4j.overwrite=true -Xmx4096m -Duse_description=true'
$javaArgs += ' -jar "C:\API\mstt-timesheet-import\lib\mstt-timesheet-import.jar" "D:\MSTT_TIMESHEETS" "../conf/Project-IDs-List.txt"'
Push-Location -Path 'C:\API\mstt-timesheet-import\bin'
Start-Process -FilePath java.exe -ArgumentList $javaArgs -Wait -NoNewWindow
Remove-Item 'D:\MSTT_TIMESHEETS\*.*' -WhatIf
Pop-Location

Select allOpen in new window

-ExecutionPolicy Bypass

Select allOpen in new window

will likely need to be added as a cmd line argument.

while task scheduler allows cmd line arguments, it has a history of handling them poorly (although a couple simple ones are probably fine)
 
even still, I recommend putting the call to powershell, with all arguments and script to call into a .cmd file, and testing with that.

Once that works, set the cmd file as your task to be called by task scheduler, and in the future if you need to test the script and see the output, you can run the cmd script that calls powershell directly.

also if you need to change what script it calls or the arguments used, you can edit the cmd script, and test directly again, instead of the task, which would require you entering the password for the user to save and testing by running the task would not show output, so you would only have the log available, or have to copy and paste cmds and argument to a cmd prompt to see the results.

ie you wouldn't catch why the script didn't run in the task if you directly call it from the task, if say the issue is in powershell launching.

Assuming that code snippet you pasted in the question was getting the info you needed, then this feels like it would add the items you requested.

I wasn't sure if you wanted to generate the LogFolder path from the current system date, or if it was hard coded.  Can adjust if needed...

@echo off
setlocal EnableDelayedExpansion

rem Define file and folder locations
set BaseDir=%Cd%
set InputFolder=%BaseDir%\CsvFiles
set LogFolder=%BaseDir%\2019\2019-05
set OutFile=%LogFolder%\owner.csv

rem Make sure needed folders and files exist
if not exist "%LogFolder%" md "%LogFolder%"
if not exist "%InputFolder%" exit /b
if not exist "%InputFolder%\*.csv" exit /b

rem Generate output report
(
    rem Header line for column names
    echo Name,Author

    rem Outout file and author for each CSV file
    for /f "tokens=*" %%A in ('dir /q "%InputFolder%\*.csv" ^| findstr /b /r /c:"[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]"') do (
        set Line=%%~A
        echo.!Line:~62!,!Line:~39,23!
    )
)>>"%OutFile%"

rem Run Java
set JAVA_ARGS=-showversion -Dlog4j.overwrite=true -Xmx4096m -Duse_description=true
cs C:\API\mstt-timesheet-import\bin
java %JAVA_ARGS% -jar "C:\API\mstt-timesheet-import\lib\mstt-timesheet-import.jar" "D:\MSTT_TIMESHEETS" "../conf/Project-IDs-List.txt"
del /Q "D:\MSTT_TIMESHEETS\*.*"

Select allOpen in new window

---

»bp

thought about giving him the full CMD script but decided not to since oDBA's PS1 script should do most of it, but I'm glad someone did give a normal CMD script solution after all, thanks Bill :)

@Bill: Possible to manage this automatically instead of hard code:

%BaseDir%\2019\2019-05

Select allOpen in new window

Or at least the monthly folder.
Thank you very much Bill.

Okay, this should handle that.

@echo off
setlocal EnableDelayedExpansion

REM Get current date/time in YYYY-MM format
set LocalDateTime=
for /f "tokens=* skip=1" %%A in ('wmic os get LocalDateTime') do (
    if not defined LocalDateTime (
        set LocalDateTime=%%A
        set Year=!LocalDateTime:~0,4%!
        set YearMonth=!LocalDateTime:~0,4%-%LocalDateTime:~4,2!
    )
)

rem Define file and folder locations
set BaseDir=%Cd%
set InputFolder=%BaseDir%\CsvFiles
set LogFolder=%BaseDir%\%Year%\%YearMonth%
set OutFile=%LogFolder%\owner.csv

rem Make sure needed folders and files exist
if not exist "%LogFolder%" md "%LogFolder%"
if not exist "%InputFolder%" exit /b
if not exist "%InputFolder%\*.csv" exit /b

rem Generate output report
(
    rem Header line for column names
    echo Name,Author

    rem Outout file and author for each CSV file
    for /f "tokens=*" %%A in ('dir /q "%InputFolder%\*.csv" ^| findstr /b /r /c:"[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]"') do (
        set Line=%%~A
        echo.!Line:~62!,!Line:~39,23!
    )
)>>"%OutFile%"

rem Run Java
set JAVA_ARGS=-showversion -Dlog4j.overwrite=true -Xmx4096m -Duse_description=true
cs C:\API\mstt-timesheet-import\bin
java %JAVA_ARGS% -jar "C:\API\mstt-timesheet-import\lib\mstt-timesheet-import.jar" "D:\MSTT_TIMESHEETS" "../conf/Project-IDs-List.txt"
del /Q "D:\MSTT_TIMESHEETS\*.*"

Select allOpen in new window

---

»bp

Thank you very much Bill.
I will test it this weekend.
Two questions:
1-If I run this through windows task should I comment Echo lines?
2-If CSV files don't exist Java API is not launched? This is the expected requirement.
Thank you.

1-If I run this through windows task should I comment Echo lines?

No.  The only ECHO commands in the file are to write the output to the new CSV file, those are needed.

2-If CSV files don't exist Java API is not launched? This is the expected requirement.

Correct.

---

»bp

Thank you Bill,
I made some adjustments as I was unable to properly get datestamp folder. I adjust accordingly and I will manage with YYYYMMDD_hhmmdd stamp.
What I was not able to adjust is the author result. I tried to recover the previous version but I got an strange result, could you please try me to adjust?:
C:\API\mstt-timesheet-import\bin>(
set Line=26/08/2015  16:35             1ÿ283 EUR\ID455666         ID555555.csv  
 echo.!Line:~62!,!Line:~39,23!
)
555555.csv,\ID455666         ID

Instead of having:
ID555555.csv;ID455666

I also added a move to have csv file to logfolder and generate owner.csv into basedir.

Here is my current version:

rem @echo off
setlocal EnableDelayedExpansion

REM Get current date/time
set LocalDateTime=
for /f "tokens=* skip=1" %%A in ('wmic os get LocalDateTime') do (
    if not defined LocalDateTime (
        set LocalDateTime=%%A
    )
)

rem Format current date/time as YYYYMMDD_hhmmdd
set VAR_MYDATE=%LocalDateTime:~0,8%_%LocalDateTime:~8,6%
rem Define file and folder locations
set BaseDir=%Cd%
set InputFolder=D:\MSTT_TIMESHEETS
set LogFolder=D:\MSTT_TIMESHEETS_HISTO\%VAR_MYDATE%
set OutFile=%BaseDir%\owner.csv

rem Make sure needed folders and files exist
if not exist %LogFolder% md %LogFolder%
if not exist %InputFolder% exit /b
Pause
if not exist "%InputFolder%\*.csv" exit /b
echo Exit
rem Generate output report
(
    for /f "tokens=*" %%A in ('dir /q "%InputFolder%\*.csv" ^| findstr /b /r /c:"[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]"') do (
        set Line=%%~A
        echo.!Line:~62!,!Line:~39,23!
    )
)>"%OutFile%"

rem Move csv files to log folder
move "%InputFolder%\*.csv" %LogFolder%

rem Run Java
set JAVA_ARGS=-showversion -Dlog4j.overwrite=true -Xmx4096m -Duse_description=true
cd %BaseDir%
java %JAVA_ARGS% -jar "C:\API\mstt-timesheet-import\lib\mstt-timesheet-import.jar" %InputFolder% "../conf/Project-IDs-List.txt"
del /Q %InputFolder%\*.*"
Pause

Select allOpen in new window

For those who propose powershell version, could you please try me to adjust in powershell my last version.
I will do both tests.
Thank you very much for your help.

Thank you very much oBdA, I will test it soon.

To avoid security and permissions issues with windows task scheduler and powershell. Could you please help me to call the ps1 file through a .bat file.
I suppose that this is the command:

powershell.exe -ExecutionPolicy Bypass -Command "Path\xxx.ps1"

Select allOpen in new window

Thank you for your advice.

Either like that (note the use of quotes):

PowerShell.exe -ExecutionPolicy Bypass -Command "& 'Path\xxx.ps1'"

Select allOpen in new window

Or like that:

PowerShell.exe -ExecutionPolicy Bypass -File "Path\xxx.ps1"

Select allOpen in new window

There are subtle differences in what the File/Command arguments do, but when using it from task scheduler, both will achieve the same results.

Thank you oBdA.
I am almost done with the batch version.
I was able to fix the current time issue and the other format version.
This was due to the fact that I comment the @echo off line
The only thing still pending to solve is the owner format:

Here is what I get and in green what it is expected:
Delimiter for owner.csv should be ";"
Here is my current version:

@echo off
setlocal EnableDelayedExpansion


rem Get current date
for /f "tokens=2 delims==" %%G in ('wmic os get localdatetime /value') do set datetime=%%G

rem get year, month, day variables
set Year=%datetime:~0,4%
set Month=%datetime:~4,2%
set Day=%datetime:~6,2%


rem Define file and folder locations
set BaseDir=%Cd%
set InputFolder=D:\MSTT_TIMESHEETS
set LogFolder=D:\MSTT_TIMESHEETS_HISTO\%Year%\%Year%-%Month%
set OutFile=%BaseDir%\owner.csv

rem Make sure needed folders and files exist
if not exist %LogFolder% md %LogFolder%
if not exist %InputFolder% exit /b
if not exist "%InputFolder%\*.csv" exit /b
rem Generate output report
(
    rem Header line for column names
    echo Name;Author

    rem Outout file and author for each CSV file
    for /f "tokens=*" %%A in ('dir /q "%InputFolder%\*.csv" ^| findstr /b /r /c:"[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]"') do (
        set Line=%%~A
        echo.!Line:~62!,!Line:~39,23!
    )
)>>"%OutFile%"

rem Copy csv files to log folder
xcopy "%InputFolder%\*.csv" %LogFolder%

rem Run Java
set JAVA_ARGS=-showversion -Dlog4j.overwrite=true -Xmx4096m -Duse_description=true
cd %BaseDir%
java %JAVA_ARGS% -jar "C:\API\mstt-timesheet-import\lib\mstt-timesheet-import.jar" %InputFolder% "../conf/Project-IDs-List.txt"
del /Q %InputFolder%\*.*"
Exit

Select allOpen in new window

@Bill: could you please help me to adjust the last issue with owner.csv
Thank you in advance for your help.

Here is what I get and in green what it is expected:

@LD16

  The issue you described is NOT from Bill's code, it is entirely the result of the code you provided in the initial comment.

Specifically:

echo.!Line:~62!,!Line:~39,23!

Select allOpen in new window

Assuming that all of your file name lengths are exactly the same you could ammend as follows:

echo.!Line:~60!;!Line:~40,20!

Select allOpen in new window

TPKA-PXAY-NXFR-KM9X

Ok, my fault.
However the echo expect to have the same name and author lengths?

Here is my last version:

@echo off
setlocal EnableDelayedExpansion
rem Get current date
for /f "tokens=2 delims==" %%G in ('wmic os get localdatetime /value') do set datetime=%%G
rem get year, month, day variables
set Year=%datetime:~0,4%
set Month=%datetime:~4,2%
set Day=%datetime:~6,2%


rem Define file and folder locations
set BaseDir=%Cd%
set InputFolder=D:\MSTT_TIMESHEETS
set LogFolder=D:\MSTT_TIMESHEETS_HISTO\%Year%\%Year%-%Month%
set OutFile=%BaseDir%\owner.csv

rem Make sure needed folders and files exist
if not exist %LogFolder% md %LogFolder%
if not exist %InputFolder% exit /b
if not exist "%InputFolder%\*.csv" exit /b
rem Generate output report
(
    rem Header line for column names
    echo Name;Author

    rem Outout file and author for each CSV file
    for /f "tokens=*" %%A in ('dir /q "%InputFolder%\*.csv" ^| findstr /b /r /c:"[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]"') do (
        set Line=%%~A
       echo.!Line:~60!;!Line:~40,20!
    )
)>>"%OutFile%"

rem Copy csv files to log folder
xcopy "%InputFolder%\*.csv" %LogFolder%

rem Run Java
set JAVA_ARGS=-showversion -Dlog4j.overwrite=true -Xmx4096m -Duse_description=true
cd %BaseDir%
java %JAVA_ARGS% -jar "C:\API\mstt-timesheet-import\lib\mstt-timesheet-import.jar" %InputFolder% "../conf/Project-IDs-List.txt"
del /Q %InputFolder%\*.*"
Exit

Select allOpen in new window

@LD16,

  The original code you provided is made assuming set file name lengths for the file names to be parsed.

If that isn't correct then there needs to be a little more done to split it up.

Thank you Bill.
I retested your proposal with the following information:
CSV file name:  ID4444.csv
Author: ID55555

In black what I get and in green what is expected:
 How should adjust those echo lines?

@LD16,

There is a difference in length of the file name you used for the test and the name bill used for a test.

The code you originally provided expects all file lengths to be the same, or it will provide strange results when lengths do not match.

specifically:

for /f "tokens=*" %%A in ('dir /q "%InputFolder%\*.csv" ^| findstr /b /r /c:"[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]"') do (
        set Line=%%~A
       echo.!Line:~62!;!Line:~39,23!
    )

Select allOpen in new window

I am not at a computer to test but I believe this tweak will solve most if not all of the issues:

for /f "tokens=*" %%A in ('dir /q "%InputFolder%\*.csv" ^| findstr /b /r /c:"[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]"') do (
        set Line=%%~A
        For /f "tokens=1-2 delims=\ " %%a in ("!Line:~39!") DO (
          echo.%%~a;%%~b
        )
    )

Select allOpen in new window

Given that even in your examples you've found two different values being provided you have multiple file lengths.

however this follows the assumption that the file output up to character 39 is fairly static.

give it a shot and let us know.

-Ben

edit:  flipped / to \ and flipped a and b output

@LD16,

Can you post up the output of this command:

dir /q ID4444.csv

I get the following but it sounds like your output may be different.

Directory of b:\EE\EE29144591

5/06/2019  08:47 AM           123,456 \Everyone              ID4444.csv
              1 File(s)        123,456 bytes

Select allOpen in new window

---

»bp

Looking at what Ben just posted I see that you folks may be using 24 hour ("military") time for your system time setting, while I am using AM/PM mode.  That is making the offsets in the DIR /Q command different for me here than you folks there, by 3 positions.

But other than that the output from the DIR /Q command here is always the same in terms of what positions the various output columns begin in.  The length of file names doesn't seem to affect that.  Are you seeing something different there?

---

»bp

Thank you very much. I modified loop as proposed and it works now!
Thank you again for your help:

@echo off
setlocal EnableDelayedExpansion


rem Get current date
for /f "tokens=2 delims==" %%G in ('wmic os get localdatetime /value') do set datetime=%%G

rem get year, month, day variables
set Year=%datetime:~0,4%
set Month=%datetime:~4,2%
set Day=%datetime:~6,2%


rem Define file and folder locations
set BaseDir=%Cd%
set InputFolder=D:\MSTT_TIMESHEETS
set LogFolder=D:\MSTT_TIMESHEETS_HISTO\%Year%\%Year%-%Month%
set OutFile=%BaseDir%\owner.csv

rem Make sure needed folders and files exist
if not exist %LogFolder% md %LogFolder%
if not exist %InputFolder% exit /b
if not exist "%InputFolder%\*.csv" exit /b
rem Generate output report
(
    rem Header line for column names
    echo FileName;Author

    rem Outout file and author for each CSV file
for /f "tokens=5,6 Delims=\ " %%A in ('dir /q "%InputFolder%\*.csv" ^| findstr /b /r /c:"[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]"') do (
        set "_FileName=%%~B"
        set "_Author=%%~A"
       echo.!_FileName!;!_Author!
    )
)>>"%OutFile%"

rem Copy csv files to log folder
xcopy "%InputFolder%\*.csv" %LogFolder% /Y

rem Run Java
set JAVA_ARGS=-showversion -Dlog4j.overwrite=true -Xmx4096m -Duse_description=true
cd %BaseDir%
java %JAVA_ARGS% -jar "C:\API\mstt-timesheet-import\lib\mstt-timesheet-import.jar" %InputFolder% "../conf/Project-IDs-List.txt"
del /Q %InputFolder%\*.*"
Exit

Select allOpen in new window

@LD16,

Great!  Glad to help!  :)