Copy csv with new column that adds 2 existing columns

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
catalyph
Posts: 6
Joined: 12 Jun 2018 09:09

Copy csv with new column that adds 2 existing columns

#1 Post by catalyph » 12 Jun 2018 09:21

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)
Last edited by catalyph on 12 Jun 2018 12:41, edited 1 time in total.

Squashman
Expert
Posts: 4465
Joined: 23 Dec 2011 13:59

Re: Copt csv with new column that adds 2 existing columns

#2 Post by Squashman » 12 Jun 2018 09:27

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
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: Copt csv with new column that adds 2 existing columns

#3 Post by Aacini » 12 Jun 2018 10:13

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! :shock: ).

Antonio

catalyph
Posts: 6
Joined: 12 Jun 2018 09:09

Re: Copt csv with new column that adds 2 existing columns

#4 Post by catalyph » 12 Jun 2018 12:01

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"
Last edited by Squashman on 12 Jun 2018 12:44, edited 1 time in total.
Reason: MOD EDIT: Pleas use [code][/code] tags to surround your code.

catalyph
Posts: 6
Joined: 12 Jun 2018 09:09

Re: Copt csv with new column that adds 2 existing columns

#5 Post by catalyph » 12 Jun 2018 12:30

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"
Aacini wrote:
12 Jun 2018 10:13

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! :shock: ).

Antonio
Last edited by Squashman on 12 Jun 2018 12:44, edited 1 time in total.
Reason: MOD EDIT: Pleas use [code][/code] tags to surround your code.

catalyph
Posts: 6
Joined: 12 Jun 2018 09:09

Re: Copt csv with new column that adds 2 existing columns

#6 Post by catalyph » 12 Jun 2018 13:25

Squashman wrote:
12 Jun 2018 09:27
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
Expert
Posts: 1885
Joined: 06 Dec 2011 22:15
Location: México City, México
Contact:

Re: Copy csv with new column that adds 2 existing columns

#7 Post by Aacini » 12 Jun 2018 14:08

@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
Posts: 6
Joined: 12 Jun 2018 09:09

Re: Copy csv with new column that adds 2 existing columns

#8 Post by catalyph » 13 Jun 2018 07:57

Aacini wrote:
12 Jun 2018 14:08
@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
Posts: 6
Joined: 12 Jun 2018 09:09

Re: Copy csv with new column that adds 2 existing columns

#9 Post by catalyph » 13 Jun 2018 09:57

Aacini wrote:
12 Jun 2018 14:08
@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.

Post Reply