Add columns in CSV file using windows batch script

Question

i have a csv file with pipe (|) delimiter like below. Column header defines by 6 columns but rows value has 7 columns.

Column 1|Column 2|Column 3|Column 4|Column 5|Column 6
Value 1 |Value 2 |Value 3 |Value 4 |Value 5 |Value 6 |0
Value 11|Value 12|Value 13|Value 14|Value 15|Value 16|0

So i try to make new column with windows batch script.

Here is my code :

@Echo off&Setlocal EnableExtensions EnableDelayedExpansion
Set Row=0
( for /f "tokens=1-7 delims=|" %%A in (BAT0071.csv) do (
    Set /A Row+=1
    If !Row! lss 2 (Rem new header
      echo %%A^|%%B^|%%C^|%%D^|%%E^|%%F^|IgnoreLast
    ) Else (Rem insert duplicate values
      echo %%A^|%%B^|%%C^|%%D^|%%E^|%%F^|%%G
    )
  )
) >"BAT0071.tmp"
move /y "BAT0071.tmp" "BAT0071.csv"
PAUSE

The problem is if all rows value filled then its fine, but example value 3 in first row empty then the structure changed like below.

Column 1|Column 2|Column 3|Column 4|Column 5|Column 6|IgnoreLast
Value 1 |Value 2 |Value 4 |Value 5 |Value 6 |0|
Value 11|Value 12|Value 13|Value 14|Value 15|Value 16|0

How to make empty value still in position?

Expected result

Column 1|Column 2|Column 3|Column 4|Column 5|Column 6|IgnoreLast
Value 1 |Value 2 |Value 3 |Value 4 |Value 5 |Value 6 |0
Value 11|Value 12|Value 13|Value 14|Value 15|Value 16|0

Hi @GerhardBarnard thanks for your response. My bad, i've edited it. So if in my csv file value 3 equals empty value, then the script will change the position value 4 to be in column 3. Any advice? — Ben Richard
– Ben Richard, Commented Dec 10, 2019 at 6:34
@GerhardBarnard added, please see value 3 and pipe delimiter in second row — Ben Richard
– Ben Richard, Commented Dec 10, 2019 at 6:56
Hi @GerhardBarnard yes this csv format same, i've try from duplicate link but i still don't get it :( — Ben Richard
– Ben Richard, Commented Dec 10, 2019 at 7:17

user7818749 · Accepted Answer · 2019-12-10 07:37:09Z

1

In this particular case, because you do not add additional values, we simply do not use delims:

@Echo off&Setlocal EnableExtensions EnableDelayedExpansion
Set Row=0
(for /f "delims=" %%a in (BAT0071.csv) do (
    Set /A Row+=1
    If !Row! equ 1 (
      echo %%a^|IgnoreLast
    ) else (
      echo %%a
    )
 )
)>BAT0071.tmp
move /y "BAT0071.tmp" "BAT0071.csv"
PAUSE

answered Dec 10, 2019 at 7:37

user7818749

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

3 Comments

Ben Richard Over a year ago

I see! so don't need to put another column in else condition. Thank you so much for your help and insight.

user7818749 Over a year ago

Ok, so you can still add additional columns if needed, it is just that currently you are not manipulating the current data, so no need to delimit on it at all, if you were to manipulate current data, then it is a different scenario.

Ben Richard Over a year ago

if want to manipulate data in new column how is the mechanism? in else condition add echo %%a|%%b? (in case i want that new column have all value same with in rows value in column 2)

Collectives™ on Stack Overflow

Add columns in CSV file using windows batch script

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related