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...

[BC_Programmer]

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

That's not true! He always starts with a dir for no reason!

Its a waste not to hone your Perl (Python or Ruby or whatever you have learned besides batch etc )

Indeed, The OP has shown some usage of perl previously, and I mean no offense to them but it had a clear "beginner" look to it; I say this because many calls were "system()'d" (or whatever the equivalent Perl statement for shelling another app is) Which isn't so bad in and of itself, but many of the tasks being done with it were quite doable from within perl. With that in mind, I feel maybe they may have some misconceptions about how versatile perl is, because even when they are using perl (and even C++) they are still performing a lot of their tasks using "batch" (shelled out commands). I've always thought it was utterly silly to shell out to another application in this fashion, and is usually a result of only trying what you think is obvious, having it not work, and just shelling out and using a batch command you are familiar with; for example:

Code: [Select]

#!/usr/bin/perl
system("cmd /c for %P in (*.txt) do echo %P");
Isn't <really> a perl script; it's a batch file pretending to be a perl script. Obviously that's a simplified example, but usually when you see System() Calls all over- in almost any language- That's usually a sign that the language is badly written (and doesn't have that functionality) or that the writer of said script is in a rush to create something and couldn't be bothered to read the documentation; (Again, I mean zero offense to you (DaveLembke) by this; the documentation is hardly something I crack open next to the fire either), And if that's the case, education as to such possible shortcomings would usually be preferable compared to possibly reinforcing the same habits; even if the solution, in this case, ended up as a pure batch file, I saw no reason not to correct a perceived misconception as to the applicability of a tool designed specifically for the task in question.
[/code]

[Salmon Trout]

Code: [Select]

#!/usr/bin/perl
system("cmd /c for %P in (*.txt) do echo %P");


Is cmd.exe kept in /usr/bin/windows?

[BC_Programmer]

Is cmd.exe kept in /usr/bin/windows?

No idea.

The script does run and work as intended. cmd is on the path.

[Salmon Trout]

No idea.

The script does run and work as intended. cmd is on the path.

A shebang line starting a script that would only run in Windows...

[ghostdog74]

Maybe hard for you to believe this, but it's not all about you!

Its true that its not all about me, however, its also not hard to believe that I am MOSTLY the one who post awk solutions here. Also, I am the one who first encourages OP to use Perl (in this thread). Therefore, its OK to quote those 2 sentences you mentioned.

[ghostdog74]

Indeed, The OP has shown some usage of perl previously, and I mean no offense to them but it had a clear "beginner" look to it; I say this because many calls were "system()'d" (or whatever the equivalent Perl statement for shelling another app is) Which isn't so bad in and of itself, but many of the tasks being done with it were quite doable from within perl.

I agree about the issue of shelling out to call external system commands. Perl (and Python/Ruby etc) has the capability (and libraries) to do many system administration tasks. Moving/Copying/removing of files, finding files, text/string manipulation etc many which batch isn't able to provide effectively. Shelling out also makes the code non-portable.

[ghostdog74]

No idea.

The script does run and work as intended. cmd is on the path.

In windows, perl (its spelt will capital "P" when referring to the language, small "P" to refer to the interpreter) will ignore shebang line. Its also better to use #! /usr/bin/env perl instead of /usr/bin/perl since not all distributions had their Perl installed in /usr/bin

[BC_Programmer]

I shall refrain from posting examples without doing extensive google to searches to make sure that the content of my examples that have nothing to do with what I was demonstrating are not redundancies that can be poked at for no good reason.

Also, Billrich has helpfully informed me that cmd.exe is in C:\windows\system32. He did so with a dir.

[DaveLembke]

Yes ... I would consider myself a beginner and I have dabbled with many languages from the early TRS-80's and BASIC to todays modern languages. I have dabbled with many of them but never became a specialist of any specific language. I also at times have broken the GOTO RULE which make professional programmers cringe, where I would have used loops and then realize I want a quick redirection in my code without having to place all that within another nested loop. Once compiled it all runs the same, although maybe with a fraction of a second difference in execution time where one may be more efficient than the other which accomplish the same goal. When it comes to SYSTEM calls, this is sort of a GOTO like habit, where there is a better way to accomplish the same or better results, but its raw and dirty code that works for what was intended.

When it comes to perl, I learned it through an online course at Virtual University. This covered all the common routines like PRINT, IF THEN ELSE, Loops, and Arrays. But it didnt go into detail as to the key strengths of perl over say C++ or BASIC in which perl has many short hand features that I was unaware of. The only shorthand I can remember from BASIC way back with GW-Basic was using ? marks in place of typing out PRINT. When listing however the interpreter would flip the ? to PRINT

I suppose I should focus on one language and learn it inside and out instead of making dirty programs that are Rube Goldberg programming mixing and matching and sometimes dynamically compiling batch from perl in which the perl writes a batch file and then executes it to perform DOS batched functions etc. I am sure perl can do this without shelling out the functions by use of SYSTEM or compiling dynamic Batches on the fly. Like a Rube Goldberg making stuff more complex than it needs to be, but ending up with the same result, if I dont know of a quick better way to do something, I usually perform the quick 'Band-Aid' I guess you could call it to make it work, although attrocious to look at by a professional programmer..lol

Also the reason to do this in batch originally was also because I didnt want to have to install perl to the system that this was going to be run from, in addition to the fact that I thought batch was better geared for this replacement need, from seeing similar replacements in the past, but usually not with spaces but instead an alternate character such as \ with _

Any suggestions on a book that really shows the strengths of perl instead of the basics of print, logic, loops, and arrays that I can dive into to learn these strengths?

*Also I have had other programmers at times state that I should avoid perl for the nature of programming that I am doing where I am reading and writing to files and performing batched like system processes, because it was really created to be used for running CGI's with a web server such as Apache etc, while I actually like the structure of perl so I have stuck with it, with C++ being my second favorite although my C++ programs are all console type crude to get the job done and very rarely Visual C++. There suggestion was that I should stick with C++ over perl for my nature of programming, yet from the information shared here, it shows that perl is well equipt for my needs where I dont need any fancy gui, just for it to console and crunch. Also C++ programs to me require much more code than perl to get the same result.

[BC_Programmer]

*Also I have had other programmers at times state that I should avoid perl for the nature of programming that I am doing where I am reading and writing to files and performing batched like system processes, because it was really created to be used for running CGI's with a web server such as Apache etc,

They are absolutely, positively wrong. They are likely thinking of PHP.

[ghostdog74]

Also the reason to do this in batch originally was also because I didnt want to have to install perl to the system that this was going to be run from, in addition to the fact that I thought batch was better geared for this replacement need, from seeing similar replacements in the past, but usually not with spaces but instead an alternate character such as \ with _

for your information, you do not have to install Perl on every machine you are going to run your script on. You can just install on one, do your development there , and then convert it to an executable so you can run almost anywhere on systems with more or less the same configuration.

Any suggestions on a book that really shows the strengths of perl instead of the basics of print, logic, loops, and arrays that I can dive into to learn these strengths?

No one should miss the official Perl documentation. A book comes later, or not at all.

*Also I have had other programmers at times state that I should avoid perl for the nature of programming that I am doing where I am reading and writing to files and performing batched like system processes, because it was really created to be used for running CGI's with a web server such as Apache etc,

that's so wrong. If you read the history of Perl, its mainly used for creating reports in the past. As the years go by, it has become a full fledged programming language capable of doing many things, besides using it for CGI ( by the way CGI is outdated, nowadays there are web frameworks such as Catalyst for web development stuff.)

while I actually like the structure of perl so I have stuck with it, with C++ being my second favorite although my C++ programs are all console type crude to get the job done and very rarely Visual C++. There suggestion was that I should stick with C++ over perl for my nature of programming, yet from the information shared here, it shows that perl is well equipt for my needs where I dont need any fancy gui, just for it to console and crunch. Also C++ programs to me require much more code than perl to get the same result.

I can understand the need for C++ if one is doing low level systems programming or games programming with you need the speed requirement etc, but if you are doing mostly systems administration , C++ is not really the tool to use as you can produce humongous lines of code just to do one simple task. Using a modern programming language that is both practical and easy to use is the way to go.

[Geek-9pm]

I got flamed for mentioning assembler. This problem the OP posted is a elementary low-level job that can easily be done at the lowest level. The pearl advocate told me it was impractical.  At one time assembly was the only tool for low-cost microcomputers. It is trivial to open a file, change all instances of just one code and then close the file. It is one of the primitive things you learn in using Assembler with an Operation System.
When GW-BASIC came out, there was a version of it, as I recall, that would let you open a file in RANDOM, and use GET and PUT to alerter the file content. Nobody at the time just set around waiting for somebody to invert a better programming language. We just used what we had. Now I am told that I should not do that sort of thing because somebody says it is not efficient, elegant or maintainable or acceptable or kosher. Never mind that it worked.
Well, they can just GOTO [unified label].

[BC_Programmer]

I got flamed for mentioning assembler.

He asked for an example. That's not a flame. That's a valid request.

This problem the OP posted is a elementary low-level job that can easily be done at the lowest level.

Really? you can trivially implement File Input,Output, understanding different Character encodings and perform the appropriate mappings and replace a given character with another? You might respond "well, that isn't what they need" but what if in the future t hey do need it? Are they supposed to modify  that assembly to need these things that come for free with either batch or Perl, or any other scripting language at all? What kind of drugs are you on where you believe that "things should be done at the lowest level" you may as well suggest that they build, test, and use a integrated circuit board specifically for the replacement of characters. The lowest level is only the simplest level operationally; it's use and direct manipulation is anything but.

The pearl advocate told me it was impractical. 

First, for the millionth time, it's Perl. Yes, I know your speech recognition program types it in for you. But deleting one letter- or simply copy pasting one of the previously mentioned instances of the word- couldn't possibly be that difficult. Second, he has a Nick. He's hardly the only one who believes Assembly is impractical. I'd go so far as to say using it for this purpose is outright stupid and driven purely by hubris.

At one time assembly was the only tool for low-cost microcomputers.

Hey, look at me! I can insert completely redundant and irrelevant pieces of data! At one time there was a Animal that looked similar to a zebra without stripes on it's hindquarters called the Quagga. See! I can do it too!

It is trivial to open a file, change all instances of just one code and then close the file. It is one of the primitive things you learn in using Assembler with an Operation System.

Cool... so why did you consider being asked to provide an example of this trivial piece of code a flame, exactly? Clearly you could have merely produced this trivial piece of code for all to see, rather then letting it remain a whimsical fantasy in your closed off tiny world where using Assembly for small, simple tasks is somehow not stupid.

When GW-BASIC came out, there was a version of it, as I recall, that would let you open a file in RANDOM, and use GET and PUT to alerter the file content.

Ok... What the *censored* are you talking about? One paragraph your going on about how trivial it is to write stuff using assembly, the next you are talking about GW-BASIC. Do you... No... you don't think GW-BASIC is assembly, do you? Because that would explain why you believe File IO and string manipulation are trivial, because they certainly are not trivial to do in Assembly, and certainly not for somebody who isn't familiar with Assembly, at all.

Nobody at the time just set around waiting for somebody to invert a better programming language. We just used what we had. Now I am told that I should not do that sort of thing because somebody says it is not efficient, elegant or maintainable or acceptable or kosher. Never mind that it worked.
Well, they can just GOTO [unified label].

This... is still entirely irrelevant. You went "it's easy to write something like this in assembly. Also, GW-BASIC, which has nothing to do with this discussion to people who actually have a clue, uses GOTO's and people say it's not elegant or kosher." Nobody cares about your whimsical banterings about GW-BASIC, especially in the context where you are talking about assembly, which in and of itself is entirely irrelevant to the thread.