Insert comma between names on a list

  • Thread starter Thread starter Highlander
  • Start date Start date
H

Highlander

Hello. I have an Excel spreadsheet with one column of server names. I
export that to a text file, and the list looks like this:

AB150-ATTS01
AB172-ATTS03
AC1AA-ATTS01
AC1DC-ATTS03
AC2BF-ATTS03
AD3C2-ATTS02
AD5A7-ATTS01
AD5CA-ATTS01

In order to process this list of names, I need it in the following
format - one single line, with the names separated by a comma:

AB150-ATTS01,AB172-ATTS03,AC1AA-ATTS01,AC1DC-ATTS03,AC2BF-ATTS03,AD3C2-ATTS02,AD5A7-ATTS01,AD5CA-ATTS01

The list is 2000 names long. It's rather tedious to insert the comma
and get everything on one single line manually. Is there a script that
will do this for me?

Any help would be greatly appreciated. Thanks!

- Dave
 
You can do something along this line.

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

dim fso : set fso = CreateObject("Scripting.FileSystemObject")
dim f : set f = fso.GetFile(server.MapPath("txtDB.txt"))
dim ts : set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

do while not ts.AtEndOfStream
if len(myText) > 0 then myText = myText & ","
myText = myText & trim(ts.ReadLine)
loop

call CreateTextFile(myText,"txtCommatized.txt")


sub CreateTextFile(byVal text, byVal filename)
dim fso, MyFile
set fso = CreateObject("Scripting.FileSystemObject")
set MyFile = fso.CreateTextFile(server.MapPath(filename),true)
MyFile.WriteLine(text)
MyFile.Close
set fso = nothing
end sub
 
Here's the answer to the first half of your problem:

'Start of script

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

dim fso : set fso = server.CreateObject("Scripting.FileSystemObject")
dim f : set f = fso.GetFile(PATHOFTEXTFILE)
dim ts : set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)

dim sTextCommatized

do while not ts.AtEndOfStream
if len(sTextCommatized) > 0 then sTextCommatized = sTextCommatized
& ","
sTextCommatized = sTextCommatized & ts.ReadLine
loop

'the variable "sTextCommatized" should now contain yoru comma-delimited
data, all you need to do is write it to a file which is trivial.
 
Highlander said:
Hello. I have an Excel spreadsheet with one column of server names. I
export that to a text file, and the list looks like this:

AB150-ATTS01
AB172-ATTS03
AC1AA-ATTS01
AC1DC-ATTS03
AC2BF-ATTS03
AD3C2-ATTS02
AD5A7-ATTS01
AD5CA-ATTS01

In order to process this list of names, I need it in the following
format - one single line, with the names separated by a comma:

AB150-ATTS01,AB172-ATTS03,AC1AA-ATTS01,AC1DC-ATTS03,AC2BF-ATTS03,AD3C2-ATTS0
2,AD5A7-ATTS01,AD5CA-ATTS01

The list is 2000 names long. It's rather tedious to insert the comma
and get everything on one single line manually. Is there a script that
will do this for me?

Any help would be greatly appreciated. Thanks!

- Dave
Click to expand...

Will this help? Watch for word-wrap.

Option Explicit
Const cVBS = "commas.vbs"
Const cOTF = "commas.txt"
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objOTF
Set objOTF = objFSO.OpenTextFile(cOTF,1)
Dim strOTF
strOTF = objOTF.ReadAll
Set objOTF = Nothing
strOTF = Replace(strOTF,vbCrLf,",")
If Right(strOTF,1) = "," Then strOTF = Left(strOTF,Len(strOTF)-1)
Set objOTF = objFSO.OpenTextFile(cOTF,2,True)
objOTF.Write(strOTF)
Set objOTF = Nothing
Set objFSO = Nothing
MsgBox "Done!",vbInformation,cVBS
 
XMLallUPinMYeye,

I've tried your script and I get the following runtime error:

Line: 10
Char: 1
Error: Bad file mode

The only change I've made to your script was inserting the file name
"test.txt":
dim f : set f = fso.GetFile("test.txt")

Btw, line 10 is:
do while not ts.AtEndOfStream

The file test.txt is in the same folder as the script.
 
XMLallUPinMYeye,

I've tried your script and I get the following runtime error:

Line: 10
Char: 1
Error: Bad file mode

The only change I've made to your script was inserting the file name
"test.txt":
dim f : set f = fso.GetFile("test.txt")
Click to expand...

Rather than just "test.txt", use server.mappath("test.txt")
 
(e-mail address removed),

I've tried that and a few other variations; still wouldn't work.

Actually, I was able to get McKirahan's script to work. I've made a few
changes to it - works great!

Thanks to all who responded!!

Modified script:

Option Explicit
Const cVBS = "Commatize Me.vbs"

Dim fFile
fFile = InputBox ("Type the name of the file you want to
commatize:","Commatize Me.vbs",fFile)
if fFile = "" Then wscript.quit

Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objOTF
Set objOTF = objFSO.OpenTextFile(fFile,1)
Dim strOTF
strOTF = objOTF.ReadAll
Set objOTF = Nothing
strOTF = Replace(strOTF,vbCrLf,",")
If Right(strOTF,1) = "," Then strOTF = Left(strOTF,Len(strOTF)-1)
Set objOTF = objFSO.OpenTextFile(fFile,2,True)
objOTF.Write(strOTF)
Set objOTF = Nothing
Set objFSO = Nothing
Set fFile = Nothing
MsgBox "You've been commatized!",vbInformation,cVBS
 
Back
Top