CODEWITHSUNDEEP
csvbatch-file
Beta User
Beta User

Reputation: 3

Need to replace specific text in CSV file using batch script

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 Final Output

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

Upvotes: 0

Views: 3396

Answers (3)

user6811411
user6811411

Reputation:

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.

Upvotes: 1

Compo
Compo

Reputation: 38613

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"

Upvotes: 0

aborruso
aborruso

Reputation: 5688

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

Upvotes: 0

Related Questions