I have a folder with a file named: 'template.xlsx', and hundreds of files named:

  • old_2301 Item 1.xlsx
  • old_2014 Item 2.xlsx
  • old_5321 Item 3.xlsx
  • ...
  • old_3212 Item 200.xlsx

I want to copy the file template.xksx and create 200 new files:

  • 2301 Item 1.xlsx
  • 2014 Item 2.xlsx
  • 5321 Item 3.xlsx
  • 3212 Item 200.xlsx

I've tried something like:

In the folder, type Shift+Right shift: select "open command-window here"

for %a in (*.*) do copy "template.xlsx" ... ren ...?

I want to rename the copied template.xlsx files to the same name as the other files, but with old_ trimmed away.

Is there a simple way to do this?

  • 4
    Unless you need to redistribute the code that does this, I'd strongly suggest learning some Python. The code is much simpler, hence faster to write, easier to read, and less prone to bugs. I have colleagues trying to do fancy stuff in bash, it usually ends in headaches. – Davidmh Aug 22 '17 at 11:59
  • 5
    @Davidmh Why learn python when it is a trivial task for a batch file? In addition, Windows 7 does not come with python or bash installed. – DavidPostill Aug 22 '17 at 12:57
  • @DavidPostill my point is that in Python it is easier, more readable, and more easily extended, at the cost of learning a bit of a new language (at this level, should be easy for a competent programmer like the OP), and installing it (which is easy enough, if you don't care about distributing it). And while the original task may be trivial, in my experience people will try to expand it, make it incrementally more complicated, and end up in a horrible mess of awk and sed (or whatever equivalent on Windows are), while Python helps to tame the complexity. – Davidmh Aug 24 '17 at 8:40
  • Also, I am not recommending bash, I consider it in the same category as batch. I mentioned it because my colleagues and me work on Linux. – Davidmh Aug 24 '17 at 8:41
  • @Davidmh I see no reason to use Python in a Windows shop. PowerShell provides much of the same "easy to learn"-ness but is more portable (Vista+ should work with the scripts) and the syntax IMO is even better. – Austin T French Aug 25 '17 at 0:59

I want to copy template.xlsx to the name of the other files, with old_ removed

Use the following batch file:

@echo off
setlocal enabledelayedexpansion
rem get list of file names
for /f "usebackq tokens=*" %%i in (`dir /b old_*.xlsx`) do (
  rem save the filename
  set _name=%%i
  rem remove old_ from the name
  set _name=!_name:old_=!
  rem do the copy to the modified name
  copy "template.xlsx" "!_name!"

Further Reading

  • Where should I type this code? How I run this code? – I am the Most Stupid Person Aug 23 '17 at 5:23
  • @DonkeyKingandDonkeyKing In a batch file, which can be in the directory of interest. Then run the batch file. – DavidPostill Aug 23 '17 at 7:35
  • @DonkeyKingandDonkeyKing in case you don't know how to create a batch file: You can type/paste the text in a text editor (Notepad, Notepad++ or whatever you like), and save it as 'filename.bat' instead of 'filename.txt'. Then you can just double-click and execute it as if it was a regular program (.exe). – Stewie Griffin Aug 23 '17 at 10:27
  • @DonkeyKingandDonkeyKing What Is A Batch File In Windows? How To Create A Batch File? – DavidPostill Aug 23 '17 at 10:34

You have to use the files named old_* as your name source and split at the underscore.

@Echo off
For /f "tokens=1* delims=_" %%A in (
    'Dir /B /A-D "old_* Item *.xlsx"'
) Do Echo Copy template.xlsx "%%B"

If the output looks OK remove the echo in front of copy.

Explaining the parsing for /f options:

filename  old_2301 Item 1.xlsx
delims       _
tokens     1 _ * (rest)
for var   %%A %%B

A variant for the command line without batch:

For /f "tokens=1* delims=_" %A in ('Dir /B /A-D "old_*.xlsx"') Do Copy template.xlsx "%B"
  • I guess this works as a charm, but I ended up using David's answer so I accepted his :) Anyway, thanks! :) – Stewie Griffin Aug 22 '17 at 10:51
  • Speedwiese there isn't a big difference as the copy will take up most of the time. Added a cmd line variant, no batch needed then. – LotPings Aug 22 '17 at 11:00

Unless I actually wanted a program that I could run over and over, if this is just a one-off or a few-off, I would use Notepad++ or any other text editor with block copy and paste. Open a command prompt in the relevant directory and type dir/b > 1.txt, which results in e.g.:

D:\MiscJunk\1>type 1.txt
old_2014 Item 2.txt
old_2301 Item 1.txt
old_5321 Item 3.txt

Edit 1.txt in Notepad++ and add as many of these lines as needed:

copy template.txt ""
copy template.txt ""
copy template.txt ""

Block copy (Alt Shift arrow keys) the text:

2014 Item 2.txt
2301 Item 1.txt
5321 Item 3.txt

and paste it between the quotes:

copy template.txt "2014 Item 2.txt"
copy template.txt "2301 Item 1.txt"
copy template.txt "5321 Item 3.txt"

Then after checking the commands are correct and adjusting accordingly, just copy&paste those commands back into the command prompt, resulting in:

2014 Item 2.txt
2301 Item 1.txt
5321 Item 3.txt

This method is ultra-simple with no debugging needed, and there's very little that can go wrong. Knocking up blocks of commands in Notepad++ is something I do fairly regularly.

  • I used David's method first, but it turned out I had to rename them again... This was perfect the second time around :) Thanks a lot! – Stewie Griffin Aug 30 '17 at 11:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.