Copy and rename with BAT File

jarett

Board Regular
Joined
Apr 12, 2021
Messages
179
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have read a lot of post on this and solved one of my problems for copying and rename a file with the following code
Code:
xcopy "M:\abc.csv" "O:\File"
set HR=%time:~0,2%
set HR=%Hr: =0%
set HR=%HR: =%
rename "O:\File\abc.csv" "x_%date:~10,4%-%date:~4,2%-%date:~7,2%_%HR%%time:~3,2%.csv"

My output on this is file name = x_YYYY-MM-DD_TTTT.csv, I don't understand how all the variables work, but I need to get this bat file to rename abc.csv file to something like "MM-DD-YYYY-#XXX.csv". The "#" would be an incremental value depending on how many files are renamed in a day(needs to start over the next day), the "XXX" would just be a set text value for every file. The abc.csv will have the same name every day. I started playing with the script but I'm getting stuck. I know I am missing the incremental part.

Code:
xcopy "M:\abc.csv" "P:\File"
set HR=%time:~0,2%
set HR=%Hr: =0%
set HR=%HR: =%
rename "P:\File\abc.csv" "%date:~4,2%-%date:~7,2%-%date:~10,4%XXX.csv"

And the last question which will come up down the road, in the folder that I am copying and renaming, they will have 3 files (abc.csv, def.csv, hij.csv) all 3 will be copied and renamed. Same date and incremental format but with different "XXX" text. Do you have all this in one bat file (I am assuming you cant accommodate all of this in the same script, you would have to do separate lines) or do you break it up into 3 different bat files?
 
I tried simplifying the code and when I ran it, the destination folder that had the 04-29-2021#001.csv file produced the next file as abc.csv, is this because I removed the part of moving the file to a temp folder? I might not have explained my self well enough abc.csv will be moved to new folder and renamed 04-29-2021#001.csv, on the same day I might need to move abc.csv(new version saved over last version) again and the new file in the destination needs to be 04-29-2021#002.csv.
Code:
xcopy "M:\TEST\abc.csv" "P:\Jarett\"
rem Presumes %date% returns Ddd DD/MM/YYYY so may need adjusting if American format
set today=%date:~4,2%-%date:~7,2%-%date:~10,4%
set count=0
for %%x in (abc.csv) do (set /a count+=1)
set count=00%count%
set count=%count:~-3%
rename "P:\Jarett\abc.csv" "%today%-#%count%.csv"
That won't work because you are counting the number of abc.csv files, not *.csv files.

Here is the code fully commented. This should do exactly what you want to do:
Code:
rem Batch file to be run from the directory where the MM-DD-YYYY-#XXX.csv files reside
rem Presumes DATE returns (e.g.) Fri 30/04/2021 so it may need adjusting if American MM-DD-YYYY format is returned by DATE
rem                                 ^..^..^
rem                                 4  7  10
rem Set the "today" variable to characters 8 and 9, dash, 5 and 6, dash, 11-14
set today=%date:~7,2%-%date:~4,2%-%date:~10,4%
rem Check for whether the temp folder exists
if not exist .\temp mkdir temp
rem Move all of the files that match the pattern for "today" to temp
move %today%* temp
rem >>> Uncomment the XCOPY command here to bring it from your directory where Access outputs the file
rem xcopy "M:\TEST\abc.csv" "P:\Jarett\"
rem Move the abc.csv to temp (noting this could be done in one step with the xcopy if you so wish
move abc.csv temp
rem Change the current directory to temp before we count the *.csv files
cd temp
rem Set the variable "count" to zero
set count=0
rem Loop through all of the *.csv files, including the abc.csv, getting the number of them to rename the file
for %%x in (*.csv) do (set /a count+=1)
rem Pad the "count" variable with two noughts
set count=00%count%
rem Truncate the "count" variable to remove zeros at the front. Examples: "00103" > "103", "0019" > "019", and "004" remains as is
set count=%count:~-3%
rem Now rename the abc.csv file to match the other files with the "count" variable
rename abc.csv %today%-#%count%.csv
rem Move all of the .csv files back to the directory above
move *.csv ..
rem Pause so you can see the results of the run - this can be removed if you wish
pause
I have tested it and it works perfectly so if you follow these comments and get the abc.csv file into the directory where the other .csv files are it should work.
 
  • Like
Reactions: yky
Upvote 0
Solution

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
That won't work because you are counting the number of abc.csv files, not *.csv files.

Here is the code fully commented. This should do exactly what you want to do:
Code:
rem Batch file to be run from the directory where the MM-DD-YYYY-#XXX.csv files reside
rem Presumes DATE returns (e.g.) Fri 30/04/2021 so it may need adjusting if American MM-DD-YYYY format is returned by DATE
rem                                 ^..^..^
rem                                 4  7  10
rem Set the "today" variable to characters 8 and 9, dash, 5 and 6, dash, 11-14
set today=%date:~7,2%-%date:~4,2%-%date:~10,4%
rem Check for whether the temp folder exists
if not exist .\temp mkdir temp
rem Move all of the files that match the pattern for "today" to temp
move %today%* temp
rem >>> Uncomment the XCOPY command here to bring it from your directory where Access outputs the file
rem xcopy "M:\TEST\abc.csv" "P:\Jarett\"
rem Move the abc.csv to temp (noting this could be done in one step with the xcopy if you so wish
move abc.csv temp
rem Change the current directory to temp before we count the *.csv files
cd temp
rem Set the variable "count" to zero
set count=0
rem Loop through all of the *.csv files, including the abc.csv, getting the number of them to rename the file
for %%x in (*.csv) do (set /a count+=1)
rem Pad the "count" variable with two noughts
set count=00%count%
rem Truncate the "count" variable to remove zeros at the front. Examples: "00103" > "103", "0019" > "019", and "004" remains as is
set count=%count:~-3%
rem Now rename the abc.csv file to match the other files with the "count" variable
rename abc.csv %today%-#%count%.csv
rem Move all of the .csv files back to the directory above
move *.csv ..
rem Pause so you can see the results of the run - this can be removed if you wish
pause
I have tested it and it works perfectly so if you follow these comments and get the abc.csv file into the directory where the other .csv files are it should work.
Appreciate the comments now I am trying to dissect everything. How does it know which files to move just from %today%
rem Move all of the files that match the pattern for "today" to temp move %today%* temp ? Just curious. Got it to actually work and rename the file the second time I ran the bat., it's saving the files on my C: drive where the temp folder is, not in the temp folder (don't want in there). On the last linemove *.txt .. do I edit it to move *txt P:\Jarett\SM_TEST\IN
And I am assuming because of the %today% variable, that is what will trigger the count to go back to 1 tomorrow?
 
Upvote 0
Appreciate the comments now I am trying to dissect everything. How does it know which files to move just from %today%
rem Move all of the files that match the pattern for "today" to temp move %today%* temp ? Just curious. Got it to actually work and rename the file the second time I ran the bat., it's saving the files on my C: drive where the temp folder is, not in the temp folder (don't want in there). On the last linemove *.txt .. do I edit it to move *txt P:\Jarett\SM_TEST\IN
And I am assuming because of the %today% variable, that is what will trigger the count to go back to 1 tomorrow?
Answered my own question about the last move line, it worked.
 
Upvote 0
I've been tinkering with the code to see how it all works so I can better understand everything. I tried rename abc.csv %today%-%count%abc%count%.csv to see if you could have the count variable in the file name twice and it keeps saving the file the same name everytime I run the bat, 04-30-2021-1abc1.csv. Could someone at least give me an idea of what line would be causing this.
 
Upvote 0
Also I ran the bat file today and it produced 04-30-2021-#001.csv, then ran it again and the only file in the destination is 04-30-2021-#001.csv. If it is only moving file abc.csv to the temp folder, what is it counting, the comment says we change dir to temp before we count, abc.csv is the only file it counts right? Or am I missing an important part.
 
Upvote 0
I've been tinkering with the code to see how it all works so I can better understand everything. I tried rename abc.csv %today%-%count%abc%count%.csv to see if you could have the count variable in the file name twice and it keeps saving the file the same name everytime I run the bat, 04-30-2021-1abc1.csv. Could someone at least give me an idea of what line would be causing this.
There is a 1 before and after abc. Isn't that exactly what you expected - count variable twice in the file name?
 
Upvote 0
Also I ran the bat file today and it produced 04-30-2021-#001.csv, then ran it again and the only file in the destination is 04-30-2021-#001.csv. If it is only moving file abc.csv to the temp folder, what is it counting, the comment says we change dir to temp before we count, abc.csv is the only file it counts right? Or am I missing an important part.
Why don't you command out everyhthing after "move abc.csv" and see what are in the temp directory?
 
Upvote 0
Hi everyone, I'm new to the forum, I signed up thanks to Jarett but I'm glad I did. I love excel and have used it a lot.


@jarett, I replied on stackowerflow, but they lack a lot of information on how you want the numbering to work. I add the code here too so if anyone wants they can help find the solution. I took the liberty of embellishing the script a bit.

Here I insert the lean version, if you want you can see the full version on stackoverflow.

Code:
@echo off
setlocal EnableDelayedExpansion

rem set source and destination directory/folder
set "source_folder=M:\TEST"
set "destination_folder=P:\Jarett"

rem assume all file of type and extension "CSV"

rem create 3 variable for the file name. You can change the name of the file abc,def,hij
set "File1=abc"
set "File2=def"
set "File3=hij"

rem create a map and lookup that associate abc with XXX01, def with XXX02, hij with XXX03. You can change the string XXX01, XXX02, XXX03
SET map=!File1!-XXX01;!File2!-XXX02;!File3!-XXX03;

for %%N in (!File1!, !File2!, !File3!) do (

  rem Set the "today" variable. Choice the correct for you language setting and delete/comment others.
  set "today=%date:~7,2%-%date:~4,2%-%date:~10,4%"

  rem Count today csv, add 1 and prepare to pad.
  for /f %%f in (' dir !destination_folder!\!today!*.csv /b /a-d 2^>nul ^| find /c /v "" ') do set /a "count=%%f+10001"

  rem prepare time part
  set "now=!time:~0,-3!" & set "now=!now: =0!" & set "now=!now::=.!"
 
  call :lookup_XXX_map %%N
 
  move !source_folder!\%%N.csv !destination_folder!\!today!_!now!_!count:~-3!_!XXX!.csv

)

echo Exit/End ... & pause

goto :eof

:lookup_XXX_map
  CALL SET XXX=%%map:*%1-=%%
  SET XXX=%XXX:;=&:%"
goto :eof
 
Upvote 0
I add the test version so everyone can try the new changes. Forgive me for the visual improvement but it helps a lot in some cases.

Code:
@echo off & setlocal EnableDelayedExpansion

cls

set sizemax=130
mode con: COLS=%sizemax% LINES=35

rem create "box" macro "Box"
set "Box=call :box"

%Box% "Prepare the test environment ..."
pause

cd %tmp%
mkdir TEST 2>nul
mkdir Jarett 2>nul

set "source_folder=%tmp%\TEST"
set "destination_folder=%tmp%\Jarett"

echo >%source_folder%\abc.csv
echo >%source_folder%\def.csv
echo >%source_folder%\hij.csv

%Box% "Source Folder"
dir %source_folder% & pause

%Box% "Destination Folder"
dir %destination_folder% & pause

cls
%Box% "Test the script." & pause

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
@echo off
setlocal EnableDelayedExpansion

rem set source and destination directory/folder
rem remove rem before using the script in production environment in the following two lines
rem set "source_folder=M:\TEST"
rem set "destination_folder=P:\Jarett"

rem assume all file of type and extension "CSV"

rem create 3 variable for the file name. You can change the name of the file abc,def,hij
set "File1=abc"
set "File2=def"
set "File3=hij"

rem create a map and lookup that associate abc with XXX01, def with XXX02, hij with XXX03. You can change the string XXX01, XXX02, XXX03
SET map=!File1!-XXX01;!File2!-XXX02;!File3!-XXX03;
%Box% "map=!map!" & pause

for %%N in (!File1!, !File2!, !File3!) do (

  rem Set the "today" variable. Choice the correct for you language setting and delete/comment others.
  set "today=%date:~7,2%-%date:~4,2%-%date:~10,4%"
  set "today=%date:~4,2%-%date:~7,2%-%date:~10,4%"
  set "today=%date:~3,2%-%date:~0,2%-%date:~6,4%" & rem italian default to MM-DD-YYYY
  %Box% "file:%%N today=!today!" & pause

  rem Count today csv, add 1 and prepare to pad.
  for /f %%f in (' dir !destination_folder!\!today!*.csv /b /a-d 2^>nul ^| find /c /v "" ') do set /a "count=%%f+10001"
  %Box% "file:%%N count=!count!" & pause

  rem prepare time part
  set "now=!time:~0,-3!" & set "now=!now: =0!" & set "now=!now::=.!"
  %Box% "file:%%N now=!now!" & pause

  call :lookup_XXX_map %%N
  %Box% "file:%%N XXX=!XXX!" & pause

  %Box% "move !source_folder!\%%N.csv !destination_folder!\!today!_!now!_!count:~-3!_!XXX!.csv" & pause
  move !source_folder!\%%N.csv !destination_folder!\!today!_!now!_!count:~-3!_!XXX!.csv

  %Box% "Destination Folder"
  dir !destination_folder! & pause
)

echo Exit/End ... & pause

goto :eof
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

:box
  set "s=%~1"
  set /A s1=%sizemax%-1, s2=s1-1
  set "l="
  for /L %%B in (1,2,%sizemax%) do set "l=!l!--" & if "!s:~%s1%,1!" == "" set "s= !s! "
  set "s=!s:~1,%s2%!"
  set "l=!l:~0,%s2%!"
  for %%B in ("" "+%l%+" "|%s%|" "+%l%+" "") do echo(%%~B
goto :eof

:lookup_XXX_map
  CALL SET XXX=%%map:*%1-=%%
  SET XXX=%XXX:;=&:%"
goto :eof
 
  • Like
Reactions: yky
Upvote 0

Forum statistics

Threads
1,223,516
Messages
6,172,777
Members
452,477
Latest member
DigDug2024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top