Copy csv with new column that adds 2 existing columns

[catalyph]

I have a .csv file

Prod,Name,Datetime,Type,MemFree,MemAvailable,MemTotal
PRDAAA,HJGYASW,6/11/2018 23:06,WIN,4319264768,4875202560,8006983680
PRDBBB,UYBCLAS,6/11/2018 23:11,WIN,4313509888,4870922240,8006983680


I need to move this csv to a new location and add 2 new columns with new headers of "MemUsedA" and "MemUsedF" that are the subtractions of other columns (MemTotal - MemAvailable) and (MemTotal - MemFree)

[Squashman]

Your totals are larger than a 32 bit Integer which is a limitation of the SET /A command which does expression evaluation. Regardless of that, your task can still be accomplished, just in a different way.

[Aacini]

Code: Select all

@echo off
setlocal EnableDelayedExpansion

set "header="
(for /F "tokens=1-7 delims=," %%a in (input.csv) do (
   if not defined header (
      set "header=1"
      echo %%a,%%b,%%c,%%d,%%e,%%f,%%g,MemUsedA,MemUsedF
   ) else (
      set "MemFree=0000%%e"  &  set "MemAvailable=0000%%f"  &  set "MemTotal=0000%%g"
      set /A "MemUsedAHigh=1!MemTotal:~-10,5!-1!MemAvailable:~-10,5!, MemUsedALow=2!MemTotal:~-5!-1!MemAvailable:~-5!"
      set /A "MemUsedFHigh=1!MemTotal:~-10,5!-1!MemFree:~-10,5!, MemUsedFLow=2!MemTotal:~-5!-1!MemFree:~-5!"
      echo %%a,%%b,%%c,%%d,%%e,%%f,%%g,!MemUsedAHigh!!MemUsedALow:~-5!,!MemUsedFHigh!!MemUsedFLow:~-5!
   )
)) > output.csv

This method fail if any number have less than 6 digits (and, of course, if there are more memory free or available than the total! ).

Antonio

[catalyph]

OK this worked by itself but not when I place it in my current bat.
I have a portion that sets the newest file in the directory to the variable %NewestFile%

Code: Select all

@echo off
setlocal EnableDelayedExpansion
FOR /F "delims=" %%I IN ('DIR "ProData\*.csv" /B /O:D') DO SET NewestFile=%%I

set "header="
(for /F "tokens=1-7 delims=," %%a in ( DIR "ProData\%NewestFile%") do ( if not defined header ( set "header=1"
      echo %%a,%%b,%%c,%%d,%%e,%%f,%%g,MemUsedA,MemUsedF
   ) else (
      set "MemFree=0000%%e"  &  set "MemAvailable=0000%%f"  &  set "MemTotal=0000%%g"
      set /A "MemUsedAHigh=1!MemTotal:~-10,5!-1!MemAvailable:~-10,5!, MemUsedALow=2!MemTotal:~-5!-1!MemAvailable:~-5!"
      set /A "MemUsedFHigh=1!MemTotal:~-10,5!-1!MemFree:~-10,5!, MemUsedFLow=2!MemTotal:~-5!-1!MemFree:~-5!"
      echo %%a,%%b,%%c,%%d,%%e,%%f,%%g,!MemUsedAHigh!!MemUsedALow:~-5!,!MemUsedFHigh!!MemUsedFLow:~-5!
   )
)) > ProData\output.csv


COPY "ProData\output.csv" "%ProHome%\user_cfg\inputdata\output.csv"

[catalyph]

Im having trouble implementing this into my batch file.

I have a line that sets the latest csv file to the var NewestFile, when I use Newest file as the file it des not work

Code: Select all

@echo off
setlocal EnableDelayedExpansion
FOR /F "delims=" %%I IN ('DIR "ProData\*.csv" /B /O:D') DO SET NewestFile=%%I

set "header="
(for /F "tokens=1-7 delims=," %%a in ( DIR "ProData\%NewestFile%") do ( if not defined header ( set "header=1"
      echo %%a,%%b,%%c,%%d,%%e,%%f,%%g,MemUsedA,MemUsedF
   ) else (
      set "MemFree=0000%%e"  &  set "MemAvailable=0000%%f"  &  set "MemTotal=0000%%g"
      set /A "MemUsedAHigh=1!MemTotal:~-10,5!-1!MemAvailable:~-10,5!, MemUsedALow=2!MemTotal:~-5!-1!MemAvailable:~-5!"
      set /A "MemUsedFHigh=1!MemTotal:~-10,5!-1!MemFree:~-10,5!, MemUsedFLow=2!MemTotal:~-5!-1!MemFree:~-5!"
      echo %%a,%%b,%%c,%%d,%%e,%%f,%%g,!MemUsedAHigh!!MemUsedALow:~-5!,!MemUsedFHigh!!MemUsedFLow:~-5!
   )
)) > ProData\output.csv
COPY "ProData\output.csv" "%PRO_HOME%\inputdata\output.csv"

Code: Select all

@echo off
setlocal EnableDelayedExpansion

set "header="
(for /F "tokens=1-7 delims=," %%a in (input.csv) do (
   if not defined header (
      set "header=1"
      echo %%a,%%b,%%c,%%d,%%e,%%f,%%g,MemUsedA,MemUsedF
   ) else (
      set "MemFree=0000%%e"  &  set "MemAvailable=0000%%f"  &  set "MemTotal=0000%%g"
      set /A "MemUsedAHigh=1!MemTotal:~-10,5!-1!MemAvailable:~-10,5!, MemUsedALow=2!MemTotal:~-5!-1!MemAvailable:~-5!"
      set /A "MemUsedFHigh=1!MemTotal:~-10,5!-1!MemFree:~-10,5!, MemUsedFLow=2!MemTotal:~-5!-1!MemFree:~-5!"
      echo %%a,%%b,%%c,%%d,%%e,%%f,%%g,!MemUsedAHigh!!MemUsedALow:~-5!,!MemUsedFHigh!!MemUsedFLow:~-5!
   )
)) > output.csv

This method fail if any number have less than 6 digits (and, of course, if there are more memory free or available than the total! ).

Antonio

[catalyph]

Your totals are larger than a 32 bit Integer which is a limitation of the SET /A command which does expression evaluation. Regardless of that, your task can still be accomplished, just in a different way.

How so ? Still using bat?

[Aacini]

@catalyph, I have a question for you.

In the new line that you added:

Code: Select all

FOR /F "delims=" %%I IN ('DIR "ProData\*.csv" /B /O:D') DO SET NewestFile=%%I
                         ^                           ^

... you inserted apostrophes (marked above with carets) to enclose the whole DIR command. Why?

[catalyph]

@catalyph, I have a question for you.

In the new line that you added:

Code: Select all

FOR /F "delims=" %%I IN ('DIR "ProData\*.csv" /B /O:D') DO SET NewestFile=%%I
                         ^                           ^

... you inserted apostrophes (marked above with carets) to enclose the whole DIR command. Why?

I'm not the creator of the command, I have little knowledge beyond basic of bat scripting.
This was how the .bat was provided to me.

Code: Select all

FOR /F "delims=" %%I IN ('DIR "ProData\*.csv" /B /O:D') DO SET NewestFile=%%I
COPY "ProData\%NewestFile%" "%PRO_HOME%\inputdata\output.csv"

[catalyph]

@catalyph, I have a question for you.

In the new line that you added:

Code: Select all

FOR /F "delims=" %%I IN ('DIR "ProData\*.csv" /B /O:D') DO SET NewestFile=%%I
                         ^                           ^

... you inserted apostrophes (marked above with carets) to enclose the whole DIR command. Why?

Without those, the .bat does not work it seems.