Need to replace specific text in CSV file using batch script

Question

I want to replace specific text in CSV file in specific column. I'm able to do that using old post. But that code is not working when my CSV data contains space. I am not batch script expert so I am not able to fix it. I tried many things.

@echo off
setlocal enabledelayedexpansion
set inputCSV=TestCSV.csv
set outputCSV=TestCSV2.csv

(for /f "tokens=*" %%a IN (%inputCSV%) DO (
  set column=0
  set "line="
  for %%i in ( %%a ) do ( 
    set /a column+=1
    set value=%%~i
    if !column!==4 (   
      if "!value!"=="TEST" set "value=abc"
    ) 
    set "line=!line!,"!value!"" 
  )
  echo !line:~1!
))>%outputCSV%

ID,Name,Date,Field1,Test Data  Space Check
1010101,Test 1,01/27/2001 10:00:00 PM,TEST,Test Data  Space Check
2020202,Test 2,01/27/2001 10:00:00 PM,TEST,Test Data  Space Check
3030303,Test 3,01/27/2001 10:00:00 PM,TEST,Test Data  Space Check
4040404,Test 4,01/27/2001 10:00:00 PM,TEST,Test Data  Space Check
5050505,Test 5,01/27/2001 10:00:00 PM,TEST,Test Data  Space Check

I ALso tried this but it didn't work.

@echo off
setlocal enabledelayedexpansion
set inputCSV=TestCSV.csv
set outputCSV=TestCSV2.csv
:: tokens=* 
(for /f "tokens=*" %%a IN (%inputCSV%) DO (
  set column=0
  set "line="
  for /f "delims=," %%i in ( %%a ) do ( 
    set /a column+=1
    set value=%%~i
    if !column!==4 (   
      if "!value!"=="TEST" set "value=ABC"
    ) 
    set "line=!line!,"!value!"" 
  )
  echo !line:%%a!
))>%outputCSV%

This is how the final output looks

Old post: Use batch scripting to replace null values with 0 in a specific column in a CSV

Are you still replacing null values in column 4 with 0? or is the code not related to the task you're now wanting it for? Also you need to expand on where the issue lies with csv data containing a space, because we cannot see it, and you've not attempted to account for that. — Compo
– Compo, Commented Jan 22, 2019 at 16:27
@Compo I am replacing some string with another string. Something like if "!value!"=="Test" set "value=ABC". I have updated the question with test csv data. — Beta User
– Beta User, Commented Jan 22, 2019 at 17:18

score 1 · Accepted Answer · 2019-01-24 11:32:17Z

1

From your excel image you also seem to have an issue with a BOM (byte order mark) from your used editor.

The old post you reference is different because there all fields are quoted.

To split your csv at the commas use the comma as a delimiter in the for /f (provided there are no commas inside the field data).

To exchange a text, use string replacement (what requires to copy to a regular (non for meta) variable inside a code block what in turn requires delayedexpansion).

This batch:

:: Q:\Test\2019\01\22\SO_54312302.cmd
@echo off
setlocal enabledelayedexpansion
set inputCSV=TestCSV.csv
set outputCSV=TestCSV2.csv

(for /f "tokens=1-4* delims=," %%a IN (%inputCSV%) DO (
  set "col4=%%d"
  set "col4=!col4:foo=bar!"
  Set "col4=!col4:test=abc!"
  echo %%a,%%b,%%c,!col4!,%%e
))>%outputCSV%

yields this output:

> type TestCSV2.csv
ID,Name,Date,Field1,Test Data  Space Check
1010101,Test 1,01/27/2001 10:00:00 PM,abc,Test Data  Space Check
2020202,Test 2,01/27/2001 10:00:00 PM,abc,Test Data  Space Check
3030303,Test 3,01/27/2001 10:00:00 PM,abc,Test Data  Space Check
4040404,Test 4,01/27/2001 10:00:00 PM,abc,Test Data  Space Check
5050505,Test 5,01/27/2001 10:00:00 PM,abc,Test Data  Space Check

When importing in Excxel use the comma as delimiter.

edited Jan 24, 2019 at 11:32

answered Jan 22, 2019 at 17:54

user6811411

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

2 Comments

Beta User Over a year ago

thanks your answer worked for me. Just small thing if I need to replace another value in same column I can add that. Means I need to replace two or more values in the same column?

user6811411 Over a year ago

To exchange other values, you need additional set commands, I'll insert one in my answer.

aborruso · Accepted Answer · 2019-01-22 18:12:42Z

1

You need a good CSV tool as Miller (http://johnkerl.org/miller/doc)

It's very simple. With

mlr.exe --csv put '$Field1=gsub($Field1,"TEST","Foo")' input.csv

You have

ID,Name,Date,Field1,Test Data  Space Check
1010101,Test 1,01/27/2001 10:00:00 PM,Foo,Test Data  Space Check
2020202,Test 2,01/27/2001 10:00:00 PM,Foo,Test Data  Space Check
3030303,Test 3,01/27/2001 10:00:00 PM,Foo,Test Data  Space Check
4040404,Test 4,01/27/2001 10:00:00 PM,Foo,Test Data  Space Check
5050505,Test 5,01/27/2001 10:00:00 PM,Foo,Test Data  Space Check

It's a great opensource multiplatform utility. Here you have also the win exe https://github.com/johnkerl/miller/releases/tag/5.4.0

answered Jan 22, 2019 at 18:12

aborruso

5,8723 gold badges27 silver badges49 bronze badges

Comments

Compo · Accepted Answer · 2019-01-22 20:58:42Z

0

You could, I suppose, utilise PowerShell from a batch file.

Basic Example:

@PowerShell -NoP -C "Import-Csv '.\TestCSV.csv'"^
 "|%%{$_.'Field1'=$_.'Field1' -CReplace('^TEST$','ABC');$_}"^
 "|ConvertTo-CSV -N|Out-File '.\TestCSV2.csv' -Fo -En ASCII"

…and to make it a little easier to understand/modify

@Echo Off
Set "Inp=.\TestCSV.csv"
Set "Out=.\TestCSV2.csv"
Set "Col=Field1"
Set "Old=TEST"
Set "New=ABC"

PowerShell -NoProfile -Command "Import-Csv '%Inp%' "^
 "| ForEach-Object {$_.'%Col%'=$_.'%Col%' -CReplace('^%Old%$','%New%'); $_} "^
 "| ConvertTo-CSV -NoTypeInformation | Out-File '%Out%' -Force -Encoding ASCII"

edited Jan 22, 2019 at 20:58

answered Jan 22, 2019 at 20:52

Compo

39k5 gold badges32 silver badges46 bronze badges

Collectives™ on Stack Overflow

Need to replace specific text in CSV file using batch script

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related