batch script - run command on each file in directory

Question

I need to convert some xls files into xlsx files. I can successfully convert one xls file into xlsx by running this command into cmd prompt (windows):

ssconvert inputFileName.xls outputFileName.xlsx

(ssconvert is a Gnumeric's command-line utility that can convert between different spreadsheet file formats)

I'd like to write a batch file that FOR EACH file in a specified directory runs the command I wrote above, using the current file name both for input and for output filename.

For example, if I have this set of files:

c:\directory\file1.xls
c:\directory\file2.xls
c:\directory\file3.xls

the output should be

c:\directory\file1.xlsx
c:\directory\file2.xlsx
c:\directory\file3.xlsx

so the batch pseudo code should be something like

directory = c:\directory\
for (fileName in directory)
    ssconvert fileName.xls fileName.xlsx

Can anyone help me?

Does this answer your question? [How do you loop in a Windows batch file?](https://stackoverflow.com/questions/1355791/how-do-you-loop-in-a-windows-batch-file) — bobobobo, Dec 19 '21 at 02:33

score 67 · Accepted Answer · edited Jan 15 '20 at 23:41

67

for /r %%v in (*.xls) do ssconvert "%%v" "%%vx"

a couple have people have asked me to explain this, so:

Part 1: for /r %%v in (*.xls)

This part returns an array of files in the current directory that have the xls extension. The %% may look a little curious. This is basically the special % character from command line as used in %PATH% or %TEMP%. To use it in a batch file we need to escape it like so: %%PATH%% or %%TEMP%%. In this case we are simply escaping the temporary variable v, which will hold our array of filenames.

We are using the /r switch to search for files recursively, so any matching files in child folders will also be located.

Part 2: do ssconvert "%%v" "%%vx"

This second part is what will get executed once per matching filename, so if the following files were present in the current folder:

c:\temp\mySheet.xls, c:\temp\mySheet_yesterday.xls, c:\temp\mySheet_20160902.xls

the following commands would be executed:

ssconvert "c:\temp\mySheet.xls" "c:\temp\mySheet.xlsx" ssconvert "c:\temp\mySheet_yesterday.xls" "c:\temp\mySheet_yesterday.xlsx" ssconvert "c:\temp\mySheet_20160902.xls" "c:\temp\mySheet_20160902.xlsx"

edited Jan 15 '20 at 23:41

Fortranner

2,525
2
22
25

answered Jan 09 '13 at 14:43

paul

21,653
1
53
54

I could not get it to work with the /r. Removed that and it was fine. – Brad Irby Jun 11 '14 at 14:05
@BradIrby I've used it to run a jar-Konverter instead of ssconvert and it was working well, so you might had some issues with your dos cmd.exe ;-). – Sebastian Mar 24 '15 at 07:57
FWIW /r means "recursive" (and seemed to work ok here) (the "%%vx" in this instance just means "use %%v and add the letter x after that") – rogerdpack Jun 30 '15 at 18:22
3

remember that if you try from command prompt it works with single percent sign while in a batch(cmd) file it needs the double percent sign! – Costin Gușă Oct 06 '15 at 23:57
4

Why didn't you explain the command so that others like me can make better use of it...? – Tomáš Zato Dec 14 '15 at 11:41
you should add some explanation to what you actually did. People viit stackoverflow to learn, not to copy - at least they should – Julian Dec 12 '16 at 22:20
Is there a way to remove the old extension ? Because "%%v" is the whole name – Foxhunt Jun 18 '20 at 23:22
1

@Phoenix you could use `"%%~nv"` to just get the filename without extension – paul Jun 22 '20 at 10:39
@paul could you point me to some resource about `~nv` syntax? – Wodzu Oct 20 '21 at 08:06
@Wodzu https://stackoverflow.com/a/54963037/1280587 – paul Oct 25 '21 at 10:09

score 32 · Answer 2 · edited Nov 16 '15 at 11:11

32

Actually this is pretty easy since Windows Vista. Microsoft added the command FORFILES

in your case

forfiles /p c:\directory /m *.xls /c "cmd /c ssconvert @file @fname.xlsx"

the only weird thing with this command is that forfiles automatically adds double quotes around @file and @fname. but it should work anyway

edited Nov 16 '15 at 11:11

davestevens

2,233
2
16
19

answered Jan 09 '13 at 15:04

weberik

2,636
1
18
11

Note that the quotes may mess with other commands, [but can be removed](https://stackoverflow.com/a/32491884/5475891) – phflack Jan 30 '18 at 17:18

score 16 · Answer 3 · answered Jan 09 '13 at 14:42

16

you can run something like this (paste the code bellow in a .bat, or if you want it to run interractively replace the %% by % :

for %%i in (c:\directory\*.xls) do ssconvert %%i %%i.xlsx

If you can run powershell it will be :

Get-ChildItem -Path c:\directory -filter *.xls | foreach {ssconvert $($_.FullName) $($_.baseName).xlsx }

answered Jan 09 '13 at 14:42

Loïc MICHEL

24,935
9
74
103

Does the first command work as %%i already has .xls extension so you'll be converting to .xlsxlsx – CashCow Mar 25 '19 at 12:08
What if my directory has a space character in it. How to handle that? – Wodzu Mar 20 '23 at 15:42

score 0 · Answer 4 · answered Oct 10 '17 at 05:27

0

I am doing similar thing to compile all the c files in a directory.
for iterating files in different directory try this.

set codedirectory=C:\Users\code
for /r  %codedirectory% %%i in (*.c) do 
( some GCC commands )

answered Oct 10 '17 at 05:27

Rishikesh Ayre

99
3
14

batch script - run command on each file in directory

4 Answers4

Linked

Related