-1

I have been trying to read a comma delimited csv file and total a value from each record using the code below that I have put together from various searches:

@echo off
setlocal EnableDelayedExpansion

set "grandtotal=0"

rem Process the file:
call :ProcessFile < %1
exit /B

:ProcessFile
set /P line=
set "total=0"

:nextLine
set line=:EOF
set /P line=
if "!line!" == ":EOF" goto :EOF
set i=0

for %%e in (%line%) do (
    set /A i+=1
    for %%i in (!i!) do (
        if %%i==4 (
            echo "value of e - " %%e
            set /A htot=+%%e
            echo "value of htot - " %htot%
        )
    )
)

goto nextLine
exit /B

The test csv file contains 4 records with the value in the 4th of 7 fields. I now have a few problems:

  1. The first record is being skipped. No idea as to why. The data in the file should be reading the values of 187.55, 109.48, 612.82, 1028.81 for a total of 1,938.66.

  2. The totalling of the record is not occurring. I am getting a Missing operator. error on the Set /A htot=+%%e

  3. Although I am getting that error, the variable htot does seem to get populated, but with the previously read value.

This is the screen output when I run the batch file:

C:\DOS Stuff>try6 testf.csv
"value of e - " 109.48
Missing operator.
"value of htot - "

"value of e - " 612.82
Missing operator.
"value of htot - " 109
 
"value of e - " 1028.81
Missing operator.
"value of htot - " 612
Improve this question
7
  • You are using delayed expansion. Good. But you forgot to delay %htot%. Oh - there is another big problem: set /a math is limited to INT32 (purely integer, no floating point) It can be done in batch, but PowerShell (or any other language) is a much better solution. Commented Jan 31 at 19:44
  • oh, and you add to a variable with set /a +=xxx, not set /a =+xxx Commented Jan 31 at 19:47
  • oh, and of course a lot of the commands are invalid in DOS. Are you working on the Windows command line? Then please adapt your tags accordingly. Commented Jan 31 at 19:54
  • 1
    Decimal math is one of the very few times that I'll recommend using Powershell for a question with the [batch-file] tag. Commented Jan 31 at 22:11
  • 1
    setlocal enabledelayedexpansion only enables delayed expansion. You still have to use it (!htot! instead of %htot%) Commented Feb 1 at 9:14

1 Answer 1

Reset to default
1

Sadly, you haven't given us an obfuscated example of your data file contents, so I made a sample file containing this data:

xyz,"opq,yyy","rsj",187.55,whatever,junk
xyz,"opq,yyy","rsj",109.48,whatever,junk
xyz,"opq,yyy","rsj",612.82,whatever,junk
xyz,"opq,yyy","rsj",1028.81,whatever,junk

The code I devised was

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION
SET /a characteristic=0,mantissa=0

FOR /f "usebackqdelims=" %%e IN ("%~1") DO (
 SET /a field=0
 FOR %%o IN (%%e) DO (
  SET /a field+=1
  IF !field!==4 FOR /f "tokens=1,2delims=." %%b IN ("%%o") DO SET /a characteristic+=%%b,mantissa=mantissa+1%%c-100
 )
)
SET /a characteristic+=mantissa/100,mantissa=(mantissa %% 100)+100
SET "result=%characteristic%.%mantissa:~-2%"

ECHO %result%

GOTO :EOF

So - to explain:

Note that it is valid to separate value assignments with a comma in set /a.

Read the data file, line-by-line into %%e. My actual command for running this code was q79403729 "u:\your files\q79403729.txt". The ~ strips the quotes from %1, the result is then quoted (so it doesn't matter whether %1 was quoted or not) and the option usebackq used to tell for that the quoted string is a filename, not a literal. (for /? for documentation)

Then use %%o to process %%e as a simple list

Pick the fourth field from the list and use the delimiter . to tokenise %%o, quoted, to be used as a literal into %%b and %%c and add these to the accumulators characteristic and mantissa. Since %%c may contain a leading 0, then that would cause it to be interpreted as octal, raising errors for .08 and .09, then the standard solution is to prefix %%c with 1 and subtract 100.

Finally, adjust for mantissa > 100, ensuring mantissa has 100 added to (its original value mod 100) so that it will be 3 characters long, then the result is bolted together using characteristic, a dot, and the last 2 digits of mantissa

Note that this would need to be adjusted if the field in question does not have a . or contains but one digit after the . and also if a field may be absent from the source data.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This worked perfectly...thank you. And an even bigger thank you for the explanation of the process.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.