how to replace comma with semicolon and remove double quotes in file using a bat file

Question

Actually i am very poor at batch programming...

I have a csv file in which data is like this

"column1","column2","column2"
"value1","value2","value3"

I have to replace comma with semicolon and remove double quotes.

MY output should be like this..

 column1;column2;column2
 value1;value2;value3

I have tried like this..

@echo off
setlocal enabledelayedexpansion

for /f "delims==" %%A in (input.csv) do (
  set string=%%A & echo !string:,=;! >> output.csv
)

it is replacing comma with semicolon (i dont know how it is) but how to remove double quotes....

1

Try it with `set string=%%~A` – jeb Apr 10 '14 at 07:49

score 3 · Accepted Answer · answered Apr 10 '14 at 08:02

3

@echo off
    setlocal enableextensions disabledelayedexpansion
    (for /f tokens^=1^-3delims^=^,^" %%a in (input.csv) do (
        echo(%%a;%%b;%%c
    ))>output.csv

It the real case is as simple as it has been posted, just define the "problematic" characters as delimiters in the for command to get them removed.

answered Apr 10 '14 at 08:02

MC ND

69,615
8
84
126

Thanx for your reply @MC ND .. Can I have an explanation of your code (if you dont mine)..is it works for all files???? – techrhl Apr 10 '14 at 10:15
What if i have 30 columns as in same format shown at top of this page?? – techrhl Apr 10 '14 at 10:41
@alluhur What does the code do? for each line in the input file, using the characters `,"` (and including the `"` as a delimiter is what makes all the `^` necessary to escape the delimiters in the `for` options string as quotes can not be used) as delimiters/field separator, take the three fields and output them separated with semicolons. – MC ND Apr 10 '14 at 10:49
Ya.. perfectly it is working for 3 columns.. if i have 30 columns (really i am having 29) how can i execute this??? – techrhl Apr 10 '14 at 10:54
@alluhur, and no, if you have 30 columns this method should not be used (you can, see [here](http://stackoverflow.com/a/8520993/2861476), but too much complication without need). You will need to continue with your original code using `set "var=%%A"` `set "var=!var:"=!"` `set "var=!var:,=;!"` – MC ND Apr 10 '14 at 11:02

score 0 · Answer 2 · answered Apr 11 '14 at 09:55

Here is one method: in two passes it replaces all double quotes \q with nothing, and then replaces all , with ;

This uses a helper batch file called repl.bat - download from: https://www.dropbox.com/s/qidqwztmetbvklt/repl.bat

Place repl.bat in the same folder as the batch file or in a folder that is on the path.

type "file.csv" | repl "\q" "" x |repl "," ";" L >"newfile.csv"

inputfile

"column1","column2","column2"
"value1","value2","value3"

outputfile

column1;column2;column2
value1;value2;value3

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

Thanks MC ND I used your answer, that worked right out of the box, and made a few "improvments":

4 columns
comma insted as delimiter
change .csv to .txt-file

@echo off        
    setlocal enableextensions disabledelayedexpansion
    
    (for /f tokens^=1^-4delims^=^,^" %%a in (input.csv) do (
    
        echo(%%a,%%b,%%c,%%4
    
    ))>output.txt

The input file as below:

"column1","column2","column3","column4"

"value1","value2","value3","value4"

"value5","value6","value7","value8"

I added a row extra.

how to replace comma with semicolon and remove double quotes in file using a bat file

3 Answers3