Tutorial: Using Excel to Apply Batch Commands to Scattered Files (Testing MP3s)


Generating the List of Files
Formulating the Desired Command
Using the Spreadsheet
Creating the Batch File
Running the Batch File; Results



I wanted to apply the same command to many different files, scattered among various folders. This post describes how I used a spreadsheet to achieve that. I used Microsoft Excel 2010. LibreOffice Calc is one free alternative. Presumably some of the steps described here would have to be modified in other spreadsheets.

I was not familiar with many of the assorted programming and scripting languages and command sets that might have been useful for this project. I had found that Windows (and previously DOS) batch commands were usually adequate. To me, at least, they also seemed relatively easy to understand, and did not require much investment in learning a language.

Typically, in a batch command involving files, I would find myself indicating an input, a process, and an output. So, for example, the command COPY X.DOC Y.DOC would apply the Copy process to the input file (X.DOC) to create an output file (Y.DOC). Many batch commands would have options (also sometimes called by other names, e.g., “flag,” “parameter”). An example: the /V option in COPY /V X.DOC Y.DOC would verify that Y.DOC had been written correctly. (That particular option rarely seemed necessary, but it does illustrate the point.)

Most batch commands could be run in two different ways: they could indeed be put into a batch file, perhaps with a set of other commands, but they could also (sometimes with modification) be run by themselves on the command line. Batch files had some advantages over direct commands. Among other things, they could be saved for future use, and the combination of multiple commands within a single batch file could be important for achieving a desired result.

To enter a single command, I would have needed to have access to the command line. This typically meant opening a command window (what some people used to call a DOS box). There were multiple ways to open a command window. Note that some commands would require the privileges of an elevated command prompt. I ran my machine as an administrator, so in writing up these remarks I did not notice any administrator vs. user issues that might have arisen if I had been running as a user.

Here, I describe how I generated a list of files and used Excel to convert that list into a batch file. The files in question were scattered among many different folders on my computer. My tech blogs contain a number of posts applying these techniques to various kinds of processes. A Google search can be modified to find specific examples.

Generating the List of Files

In this example, I wanted to apply a certain process to all MP3 files on my computer. I knew those files were located on drives I and J. To get their exact names and locations, I used this command:

I:\>DIR *.mp3 /S /A-D /B > MP3list.txt

There are several things to understand about that command:

  • I:\> is the “prompt.” It is produced by the computer. It basically says, What do you want me to do?
  • DIR is the command. Here, I use capital letters to indicate elements of the command, but DOS commands were not case-specific.
  • *.mp3 means “apply this to all files with an MP3 extension.” So the command that I am issuing would apply to X.MP3 and YYY.MP3 but not to X.MP4.
  • /S /A-D /B are options that are available for the DIR command. To get the list of options for the commands built into Windows, I could just type the command followed by /?. So DIR /? would explain the /S option for the DIR command.
  • > MP3list.exe means “send the output to a new file called MP3list.txt.” If I wanted to append the output to an already existing file called MP3list.txt, I would use >> rather than >.
  • The order of some of these command parts is important; for others, it is less important.

In this particular command, the I:\> prompt means we are beginning at the root (also confusingly known as the top level) of drive I. All files and folders on drive I (e.g., I:\Folder Number One) would be subsidiaries of this top level. The top level was where I wanted to start, because I wanted to use the /S option to apply my DIR command to all of the subfolders (also called subdirectories) on drive I. My command might not have detected many of the MP3 files on drive I if I had started at this command prompt: I:\Word Documents\Docs from May 1992\>.

After running the foregoing command to create MP3list.txt in drive I:\, I re-ran it in the root of drive J (using >>) to make sure MP3list.txt also contained the names and locations of the MP3 files on drive J. To get from drive I to drive J on the command line, I typed simply J: to change drives, and then CD \ to position me at the root of J (if I wasn’t there already). I used Windows Explorer to move MP3list.txt from I:\ to J:\ but I could also have used the MOVE command.

Formulating the Desired Command

The process that I wanted to apply to each of my MP3 files was as follows: I wanted to capture error messages that might arise while converting the MP3s to WAV format. I did not actually want a bunch of new WAV files; I just wanted to see if the conversion process would run into problems with any particular files. I would want to look at any MP3 files generating problems, to make sure those files were not corrupt. In other words, I was going through all this effort to check whether my MP3s were in good shape. I was not certain whether the conversion process would catch errors in the MP3s; I just wanted to try and see what happened.

I found that Boxoft offered a free MP3 to WAV conversion program. As with most Windows programs, that converter offered a GUI interface to make the program easy to use. For present purposes, I was more interested in the command-line program that Boxoft included in the download. That program was called AlltowavCmd.exe. Files with that .exe extension (and also files with certain other extensions, notably .bat) were executable: they would run if you typed their name on the command line, or double-clicked on them in Windows Explorer.

AlltowavCmd.exe would not run, on the command line, unless I was working in the same directory. That is, a copy of AlltowavCmd.exe had to be present in the folder where the command-line prompt was located. If AlltowavCmd.exe was in I:\ and I was looking at a prompt that said J:\Subfolder\, I might find that commands invoking AlltowavCmd.exe would not run. There would be an exception if AlltowavCmd.exe was specified in my system’s PATH, and possibly also if I had a copy of AlltowavCmd.exe in C:\Windows. But I did not want or need those complications. I just used Windows Explorer to put a copy of AlltowavCmd.exe in I:\, where I planned to do most of my work.

Like the DIR command, AlltowavCmd.exe came with certain options. But unlike DIR, I could not get those options by typing AlltowavCmd /? because AlltowavCmd.exe was not programmed to work that way. When I typed AlltowavCmd /? the computer said, “Input File does not found.” For guidance on how to use AlltowavCmd.exe, I had to rely instead on a file called “Command line.txt” that was installed when I installed the Boxoft program.

Those instructions said that, for my purposes, the command I wanted to use would look something like this:

alltowavcmd -Otest.wav -S44100 -B16 >> errors.txt

In that command, alltowavcmd was the process being run. The -O flag indicated the name of the output file. According to the way AlltowavCmd.exe was programmed, apparently I was not allowed to leave a space after the O. The -S and -B flags indicated the output sample and bit rates: I would be producing a 44,100Hz, 16-bit WAV file named Test.wav. I would use that same output filename in each command because, like I say, I did not want a bunch of new WAVs. I was happy to see Test.wav get overwritten each time. I only cared about the command line errors that would be captured in Errors.txt. (I was not sure whether AlltowavCmd.exe was programmed to be case-specific — whether, that is, it would care if I used an upper- or lower-case O or S or B in the foregoing command.) I used “>>” rather than “>” because I wanted errors.txt to capture all of the error messages from every run of the AlltowavCmd command.

The AlltowavCmd command just shown was deficient in one regard: it failed to specify the input file. I would be using the spreadsheet to provide that part.

Using the Spreadsheet

After running the foregoing DIR command, I had information in MP3list.txt. That file consisted of many entries, each looking approximately like this:

I:\Folder XYZ\Subfolder A\Hall & Oates–Adult Education.mp3

This said, of course, that this Hall & Oates MP3 was located in that particular folder. I would be using the spreadsheet to adapt the foregoing AlltowavCmd command to this particular MP3, and to many other MP3s scattered around my computer.

The next step was to copy all of the information out of MP3list.txt and insert it into my spreadsheet. Simple copy and paste (e.g., Ctrl-C and Ctrl-V) commands worked for this. I pasted the list of files from MP3list.txt starting in cell A1, at the top left corner of the blank spreadsheet.

Next, I wanted to work up the set of needed steps on the first row, using just the first listed file as a prototype. Once I had those steps worked out, Excel would allow me to copy the top row of cells down to all other rows.

So cell A1 of my Excel spreadsheet contained the Hall & Oates file and path name stated above. (The “path” is the part that names the folders where the file is located.) In cell B1, next to it, I typed this:

="alltowavcmd "&CHAR(34)&A1&CHAR(34)&" -Otest.wav -S44100 -B16 >> errors.txt"

This command contained the following parts:

  • We are running the AlltowavCmd process, not DIR or some other command process.
  • We are applying the -O, -S, and other command parameters discussed above.
  • We are applying this process, with these parameters, to the path and file name specified in cell A1, which happens to be the Hall & Oates information presented above.
  • & means “combine the preceding part of this command with the next part.”
  • CHAR(34) means “insert a quotation mark here.”
  • We are surrounding that reference to the Hall & Oates information with quotation marks because there are spaces in “Hall & Oates.”
  • Excel uses actual quotation marks (i.e., not CHAR(34)) to denote text portions of this command — to state the “alltowavcmd” part, for example, as distinct from the reference to cell A1, which needs no quotes.

The command would have been more complicated if I had wanted to specify a unique output filename instead of just Test.wav. In that case, I might have put this Alltowavcmd command in cell C1, using cell B1 to specify the new name that I wanted to give to the Hall & Oates MP3. In that case, after -O, the command would have had another set of & symbols and CHAR(34) instructions and quotation marks, surrounding a reference to cell B1.

Another complication would arise if the process being executed, unlike AlltowavCmd.exe, was not able to accept a path name as input, or if I wanted to remove the MP3s and replace them with WAVs. In those cases, I might have to use DIR to identify where the original MP3s were located, use that information in a spreadsheet to generate MOVE commands that would relocate them all (preferably after testing for duplicate filenames) to a single folder, run my conversion process on those collected files, and then reuse the DIR information to work up another set of commands to return the new WAVs to the places where the old MP3s had been. I had found the REVERSE function useful in such cases.

Creating the Batch File

Now that I had a working command in cell B1, I could copy it down the spreadsheet to automatically produce unique AlltowavCmd conversion commands for each MP3 on my computer. I copied all those commands from Excel and pasted them back into MP3list.txt. I renamed that file to be Tester.bat. I was using Notepad to view MP3list.txt and to create this new Tester.bat file. When saving, I made sure that Notepad was saving Tester.bat in I:\, where I had put a copy of AlltowavCmd.exe, and that it was being saved in ANSI encoding.

So now I had a vast number of AlltowavCmd commands in Tester.bat. Before turning them loose, to do God knows what, of course I made sure I had a backup of my MP3s. As another precaution, I saved Tester.bat again under another filename, X.bat, and deleted all but just a few sample lines. I was going to run X.bat to see what it did, before unleasing the full joy or disaster of Tester.bat.

I wanted to see X.bat perform, so I ran it from the command line rather than by double-clicking on it in Windows Explorer. I could have typed it manually at the command prompt instead of running the batch file containing the command. The command I issued at the prompt, to run X.bat, was very simple:


When I ran X.bat from the command prompt in that way, it was slightly altered. What I saw next on the screen looked like this:

I:\>alltowavcmd “I:\Folder XYZ\Subfolder A\Hall & Oates–Adult Education.mp3” -Otest.wav -S44100
-B16 1>>errors.txt

There were a couple of things to notice about that:

  • This screen output was not the same as the error messages (if any) that might have been captured in Errors.txt. I would be getting to Errors.txt in a moment. This was just the command window’s verbose report of what it was doing. It was possible to shut off some of that chatter by adding an ECHO OFF command to a batch file, but in this case I wanted to see the nasty details.
  • The command interpreter added the number 1 before the “>> errors.txt” part of the command. That was different from the 2>> option, but those details were not important here.
  • Note that CHAR(34) in the Excel command did result in quotation marks where needed in the batch file command.

Running my little x.bat test program produced an error message inside the Errors.txt file: “Output path does not be found: test.wav.” It seemed that AlltowavCmd wanted me to specify not only the output filename (test.wav) but also its directory. So after experimenting with X.bat, I used Notepad’s search and replace function to alter the commands in Tester.bat so that they would refer to “-OI:\Test.wav” instead of just “-Otest.wav.” That fixed it: when I re-ran Tester.bat, Errors.txt now contained this:

Command: alltowavcmd  alltowavcmd “I:\Folder XYZ\Subfolder A\Hall & Oates–Adult Education.mp3” -OI:\test.wav -S44100 -B16

Set wav bit to: 16
Set channel to: Default
Set samplerate to: 44100
Set encoding to: wtPCM
Is converted, please wait…
Save to: I:\test.wav [Success]

Everything seemed to be working as expected. So I went ahead and ran the full Tester.bat.

Running the Batch File; Results

It took quite a while to process a large number of MP3s. When it was done, I opened Errors.txt. I made a number of transformations in Microsoft Word and Excel to produce a list of files that I could examine. In Word, one thing to know was that ^p (less frequently ^l) is the character indicating a link break. So it was possible to search the output to eliminate some of the extraneous lines shown above (e.g., “Set wav bit to: 16”). For the most part, I wound up with output that put the file path and name and the word [Success] on the same line. So for most files, I had one line per file.

I saved that as a text file and imported it into Excel. There, I had to do some more cleaning, because there were instances where a file’s output still spanned two or more lines. I put an Index column at the left to number the rows sequentially. That way, I could put them in order after my further search and replace processes created a few more blank rows, which I then isolated for deletion by sorting on the filename. I used FIND commands to identify the rows that did not have “Success” in them (e.g., =IF(ISERROR(FIND(“[Success]”,A1)),”x”,””), and created more columns to identify the reasons for failure when there was no success. That yielded the conclusion that AlltowavCmd had generated four basic types of output:

  • “Success”: AlltowavCmd felt that it had converted the MP3 to WAV without problems.
  • “The input file is invalid”: about 0.6% of MP3s generated this error.
  • “Input file does not found”: these errors, occurring with a very small percentage of MP3s, may have resulted from my activities of moving files around during the long hours when the batch file was running.
  • “Type must be”: these were of two kinds: either “Channel type must be 0,1,2” or “Encoding type must be 1,2.” It was not immediately clear whether these messages signaled something wrong with the input MP3 or, rather, with the command I had tried to issue. About 1.1% of MP3s generated these “type” errors.

I decided to repeat the process on the MP3s that had generated the last two kinds of errors (i.e., “not found” and “type”), just to make sure those results were not due to interference or error on my part. So I used DIR to work up a list of files, as above, and once again used Excel to create a new batch file full of AlltowavCmd commands, same as before.

I ran that batch file and compared its results against the previous round. That unchanged command worked on only a small number of files, possibly the ones that AlltowavCmd had not found at the prescribed location on the first round. For the others, I realized that perhaps I just had to alter my AlltowavCmd command to specify a channel and encoding type.

Going back to the Boxoft file called Command line.txt, I saw that the default channel type was 0. So it seemed I was already complying with the “type must be” error just described. Maybe the error message meant that I had to specify something. I looked at the files generating that error. Some were definitely music MP3s, so I decided to try a stereo channel setting: -C2. I hadn’t been specifying encoding type either. The default was 1 (RAW PCM). In case that wasn’t appropriate for these particular files, I tried -E2 (DVI IMA ADPCM). I added those changes (-C2 and -E2) to the existing batch file via search and replace in Notepad, wondering whether this change in the commands that had already produced success for some of these MP3 conversions would now produce failure. (Note that I had to delete Errors.txt to avoid adding new results to the previous results.)

This time around, the batch file seemed to run more slowly. I guess that was to be expected, as Test.wav was now being produced in each case as a stereo file, presumably of about twice the size. But the results were worth it. There were no recurrences of any “type must be” errors in the Errors.txt output. Every file was now reported as either achieving “Success” in the AlltowavCmd process, except those for which I received “The input file is invalid” error. Hence, it appeared that perhaps I should have begun with the command I had used this time around, in the following form:

alltowavcmd "[input path and file name]" -OJ:\Test.wav -S44100 -C2 -E2 -B16 >> Errors.txt

Those results narrowed the focus down to those allegedly invalid MP3s. Their number had not changed from the first try. In that sense, the revised AlltowavCmd command just shown was useful only to verify that AlltowavCmd felt that it could convert all except the invalid MP3s.

Now I wondered whether AlltowavCmd was correct in its conclusion that a large number of my MP3s were invalid. Here again, the spreadsheet already created would be handy for creating a batch file to test that. I was using IrfanView as my audio player, and I had a shortcut to i_view32.exe (i.e., IrfanView’s executable, discovered by starting IrfanView > going into Task Manager (Ctrl-Alt-Del) > Applications tab > right-click on IrfanView > Go To Process) in C:\Windows, so I could use this Excel formula for each allegedly invalid MP3 (whose path and file names were identified in cells A3, A4, etc.):

="start IrfanView "&CHAR(34)&A3&CHAR(34)&" & pause & taskkill /f /im i_view32.exe"

The & characters would execute one part of the command at a time: first start IrfanView, then pause, then kill IrfanView, so that the next command could restart it, focused on a different MP3. So the batch file would play the problematic MP3s one at a time, and I would not have to be mousing all over the screen to move ahead to the next MP3. Note that an ampersand in a file name would have to be escaped by preceding it with a caret (^) before developing this Excel command with its ampersands. So, within this list, a reference to a file named “R&D” would have to be changed to refer instead to “R^&D.” (The file itself would not be renamed; these remarks refer just to the name appearing in my list of files.)

For each file in the list, that Excel formula would result in a command like this:

start IrfanView "[MP3 path and file name]" & pause & taskkill /f /im i_view32.exe

As I quickly played through those MP3s — just listening for the start of the audio and then moving on to the next file — I found that most of the files that AlltowavCmd had marked as invalid were playable. Possibly the list of invalid files would have been reduced if I had experimented with other settings in the conversion commands.

Having completed that, it was time to return to the separate post in which I was proceeding with my comparison of methods of verifying MP3s.

This entry was posted in Uncategorized and tagged , , , , , , , , , . Bookmark the permalink.

2 Responses to Tutorial: Using Excel to Apply Batch Commands to Scattered Files (Testing MP3s)

  1. Matthew says:

    Hey guys not sure if i am in the right area. But i would like computer information from batch file be exported to a excel sheet through the batch. so currently i have the below which collects information about the computer and would like this info exported to a Excel spread sheet. i need it so when its exported it will show the information in each column and rows. so i can run this on multiple computers and it saves it to the spreadsheet . any help would be appreciates

    @echo off
    if %os%==Windows_NT goto WINNT
    goto NOCON

    echo .Using a Windows NT based system
    echo ..%computername%

    REM set variables
    set system=
    set manufacturer=
    set model=
    set serialnumber=
    set osname=
    set sp=
    set “volume=C:”
    set totalMem=
    set availableMem=
    set usedMem=

    echo Getting data [Computer: %computername%]…
    echo Please Wait….

    REM Get Computer Name
    FOR /F “tokens=2 delims=’='” %%A in (‘wmic OS Get csname /value’) do SET system=%%A

    REM Get Computer Manufacturer
    FOR /F “tokens=2 delims=’='” %%A in (‘wmic ComputerSystem Get Manufacturer /value’) do SET manufacturer=%%A

    REM Get Computer Model
    FOR /F “tokens=2 delims=’='” %%A in (‘wmic ComputerSystem Get Model /value’) do SET model=%%A

    REM Get Computer Serial Number
    FOR /F “tokens=2 delims=’='” %%A in (‘wmic Bios Get SerialNumber /value’) do SET serialnumber=%%A

    REM Get Computer OS
    FOR /F “tokens=2 delims=’='” %%A in (‘wmic os get Name /value’) do SET osname=%%A
    FOR /F “tokens=1 delims=’|'” %%A in (“%osname%”) do SET osname=%%A

    REM Get Computer OS SP
    FOR /F “tokens=2 delims=’='” %%A in (‘wmic os get ServicePackMajorVersion /value’) do SET sp=%%A

    REM Get Memory
    FOR /F “tokens=4” %%a in (‘systeminfo ^| findstr Physical’) do if defined totalMem (set availableMem=%%a) else (set totalMem=%%a)
    set totalMem=%totalMem:,=%
    set availableMem=%availableMem:,=%
    set /a usedMem=totalMem-availableMem

    FOR /f “tokens=1*delims=:” %%i IN (‘fsutil volume diskfree %volume%’) DO (
    SET “diskfree=!disktotal!”
    SET “disktotal=!diskavail!”
    SET “diskavail=%%j”
    FOR /f “tokens=1,2” %%i IN (“%disktotal% %diskavail%”) DO SET “disktotal=%%i”& SET “diskavail=%%j”

    echo done!

    echo ——————————————–
    echo System Name: %system%
    echo Manufacturer: %manufacturer%
    echo Model: %model%
    echo Serial Number: %serialnumber%
    echo Operating System: %osname%
    echo C:\ Total: %disktotal:~0,-9% GB
    echo C:\ Avail: %diskavail:~0,-9% GB
    echo Total Memory: %totalMem%
    echo Used Memory: %usedMem%
    echo Computer Processor: %processor_architecture%
    echo Service Pack: %sp%
    echo ——————————————–

    REM Generate file
    SET file=”%~dp0%computername%.txt”
    echo ——————————————– >> %file%
    echo Details For: %system% >> %file%
    echo Manufacturer: %manufacturer% >> %file%
    echo Model: %model% >> %file%
    echo Serial Number: %serialnumber% >> %file%
    echo Operating System: %osname% >> %file%
    echo C:\ Total: %disktotal:~0,-9% GB >> %file%
    echo C:\ Avail: %diskavail:~0,-9% GB >> %file%
    echo Total Memory: %totalMem% >> %file%
    echo Used Memory: %usedMem% >> %file%
    echo Computer Processor: %processor_architecture% >> %file%
    echo Service Pack: %sp% >> %file%
    echo ——————————————– >> %file%

    REM request user to push any key to continue

    goto END

    echo Error…Invalid Operating System…
    echo Error…No actions were made…
    goto END


    • Ray Woodcock says:

      Matt — I’d suggest posting your question on a forum with lots of DOS/batch experts. Since most of your batch file consists of ECHO commands, I’d suggest boiling it down and focusing their attention on the specific part that’s not working for you. Sounds like you want to know how to automate the importing of batch output into Excel. I think you can use a batch command to open a specific Excel file. I’m guessing your solution will be to combine that sort of batch command with an autorun macro in Excel that will open the text file containing the output of your batch file (above). Good luck!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.