1.

Solve : Add single comma in place of any number of spaces?

Answer»

Looking for a way to add a (SINGLE comma) in between data like below to force non comma delimited data to become comma delimited...any suggestions on how I can do this for a text document with the raw data like below in a batch ( or vb script ) so that I dont have to run this manual notepad replace 3 step process for daily data?

Deathstarr Rank 4 80 0 0
Dianniah Rank 4 80 0 0
Minnieknight Rank 4 80 0 0
Mortimur Rank 4 80 0 0
Shamadar Rank 4 80 0 0
Starrgantis Rank 4 80 0 0
Starrgazer Rank 4 80 0 0

Trying to get away from this Manual NOTEPAD " Find All Replace " Process:
(1.) Replacing ALL the (spaces) with ( , )
--- > and then in two locations I get (,,,,,,) and (,,)
(2.) I then need to replace ( ,,,,,, ) with ( , )
(3.) Then finally replace ( ,, ) with ( , )
---> So that all data is single comma delimited.

In the end I want clean comma delimited like below:

Deathstarr,Rank,4,80,0,
Dianniah,Rank,4,80,0,0,
Minnieknight,Rank,4,80,0,0,
Mortimur,Rank,4,80,0,0,
Shamadar,Rank,4,80,0,0,
Starrgantis,Rank,4,80,0,0,
Starrgazer,Rank,4,80,0,0,

Any suggestions or solutions to automate this space replacement and make it a nice comma delimited file?

If you use space(s) as the token delimiter, you can write out each line as a set of tokens with commas in between. One or more spaces will be replaced by 1 comma. I am curious why you want a trailing comma? I have included one after %%F as you can see, but I wonder why you need it.

e.g (assuming 6 tokens on each line)


Code: [Select]
if exist output.csv del output.csv
for /f "tokens=1-6 delims= " %%A in ('type input.txt') do (
echo %%A,%%B,%%C,%%D,%%E,%%F,>>output.csv
)

I started this before Dias's post, and yet notice my keen observation in the comments.... sort of, I was right about "being able to do it better" anyway... (that is, "other peeps")


Also, it removes all trailing spaces... don't think that was what you wanted...

Code: [Select]
Dim InName,OutName
Dim FSO
set FSO = CreateObject("Scripting.FileSystemObject")
if wscript.arguments.count < 2 then
wscript.echo "Spc2Comma <InputFile> <OutputFile>"
end if
InName = wscript.arguments(0)
Outname = wscript.arguments(1)

Dim InFile,OutStream
Dim InStream
Dim ReadStr
'Set InFile = FSO.GetFile(InName)

set InStream=FSO.OpenTextFile(InName,1)
Set OutStream=FSO.CreateTextFile(Outname,-1)

ReadStr = InStream.ReadAll()
ReadStr = Replace(ReadStr," ", ",")
Do Until Instr(readStr,",,") = 0
'replace two comma's with one...
ReadStr = Replace(readstr,",,",",")

Loop
Do Until Instr(readstr," ") = 0
'replace two spaces with one...
readstr = replace(readstr, " "," ")
loop


'last replace a comma and crlf combination with just a crlf...
readstr = replace(readstr,"," + Chr(13) + Chr(10) + ",",chr(13) + chr(10))
'lastly, cut off last char if it is a comma.
'this is a terrible kludge and I'm sure more vbscript able peeps could do this better...
if right(readstr,1) = "," then
wscript.echo "*censored*"
readstr = mid(readstr,1,len(readstr)-3)
end if
outStream.Write ReadStr

outstream.close
InStream.Close

Except that mine will do the grunt work in one line

Code: [Select]@echo off
set /p infile=File to process ?
set /p otfile=Output file name ?
if exist "%otfile%" del "%otfile%"
for /f "tokens=1-6 delims= " %%A in ( ' type "%infile%" ' ) do echo %%A,%%B,%%C,%%D,%%E,%%F,>>"%otfile%"
Thanks Dias & BC

And regarding the trailing comma .... this was caused by notepads replace of spaces and finding a single space in the datas formatting after the last piece of data on each line. It is not required, but also doesnt scue the output for my application.

Thanks for solving this for me so I can avoid the 3 step manual replace process, and I have saved a copy of this so that in the future I can look back and tweak it for other replacement uses.

Many thanks to both of you in your efforts! How can I do the same thing with my data set? First row is my column headings

I've been struggling with this for hours.... Any help would be greatly appreciated. Thanks in ADVANCE.

samid upn fn ln display canchpwd pwdneverexpires disabled
134-2 [emailprotected] Mike Smith 927 Mike Smith 927 yes no no Quote from: ry8200 on August 04, 2011, 01:23:26 PM

How can I do the same thing with my data set? First row is my column headings

I've been struggling with this for hours.... Any help would be greatly appreciated. Thanks in advance.

samid upn fn ln display canchpwd pwdneverexpires disabled
134-2 [emailprotected] Mike Smith 927 Mike Smith 927 yes no no

comma
« Sent to: ry8200 on: Today at 06:03:57 PM » | Reply | Quote | Delete |

Code: [Select]@echo off & setLocal EnableDELAYedeXpansion
del otfile.txt > nul
for /f "delims=" %%i in (infile.txt) do (
call :sub %%i
)
echo.
type otfile.txt
goto :eof

:sub

echo %*

:loop


echo/|set /p ="%1," >> otfile.txt

shift

if "%1"=="" (
goto :end
)
goto :loop
:end
echo. >> otfile.txt




Output:

c:\test>comma2.bat
134-2 [emailprotected] MikeSmith 927 yes no
134-2 [emailprotected] MikeSmith 927 yes no
134-2 [emailprotected] MikeSmith 927 yes no
134-2 [emailprotected] JoeLong 927 yes no no yes

134-2,[emailprotected],MikeSmith,927,yes,no,
134-2,[emailprotected],MikeSmith,927,yes,no,
134-2,[emailprotected],MikeSmith,927,yes,no,
134-2,[emailprotected],JoeLong,927,yes,no,no,yes,

c:\test>

ref:

http://stackoverflow.com/questions/4479734/echo-with-no-new-line-cr-lf

That kind of work can be done by a spreadsheet program.1. This thread has been revived by ry8200 after a gap of over two and a half years. ry8200, if you are genuine, and not Bill's sock puppet, as certain internal evidence suggests, start your own thread. Better still, read this one, where the solution was clearly described by my friend Dias de Verano, in February 2009.

2. Fred35 is, as I suspected ever since he first appeared, Billrich.

Honestly Salmon Trout - Sock puppet??? Are you kidding me... If this is how a new person is treated in this forum, then I'll take my request somewhere else. I am a real person with an issue that falls in line with this post and the previous issue that DaveLembke had; so I thought it would make sense to revive this post.

Anyway, moving on.

The new issue I'm having is two of the fields of data, called "fn" and "display" have spaces in them (for example "Display" = "John Smith D1234567"), that I need to keep, but I want to replace all the other spaces with a comma.

The data pulled from my DSQUERY is:
samid upn fn ln display canchpwd pwdneverexpires disabled
123-2 [emailprotected] John Smith D1234567 John Smith D1234567 yes no no
456-2 [emailprotected] Mike Smith U1234567 Mike Smith U1234567 yes no no

The output would need to look like this:
samid,upn,f,ln,display,canchpwd,pwdneverexpires,disabled
123-2,[emailprotected],John Smith,D1234567,John Smith D1234567,yes,no,no
456-2,[emailprotected],Mike Smith,U1234567,Mike Smith U1234567,yes,no,no Did you read what was said about starting your own thread? You have revived a thread from 2009.
Anyhow,

@echo off
setlocal enabledelayedexpansion
set InputFileName=Input.txt
set OutputFileName=output.csv
set line=1
if exist "%OutputFileName%" del "%OutputFileName%"
for /f "tokens=1-11 delims= " %%A in ('type "%InputFileName%"') do (
if !line! equ 1 (
set output=%%A,%%B,%%C,%%D,%%E,%%F,%%G,%%H
) else (
set output=%%A,%%B,%%C %%D,%%E,%%F %%G %%H,%%I,%%J,%%K
)
echo !output! >> "%OutputFileName%"
set /a line+=1
)
echo Input:
echo.
type "%InputFileName%"
echo.
echo Output:
echo.
type "%OutputFileName%"


Input:

samid upn fn ln display canchpwd pwdneverexpires disabled
123-2 [emailprotected] John Smith D1234567 John Smith D1234567 yes no no
456-2 [emailprotected] Mike Smith U1234567 Mike Smith U1234567 yes no no

Output:

samid,upn,fn,ln,display,canchpwd,pwdneverexpires,disabled
123-2,[emailprotected],John Smith,D1234567,John Smith D1234567,yes,no,no
456-2,[emailprotected],Mike Smith,U1234567,Mike Smith U1234567,yes,no,no


Discussion

No Comment Found

Related InterviewSolutions