Replace string in csv file using batch file

Question

My csv file has 25 columns, I am trying to replace double double quotes" to \" from 23rd field. However, the logic below is excluding the empty data in output, for example:

input data:

14579865,YUPO,"RAMANMAN",2222222,1111,"RAM","Active",,,False

output data:

14579865,YUPO,"RAMANMAN",2222222,1111,"RAM","Active",False

Here in above input, we have empty data in 8th and 9th fields. Whereas in output, 10th field is moved to 8th field which is totally messing up.

Script used:

set "search_string="""
set "replace_string=\""

for /f "tokens=1-15,* delims=," %%c in ("!input_file!") do (
    set "line=%%a,%%b,%%c,%%d,%%e,%%f,%%g,%%h,%%i,%%j,%%k,%%l,%%m,%%n,%%o,%%p,%%q,%%r,%%s,%%t,%%u,%%v,%%w,%%x,%%y" 
    set "column_value_cn=%%w" 
    echo !column_value_cn! | findstr /c:"%search_string%" >nul
    if !errorlevel! == 0 (
        echo Match found!
        set "column_value_cn=!column_value_cn:%search_string%=%replace_string%!"
        set "line1=%%a,%%b,%%c,%%d,%%e,%%f,%%g,%%h,%%i,%%j,%%k,%%l,%%m,%%n,%%o,%%p,%%q,%%r,%%s,%%t,%%u,%%v,!column_value_cn!,%%x,%%y"
    )
    echo !line! >> "%TEMPFILE%"
)

move /y "%TEMPFILE%" "!input_file!"

I really appreciate your help.

Double double quotes are used to escape double-quotes - is that definitely what you intend to remove? (Would using PowerShell be an option?) — Andrew Morton
– Andrew Morton, Commented Apr 13 at 18:00
There should be read first the Wikipedia page about comma-separated values (CSV) format explaining the valid syntax to use on a field value contains a comma or newline characters by using " around the field value string and that one more " must be used to escape the character " inside a field value enclosed in ". A replacement of "" by \" makes the CSV file invalid for all applications capable of correct parsing a valid CSV file. — Mofi
– Mofi, Commented Apr 14 at 17:25
Well, many applications reading data from a CSV file are very poor coded and do not fully support CSV file parsing with field values enclosed in " containing , or " escaped with one more " or newline characters inside such a field value. — Mofi
– Mofi, Commented Apr 14 at 17:29

ProfiDE · Accepted Answer · 2025-04-13 18:05:53Z

Try this instead:

@echo off
setlocal enabledelayedexpansion

set "input_file=yourfile.csv"
set "TEMPFILE=%temp%\tempfile.csv"
set "search_string="""
set "replace_string="^""

(for /f "usebackq delims=" %%A in ("%input_file%") do (
    set "line=%%A"
    set "newline="
    
    :: Split the line into columns
    set "remaining=!line!"
    for /l %%i in (1,1,25) do (
        for /f "tokens=1* delims=," %%x in ("!remaining!") do (
            set "col%%i=%%x"
            set "remaining=%%y"
        )
    )
    
    :: Process the 23rd column
    if defined col23 (
        set "col23=!col23:%search_string%=%replace_string%!"
    )
    
    :: Rebuild the line with all 25 columns
    set "newline=!col1!"
    for /l %%i in (2,1,25) do (
        set "newline=!newline!,!col%%i!"
    )
    
    echo !newline!
)) > "%TEMPFILE%"

move /y "%TEMPFILE%" "%input_file%"
endlocal

But if you can use PowerShell instead, this would be more reliable:

$inputFile = "yourfile.csv"
$outputFile = "output.csv"

Import-Csv $inputFile -Header (1..25) | ForEach-Object {
    if ($_.P23 -ne $null) {
        $_.P23 = $_.P23 -replace '""', '"'
    }
    $_
} | Export-Csv $outputFile -NoTypeInformation -Force

Magoo · Accepted Answer · 2025-04-14 14:48:53Z

@ECHO Off
SETLOCAL
rem The following settings for the directories and filenames are names
rem that I use for testing and deliberately includes spaces to make sure
rem that the process works using such names. These will need to be changed to suit your situation.

SET "sourcedir=u:\your files"
SET "destdir=u:\your results"
SET "filename1=%sourcedir%\q79571846.txt"
SET "outfile=%destdir%\outfile.txt"

SET /a target_field=9

(
FOR /f "usebackqdelims=" %%e IN ("%filename1%") DO (
 SET "line=%%e"
 CALL :process
)
)>"%outfile%"

GOTO :EOF

:process
SET "newline="
SET /a field=1
SET "quoted_field="
:buildnew
:: first character
SET "test=%line:~0,1%"
SET "test=%test:"=%"
IF NOT DEFINED test GOTO rabbits_ears
IF NOT "%test%"=="," GOTO transfer_char
:: a comma - count if it's not a quoted field
IF NOT DEFINED quoted_field SET /a field+=1
:transfer_char
SET "newline=%newline%%line:~0,1%"
SET "line=%line:~1%"
IF DEFINED line GOTO buildnew
ECHO %newline%
GOTO :eof

:rabbits_ears
IF DEFINED quoted_field (SET "quoted_field=") ELSE SET "quoted_field=Y"
:: if quoted_field is set, we are within a quoted field, so comma is no longer special
IF %field%==%target_field% SET "newline=%newline%\"
GOTO transfer_char

GOTO :eof

Always verify against a test directory before applying to real data.

Note that if the filename does not contain separators like spaces, then both usebackq and the quotes around %filename1% can be omitted.

YYou would need to change the values assigned to sourcedir and destdir to suit your circumstances. The listing uses a setting that suits my system.

I deliberately include spaces in names to ensure that the spaces are processed correctly.

I used a file named q79571846.txt containing this data for my testing.

14579865,YUPO,"RAMANMAN",2222222,1111,"RAM","Active",,,False
14579865,YUPO,"RAMANMAN",2222222,1111,"RAM","Active",,"Fixme",False
14579865,YUPO,"RAMANMAN",2222222,1111,"RAM","Active",,"Fixme, but I have a comma",False
14579865,YUPO,"RAMANMAN",2222222,1111,"RAM","Active",,Leaveme,False

Result:

14579865,YUPO,"RAMANMAN",2222222,1111,"RAM","Active",,,False
14579865,YUPO,"RAMANMAN",2222222,1111,"RAM","Active",,\"Fixme\",False
14579865,YUPO,"RAMANMAN",2222222,1111,"RAM","Active",,\"Fixme, but I have a comma\",False
14579865,YUPO,"RAMANMAN",2222222,1111,"RAM","Active",,Leaveme,False

Produces the file defined as %outfile%

I operated on field 9, not 23.

Assign each line in turn to line via %%e.

Process each character in line in turn to newline

If the character is ", then frob the quoted_string switch. If the field being processed is the target field, inject the required backslash.

If the character is a comma, then we are moving to the next field number unless we are in a quoted string.

Naturally, this will not process certain characters like % correctly, as some symbols have special meaning to the batch language.

Collectives™ on Stack Overflow

Replace string in csv file using batch file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related