CODEWITHSUNDEEP
batch-file
LearningCpp
LearningCpp

Reputation: 982

adding a new column to csv and populate the values alternatives

I am trying to write a batch file in windows which copies / appends a new column at the starting of CSV file . and then populates with values 0 and 1 alternately

For Example:

F1,F2,F3
1,2,3
1,2,3
2,3,4
3,4,5

Now I wish to add a new column at first and add values to them ex 

F0,F1,F2,F3
0,1,2,3
1,1,2,3
0,2,3,4
1,3,4,5

Just append 0 for all even row numbers and 1 for all odd rows

Below is the code that I have written, but that just adds 0 to all rows, but I want 0 and 1 alternately

@echo off > newfile.csv & setLocal enableDELAYedeXpansion
for /f "tokens=* delims= " %%a in (J_CAFE27032018_090325.csv) do (
>> newfile.csv echo a,%%a
)

A c equivalent would be having a for loop for all even and odd columns

for(i=0;i<n;i+2)
 {
   add 0
 }
for(i=0;i<n;i++)
 {
   add 0
 }

Would you please help me with the batch file equivalent to traverse each odd and even rows.

Upvotes: 0

Views: 1959

Answers (4)

aschipfl
aschipfl

Reputation: 34979

I would do it the following way -- given that no line of the input CSV file (data.csv) exceeds an overall length of 1021 characters/bytes:

@echo off
setlocal EnableExtensions DisableDelayedExpansion

rem // Define constants here:
set "_FILE=%~1" & rem // (CSV file to process; use first argument)
set "_SEP=,"    & rem // (separator character)
set "_HEAD=F0"  & rem // (header text for new column)
set /A "_MOD=2" & rem // (divisor for modulo operation)
set /A "_OFF=0" & rem // (offset for modulo operation)

setlocal EnableDelayedExpansion
rem // Determine number of lines contained in CSV file:
for /F %%C in ('^< "!_FILE!" find /C /V ""') do set /A "COUNT=%%C"
rem // Read from CSV file:
< "!_FILE!" (
    rem // Check whether header text is defined:
    if defined _HEAD (
        rem // Header text defined, so read current header:
        set "LINE=" & set /P LINE=""
        rem // Prepend header text for new column to current line:
        echo(!_HEAD!!_SEP!!LINE!
        rem // Decrement number of lines:
        set /A "COUNT-=1"
    )
    rem // Process remaining lines in a loop:
    for /L %%I in (1,1,!COUNT!) do (
        rem // Read current line:
        set "LINE=" & set /P LINE=""
        rem // Perform modulo operation:
        set /A "NUM=(%%I+_OFF-1)%%!_MOD!"
        rem // Prepend remainder of division to current line:
        echo(!NUM!!_SEP!!LINE!
    )
)
endlocal

endlocal
exit /B

This approach uses input redirection to read from the input CSV file.

To write the output to another CSV file, say data-mod.csv, rather than to the console, use the following command line, assuming the script is called prepend-modulo.bat and the input CSV file is named data.csv, and both reside in the current directory:

prepend-modulo.bat "data.csv" > "data_mod.csv"

Upvotes: 1

Compo
Compo

Reputation: 38718

This method is the same as Aacini's however it prepends the header line with #,, (can be modified).

@Echo Off & SetLocal EnableDelayedExpansion
Set "i=" & (For /F "UseBackQ Delims=" %%A In ("J_CAFE27032018_090325.csv") Do (
    If Defined i (Echo !i!,%%A) Else Set "i=1" & Echo #,%%A
    Set /A "i=(i+1)%%2"))>newfile.csv & Exit /B

Upvotes: 2

Magoo
Magoo

Reputation: 80203

@ECHO OFF
SETLOCAL
SET "sourcedir=U:\sourcedir"
SET "destdir=U:\destdir"
SET "filename1=%sourcedir%\q50041056.txt"
SET "outfile=%destdir%\outfile.txt"
SET "firstline=Y"
SET "zerostart=Y"
(
FOR /f "usebackqdelims=" %%a IN ("%filename1%") DO (
 IF DEFINED firstline (
  ECHO F0,%%a
  SET "firstline="
 ) ELSE (
  IF DEFINED zerostart (
   ECHO 0,%%a
   SET "zerostart="
  ) ELSE (
   ECHO 1,%%a
   SET "zerostart=Y"
  )
 )
)
)>"%outfile%"

GOTO :EOF

You would need to change the settings of sourcedir and destdir to suit your circumstances.

I used a file named q50041056.txt 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.

This solution uses the fact that if defined interprets the current status of the variablename, so the variable in question is simply toggled between a value and nothing.

Upvotes: 1

Aacini
Aacini

Reputation: 67256

This is one of several ways to do it:

@echo off & setLocal enableDELAYedeXpansion
set "i=0"
(for /f "delims=" %%a in (J_CAFE27032018_090325.csv) do (
   echo !i!,%%a
   set /A "i=(i+1)%%2"
)) > newfile.csv

However your original logic dos not correctly process the header (first) line...

Upvotes: 1

Related Questions