Can't get my Batch Script to work due to incorrect coding or bugs

Question

I'm trying to manipulate a simple data series set (input data) as listed below to get an output csv file as shown below. I have been pulling my hair out trying to work out where the bugs are and how to code this script, but could not work it out.

I can't get my simple batch script to work, when I run the batch program it does nothing. Here is the code I'm using, the input data I'm using, and the output data that I need to obtain:

@echo off
setlocal enabledelayedexpansion
FOR /F "tokens=1-7* delims=," %%A IN (eurusd1.csv) DO (
    set sum1 = %%~D - %%~E
    set /a result=(sum!*1000000)+1
    echo %%~A,%%~B,%%~C,%%~D,%%~E,%%~F,!result!
) >> output.csv

Input data:

2020.12.24,16:44,1.21906,1.21909,1.21897,1.21905,1
2020.12.24,16:45,1.21904,1.21910,1.21889,1.21906,1
2020.12.24,16:46,1.21905,1.21913,1.21899,1.21909,1

Want output data to look like this:

2020.12.24,16:44,1.21906,1.21909,1.21897,1.21905,120
2020.12.24,16:45,1.21904,1.21910,1.21889,1.21906,211
2020.12.24,16:46,1.21905,1.21913,1.21899,1.21909,141

Answer 1

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION
rem The following settings for the source directory, destination directory, target directory,
rem batch directory, filenames, output filename and temporary filename [if shown] are names
rem that I use for testing and deliberately include names which include spaces to make sure
rem that the process works using such names. These will need to be changed to suit your situation.
SET "sourcedir=u:your files"
SET "destdir=u:your results"
SET "filename1=%sourcedir%q65529783.csv"
SET "outfile=%destdir%outfile.csv"

(
FOR /f "usebackqtokens=1-7delims=," %%a IN ("%filename1%") DO (
 FOR /f "tokens=1-4delims=." %%s IN ("%%d.%%e") DO SET /a value=(%%s*100000^)+1%%t-(%%u*100000^)-1%%v&SET /a value=10*value+1
 ECHO %%a,%%b,%%c,%%d,%%e,%%f,!value!
)
)>"%outfile%"

GOTO :EOF

I used a file named q65529783.csv containing your data for my testing.

Produces the file defined as %outfile%

The usebackq option is only required because I chose to add quotes around the source filename.

First, read and tokenise using , - 7 columns, so tokens 1-7, although the last is unused.

Next, re-tokenise the string %%d.%%e using . as a delimiter. Note the dot between %%d and %%e.

Now set our value. Since there is no indication of the value-ranges involved (I have assumed that they are all positive) then we convert %%d to a six-digit number by multiplying %%s by 100000 and adding 1%%t to that. This takes care of the situation where %%t contains a leading 0 which would otherwise be treated as octal.

Rinse and repeat for %%e and perform the subtraction, noting that the 1 preceding %%v balances that preceding %%t. The carets are required so that cmd sees the ) as part of the set, not the for.

Since the result is now 10000* the floating-point difference, multiply by 10 and add 1, as anticipated in the original code.

and output the result.

Since you don't explain why the first result of the manipulation is 120, which seems illogical given the formula, I'll assume that this is an error and it should be 121.