Actually, it's impossible to do it without calling in the troops so to speak but there is a very neat way of adding floating point numbers in a batch application. Read on.
Recently a client of ours was exporting software from one of their desktop applications and putting it into another desktop application. The format of the file was a flat CSV (comma separated values) file with 6 columns.
Both of the pieces of software, the source and the destination are expensive, robust pieces of software but there was an inconsistency with what they were able to export and what they had to import. Queue the trusty windows batch file!
In the first application, a particular concept, in this case it was the number of hours a particular employee worked, was represented by 2 numeric columns but in the destination application, it needed to only be represented by 1 numeric column.
Most batch programmers are familiar with the ability to add integers (whole numbers) in batch files with the set /a
command, e.g.
set /a myCounter=myCounter+1
However, if you try an ol' set /a myCounter=myCounter+1.5
, you'll get a missing operator error. So, in our CSV file, we want to create an extra column which is the sum of the two relevant floating point columns. Here's the code:
@echo off
cscript.exe //H:cscript
setLocal EnableDelayedExpansion
The second line above sets the windows scripting host. We're actually going to perform the calculation using a small bit of VB Script. We then set EnableDelayedExpansion so that the batch file will actually work. This property is a bit beyond the scope of this article but it's root is in loops and their content being treated as a single statement.
if exist "eval.vbs" goto script
@echo Set objArgs = WScript.Arguments>"eval.vbs"
@echo wscript.echo eval(objArgs(0))>>"eval.vbs"
:script
All these lines of code do is create an external file called "eval.vbs" which uses the eval function in VB to evaluate the argument that is passed to it. One of the uses of this is that we can pass in an arithmetic expression which contains floating point numbers, e.g. 7.2+3.2 and it will return 10.4 for us.
Our script in questions takes a filename as a parameter, this is available in the script using the variable %1. As we don't want to overwrite our original file, we want to create a new file with something appended to distinguish it from the original. The following lines take care of this:
for /f "tokens=1-3* delims=." %%a in (%1) do (
set newfile=%%a_after_script.%%b
)
This tokenizes the input filename using the . character so if we have a file called "input.csv", we'll have 2 tokens, "input" and "csv". This then produces a new string which contains "input_after_script.csv". This is the file that we will write to.
Next up, we delete the output file if it already exists. It's enclosed in quotes in case the filename has spaces in it. This is likely if for example you drag a file from your desktop onto the batch file.
if exist "%newfile%" del "%newfile%"
Here's where the work is done:
set linecounter=0
for /f "tokens=1-7* delims=," %%a in ('type %1') do (
for /f %%i in ('eval //nologo %%d+%%f') do (
set var=%%i
)
echo %%a,%%b,%%c,%%d,%%e,%%f,!var! >> "%newfile%"
set /a linecounter=linecounter+1
)
So we create a variable called linecounter
which will just tell us how many lines we've processed at the end. Then we set up a for loop which will divide up our columns based on the comma, it being a comma separated values file. The in
statement of the for loop is a little trick which is covered in a different article on Celtic Productions. It is used, again, to deal with spaces in the filename. The outer for loop loops through each line in the file. The inner for loop then performs the arithmetic.
You can see that we are adding columns 4 and 6 together, i.e. our first variable is assigned to %%a, our second to %%b &c. The nologo switch passed to our VB script is so that the windows scripting host header isn't printed along with our result. The inner for loop simply creates another variable, imaginatively called var
.
Then we echo all of our tokens as well as our variable !var! to our new filename thus creating an extra column on each line. We then increment the linecounter
variable and outside of our for loops we just echo a status message.
echo Processed %linecounter% line(s)
That's it, floating point arithmetic is only one of a few different mathematical functions you can use this script for. For example, instead of passing %%d+%%f
, you could pass in %%d^%%f
to raise %%d to the power of %%f. In fact, anything that you can eval in VB Script, you can pass in to that "eval.vbs" file, just make sure it's valid! Here's the complete code:
@echo off
cscript.exe //H:cscript
setLocal EnableDelayedExpansion
if exist "eval.vbs" goto script
@echo Set objArgs = WScript.Arguments>"eval.vbs"
@echo wscript.echo eval(objArgs(0))>>"eval.vbs"
:script
for /f "tokens=1-3* delims=." %%a in (%1) do (
set newfile=%%a_combined.%%b
)
if exist "%newfile%" del "%newfile%"
set linecounter=0
for /f "tokens=1-7* delims=," %%a in ('type %1') do (
for /f %%i in ('eval //nologo %%d+%%f') do (
set var=%%i
)
echo %%a,%%b,%%c,%%d,%%e,%%f,!var! >> "%newfile%"
set /a linecounter=linecounter+1
)
echo Processed %linecounter% line(s)
As with all articles on Celtic Productions, this article is protected by international copyright laws. It may be linked to (we are of course most grateful of links to our articles), however, it may never be reproduced without the prior express permission of its owners, Celtic Productions. The code contained therein of course can be used freely.