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, Dec 10 '19 at 06:34
@GerhardBarnard added, please see value 3 and pipe delimiter in second row — Ben Richard, Dec 10 '19 at 06:56
Hi @GerhardBarnard yes this csv format same, i've try from duplicate link but i still don't get it :( — Ben Richard, Dec 10 '19 at 07:17

score 1 · Accepted Answer · answered Dec 10 '19 at 07:37

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 '19 at 07:37

Gerhard

22,678
7
27
43

I see! so don't need to put another column in else condition. Thank you so much for your help and insight. – Ben Richard Dec 10 '19 at 07:47
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. – Gerhard Dec 10 '19 at 08:00
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) – Ben Richard Dec 10 '19 at 08:49

Add columns in CSV file using windows batch script

1 Answers1