Windows Batch: replace substring in CSV file

Question

This is a popular subject on the Web but I can't find a simple way to do a find-and-replace inside a CSV file. My CSV file looks like this:

"0.219530613504834,43.7737904197643,PR RUE D'ARTOIS"
"0.522235882011867,41.7681203998576,PR DE LA FOSSE AU ROI"
"0.039404145384227,44.7565229712732,PR DES PETITS PRES"

I need to remove double quotes at the beginning and end of each line.

I tried the following script:

setlocal enabledelayedexpansion

for /f "tokens=*" %%a in (D:/data.csv) do (
    set temp=%%a
    set temp=%temp:^"=%
)

My logic is to store each line in %%a and to replace the double quotes by nothing.

I escape the double quote with ^.

This script is doing nothing. Could you help?

dbenham · Accepted Answer · 2016-02-18 00:46:43Z

2

There is a much simpler solution that avoids delayed expansion altogther - The ~ modifier strips enclosing quotes from FOR variable content.

for /f "delims=" %%a in (D:/data.csv) do echo %%~a

answered Feb 18, 2016 at 0:46

dbenham

132k29 gold badges273 silver badges410 bronze badges

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

3 Comments

wiltomap Over a year ago

Good job! This do the job perfectly... However, I will prefer @Klitos Kyriacou's answer as I also have a few double quotes inside the lines that I need to get rid of too.

dbenham Over a year ago

@wiltomap - Be careful about removing additional quotes from a CSV file - they may be important.

wiltomap Over a year ago

OK @dbenham, thanks for the warning! Anyway, my CSV file is very simple and I'm sure I can delete these characters safely.

Klitos Kyriacou · Accepted Answer · 2016-02-17 15:34:27Z

1

Your attempt has several issues:

You need to use delayed expansion (use ! instead of %). Without delayed expansion, the variable gets expanded before the loop is entered.
It's more reliable to use "delims=" than "tokens=*". With "tokens=*" each line is still tokenized, and then all tokens are concatenated separated by a space. But the tokens may have originally been separated by a ;. So if you had "one;two" in the input you'd get "one two" in the output.
You don't need to escape the ".

You need to print temp, not just modify it without using the new value.

setlocal enabledelayedexpansion

for /f "delims=" %%a in (D:/data.csv) do (
    set temp=%%a
    echo !temp:"=!
)

answered Feb 17, 2016 at 15:34

Klitos Kyriacou

11.9k3 gold badges50 silver badges86 bronze badges

4 Comments

wiltomap Over a year ago

Thanks! But my file still includes double quotes. In the cmd window, I can control that temp is echoed properly without double quotes, but the file isn't changed (double quotes are not removed from it).

Klitos Kyriacou Over a year ago

This doesn't make in-place modifications to the file. It produces new output, leaving the input unchanged. You can pipe the output of your batch script using ` > newfile` and then check the contents of newfile and, if it's what you want, you can replace the old file.

wiltomap Over a year ago

Thanks, that's what I was thinking about. I just wanted to make sure that it was not possible to do directly inside the input.

dbenham Over a year ago

You are correct that many people use "tokens=*" when they really should use "delims=". But your explanation is wrong - tokens=* does not tokenize the entire line. It simply strips leading delimiters from the line, and leaves the rest alone.

Collectives™ on Stack Overflow

Windows Batch: replace substring in CSV file

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related