0

I need to do a simple multiplication in a CSV sheet from a batch file on a Windows 7 machine.

Assume this is my CSV sheet:

enter image description here

COL_A, COL_B, COL_C, COL_D
23,3,0.667,6
24,4,0,7
25,2,3.55,7
26,3,,8

I need this output:

enter image description here

COL_A, COL_B, COL_C, COL_D,COL_E
23,3,0.667,6,1.0
24,4,0,7,0
25,2,3.55,7,5.3
26,3,,8,0

Formula for Col_E:

enter image description here

COL_E = COL_C * 1.5
Ross Ridge
  • 38,414
  • 7
  • 81
  • 112
Malini Shanmugam
  • 13
  • 6
  • Why does it have to be in batch? Batch can't manipulate Excel files (other than CSV, which your sheet is clearly not). Why not just use VBA or inline formulas like you already used? – SomethingDark Nov 14 '17 at 11:04
  • @SomethingDark: ok i will save the file as CSV instead of EXCEL. is it possible to manipulate it in CSV file – Malini Shanmugam Nov 14 '17 at 11:09
  • @SomethingDark: i am converting a .blg in to CSV in a batch file. i need to perform this calculation while doing this conversion itself. – Malini Shanmugam Nov 14 '17 at 11:11
  • You haven't answered my question. Why does it have to be in batch? – SomethingDark Nov 14 '17 at 11:14
  • @SomethingDark:I need to do the relog for atleast 100 blg files , so I have created batch file for this conversion. we are getting the files from different server's so I cant merge it in to one file. after that I will open the csv file and will perform this manual calculation all the time. so I thought after doing the conversion process whether we can do this programmatically in the same batch file itself.. – Malini Shanmugam Nov 14 '17 at 11:20
  • Unfortunately I cannot help as everything is captured in images and I cannot see them. – Gerhard Nov 14 '17 at 11:35
  • @GerhardBarnard: i could able to see the images in this question – navbingo Nov 14 '17 at 11:53
  • Not everyone is in an open network, so for me images are unavailable. – Gerhard Nov 14 '17 at 11:53
  • @GerhardBarnard: ok , I will add it in the question itself as per ur suggestion. I will separate the column values by comma – Malini Shanmugam Nov 14 '17 at 11:56
  • @GerhardBarnard: I have edited the question as you requested ;) – Malini Shanmugam Nov 14 '17 at 12:01
  • yes it is possible to do that with csv, but it is not going to be easy. – Gerhard Nov 14 '17 at 12:03
  • @GerhardBarnard: Waiting for a positive reply from you – Malini Shanmugam Nov 14 '17 at 12:12

1 Answers1

0
@echo off
setlocal enabledelayedexpansion
FOR /F "tokens=1-18* delims=," %%A IN (mycsv.csv) DO (
set sum1="%%~C"
set /a cole=!sum1! * 2
echo %%~A,%%~B,%%~C,%%~D,!cole!
) >> output.csv

EDIT, fixed the pipe to support all rows and edited for the output to work, there is going to be rounding off issue though as batch does not generally support fractions, so will need to relook this.

Gerhard
  • 22,678
  • 7
  • 27
  • 43
  • Hi thanks for the quick response.. when I run this program I am getting the following error "Missing Operand" – Malini Shanmugam Nov 14 '17 at 12:48
  • the output.csv file is generating after the "Missing Operand" error in command prompt. when I open the file, the header row is not there. only one row is present from COL_A to COL_D. I need the header row in the output csv file. – Malini Shanmugam Nov 14 '17 at 12:58
  • Fifth line should read `set /a cole=!sum1!*2`. No quotes, use delayed expansion. You can also skip the `!`s altogether because the `/a` flag is weird like that. You're also going to want to change `%cole%` to `!cole!`. – SomethingDark Nov 14 '17 at 13:49
  • wrote this on my phone, so will fix few things now. However, just realised that this will not work as intended. in batch doing 2 * 1,5 will return 2, it is rounded off. – Gerhard Nov 14 '17 at 13:59
  • after editing , I got the below output in the output.csv file 26,3,,8,,,,0. only the last record is printing rest all other rows are deleted including header. the value '0' is printed after 3 columns. – Malini Shanmugam Nov 14 '17 at 14:08
  • you can try now, but your calculation is still going to be wrong as mentioned that batch by default rounds off the decimals. – Gerhard Nov 14 '17 at 14:09
  • @GerhardBarnard: I wont get decimal value after multiplying. so no problem with rounding off issue – Malini Shanmugam Nov 14 '17 at 14:14
  • but you are multiplying by `1,5` being a decimal value on its own. – Gerhard Nov 14 '17 at 14:20
  • the value on col_C * 1.5 always an integer only. we would not get any decimal value on the result value – Malini Shanmugam Nov 14 '17 at 14:23
  • yes, but the problem in batch is that it rounds it to the lowest value. So `10 * 1,5 = 10` in batch – Gerhard Nov 14 '17 at 14:27
  • to do this properly, you would need to use something external, like cscript/wscript of something cooler like `perl` also see [this](https://www.computing.net/howtos/show/batch-script-floating-point-math/753.html) – Gerhard Nov 14 '17 at 14:29
  • but yes, the script as it is now will work for non decimal calculations like 3 * 2 etc. – Gerhard Nov 14 '17 at 14:29
  • now I am getting the header and all the columns as desired. but the multiplication is not happening . the values inside the brackets () is the desired value COL_A, COL_B, COL_C, COL_D,COL_E 23,3,0.667,6,0(1) 24,4,0,7,0(0) 25,2,3.55,7,3(5.3) 26,3,,8,0(0) – Malini Shanmugam Nov 14 '17 at 14:31
  • yes, because **does not support floating point arithmetic** have a look at [this answer](https://stackoverflow.com/questions/1503888/floating-point-division-in-a-dos-batch), it uses external script to do the functionality. So you could combine that, with this script and get the desired result. – Gerhard Nov 14 '17 at 14:35
  • In case if I have a blank value in col_c then that particular row in the output file col_d value is placed in col_c and col_d is null and the col_e multiplication is done on col_d value which is wrong – Malini Shanmugam Nov 14 '17 at 14:50
  • I checked with integers the multiplication is happening correctly.. thanks a lot for the quick responses.. only one issue as I mentioned in the above comment.. if col_c got null values – Malini Shanmugam Nov 14 '17 at 14:52
  • You could do a statement to check for emty columns. – Gerhard Nov 14 '17 at 16:24
  • I am new to this , could you please guide me on empty column statement too. Thanks in advance – Malini Shanmugam Nov 14 '17 at 16:36