Find & Replace "," with " " in large text file using batch

[DaveLembke]

I am trying to change a large text file that is comma delimited to space delimited and was going to do this with notepad, but notepad isnt happy with replacing a comma with a space. So I thought this is the perfect job for a batch file, but then I drew a blank on how to make this happen using batch. So I figured I would post this to see if anyone can refresh my memory on how this is done. I am thinking I use a FOR statement that will look for all comma's IN ('file.txt') and replace them with a space. Not sure if the space can be declared by " " or if another method is needed? Guessing this will be a few lines to make happen maybe?

[ghostdog74]

I am curious, you have exposure to Perl right? so can't you do it in Perl? why waste time with a batch?

Code: [Select]

echo a,b,c | perl -pne 's/,/ /g'
a b c



[Salmon Trout]

Code: [Select]

@echo off
setlocal enabledelayedexpansion

echo 1,eggs,milk,bacon>input.csv
echo 2,bread,cakes,flour>>input.csv
echo 3,salmon,pork,chicken>>input.csv
echo 4,beer,wine,brandy>>input.csv

if exist output.csv del output.csv

REM One line does the work
for /f "delims==" %%A in (input.csv) do set string=%%A & echo !string:,= ! >> output.csv

echo input file       
type input.csv
echo.
echo output file
type output.csv       
echo.

Code: [Select]

input file
1,eggs,milk,bacon
2,bread,cakes,flour
3,salmon,pork,chicken
4,beer,wine,brandy

output file
1 eggs milk bacon
2 bread cakes flour
3 salmon pork chicken
4 beer wine brandy

[DaveLembke]

Thanks everyone! 

Ghostdog in regards to perl, I was thinking it would be more involved than batch to replace all "," with " ", such as almost equally involved to do it in perl as it would be in C++ where you have to open file and read in contents to an array from file, then close then use a loop to search for all "," and replace with " " and then open the output file and write to the output file and then close...so i was thinking batch would be the more clean cut method to achieve this replacement result.



Salmon thanks for showing how to achieve this in batch... have a question about the line below in regards to the "!" instruction !string:,= ! >> 
Question is I dont recall ever seeing a !string: function before but I am guessing the , before the = followed by the space between the following ! and >> write to output.csv is where the space is being passed from in substitution for ","   is this required because a space such as " " cant be declared to be passed in for ","
for /f "delims==" %%A in (input.csv) do set string=%%A & echo !string:,= ! >> output.csv

Now to put it to work...Many Thanks!!! 

[BC_Programmer]

Ghostdog in regards to perl, I was thinking it would be more involved than batch to replace all "," with " ", such as almost equally involved to do it in perl as it would be in C++ where you have to open file and read in contents to an array from file, then close then use a loop to search for all "," and replace with " " and then open the output file and write to the output file and then close...so i was thinking batch would be the more clean cut method to achieve this replacement result.

That is simply wrong. In fact, it is exactly this type of problem that perl was designed for.

[Salmon Trout]

a question about the line below in regards to the "!" instruction !string:,= ! >> 
Question is I dont recall ever seeing a !string: function before but I am guessing the , before the = followed by the space between the following ! and >> write to output.csv is where the space is being passed from in substitution for ","   is this required because a space such as " " cant be declared to be passed in for ","
for /f "delims==" %%A in (input.csv) do set string=%%A & echo !string:,= ! >> output.csv

1.

Replacing a substring in a string - general format is where %string% is a variable:

%string:A=B%

where A is the part (or all) or the string you want to change and B is what you want to see there instead.

A must be at least one character (obviously) and B can be zero or more characters. If B is nothing (zero characters) then the effect is that A is deleted.

A can begin with an asterisk, in which case it will match everything from the beginning of the expanded output to the first occurence of the remaining portion of A.

You can echo the string with changes:

echo %string:A=B%

change the string itself:

set string=%string:A=B%

Or you can create a new string containing the changes:

set newstring=%string:A=B%

Thus:

c:\>set string=California

c:\>echo %string:C=K%
Kalifornia

c:\>echo %string:C=c%
california

c:\>echo %string:a=A%
CAliforniA

c:\>echo %string:a=%
Cliforni

c:\>echo %string:nia=nia the Golden State%
California the Golden State

c:\>echo %string:California=Kansas%
Kansas

C:\>echo %string:*or=Pennsylva%
Pennsylvania

c:\>set string1=%string:C=K%

c:\>echo %string1%
Kalifornia

c:\>set string=I love my dog

c:\>echo %string:dog=cat%
I love my cat


2.

To change all the lines in a text file you can use FOR /F to read in the lines one by one and get each one in turn into a variable.

In a FOR loop % variables are expanded at parse time not at run time

If you want to set and then use variables you use delayed expansion with exclamation marks ! instead of percent signs.

To enable delayed expansion you put this command before the loop

setlocal enabledelayedexpansion

so echo !string:,= ! echoes !string! (we're in a loop, remember) but each comma is replaced by one space (there is one space after the equals sign).

I am not going to get into an argument about whether perl, awk sed, or whatever is "better" than batch, or try to tell you that you "ought" to use any particular tool, but since you explicitly asked how to do string substitution with a built in tool that is already installed that is what I have suggested.

This and plenty of other stuff is documented in the SET documentation which you can see by typing SET /? at the prompt.

[DaveLembke]

Many Thanks for going into detail and showing examples. Going to save this page as a pdf and save for future reference. 

[ghostdog74]

Thanks everyone! 

Ghostdog in regards to perl, I was thinking it would be more involved than batch to replace all "," with " ", such as almost equally involved to do it in perl as it would be in C++ where you have to open file and read in contents to an array from file, then close then use a loop to search for all "," and replace with " " and then open the output file and write to the output file and then close...so i was thinking batch would be the more clean cut method to achieve this replacement result.

Adding on to BC's comment (which i agree totally), Perl has been used to manipulate files since day one. The switches -ne and for later version , the -a option together with -F (which allows you specify a delimiter , including a regex) allows you to "open the file", "read in the contents", and "close the file".  Because your file format is VERY SIMPLE, maybe a batch for loop is enough for you at the moment. BUT if you have things like embedded commas between quotes , or you have more complex requirements, Perl (or any other language) can do the job much better than crippled batch. Its a waste not to hone your Perl (Python or Ruby or whatever you have learned besides batch etc ) knowledge to further maximize their capability, than sticking to "old technology that didn't keep up with the times"

[Geek-9pm]

It also could be done in assembler.

[Salmon Trout]

It also could be done in assembler.

Example?

[ghostdog74]

It also could be done in assembler.

yes it can, but would you? this is called impractical. I hope you are not trolling, otherwise, I will put you in Bill's category.

[Salmon Trout]

This thread, resolved to the OP's explicitly expressed satisfaction, has grown a sort of argumentative "tail", as so often happens when certain people decide to post. I feel this is a deplorable tendency. Typical scenario:

OP: How can I do xyz in batch?

Someone: you can do it like this for /f blah blah blah in %%Z do blah blah blah

OP: That worked, thanks!

Someone else: That's crap, do it in Perl

Someone else again: No do it in awk

Billrich:

c:\batch\BillRich>type xyz.txt
blah blah blah
blah blah blah

 

[ghostdog74]

I feel this is a deplorable tendency.

I beg to differ. I can say most of the time, the OP doesn't know better, even though its posted in a "batch forum". A batch that works does not mean its maintainable, or efficient, or platform dependent or you name it. Sometimes, the solution to the problem can be done more efficiently, with more maintainability and even works across multi platforms. Its up to those who knows better to post as possible solutions to a particular problem.

[ghostdog74]

Someone else: That's crap, do it in Perl

Someone else again: No do it in awk

I do not have a problem if you specifically quote my nick for the above 2 sentences, because I do stand by it. Anything else is better than batch , to do anything else.

[Salmon Trout]

I do not have a problem if you specifically quote my nick for the above 2 sentences

Maybe hard for you to believe this, but it's not all about you! Except insofar as you are a scripting-tool fanboi...