Parsing multiple files into columns in excel with special character deliminators

I have 13 text files each one with a command line with many switches. I am looking for an external (something I can execute in DOS or mouse clic) VBS snippet (NOT VBA)  that can read in an array (or external list) of files and parse the string using "/" as a deliminator in each file, and list the results in a column in excel.

Example 1: First File

String found in somefile001.txt

"D:\Program Files\PIPC\Interfaces\RDBMSPI\RDBMSPI.exe" 1 /BAD1=1 /BAD2=1 /DEB=0 /DSN=CAMPSPRINGSI /NO_INPUT_ERROR /OUTPUT="D:\Program Files\PIPC\Interfaces\RDBMSPI\Log\RDBMSPI.out" /SQL="D:\Program Files\PIPC\Interfaces\RDBMSPI\SQL" /SUCC1=0 /SUCC2=0 /USER_ODBC=odbc /PS=RDBMS /ID=1 /host=CS1-PI:5450 /pisdk=1 /maxstoptime=120 /pisdktimeout=60 /perf=8 /pisdkConTimeout=15 /UFO_SYNC="\\CS1-PI-INT2\UFO2 Files Share\RDBMSPI_RDBMS_1.dat" /UFO_TYPE=COLD /UFO_ID=1 /UFO_OtherID=2 /f=00:05:00 /f=00:05:00 /f=00:05:00

Desired output in an excel spreadsheet (Column A)
 
/BAD1=1
/BAD2=1
/DEB=0
/DSN=CAMPSPRINGSI
/NO_INPUT_ERROR
/OUTPUT="D:\Program Files\PIPC\Interfaces\RDBMSPI\Log\RDBMSPI.out" /SQL="D:\Program Files\PIPC\Interfaces\RDBMSPI\SQL"
/SUCC1=0
/SUCC2=0
/USER_ODBC=odbc
/PS=RDBMS
/ID=1
/host=CS1-PI:5450
/pisdk=1
/maxstoptime=120
/pisdktimeout=60
/perf=8
/pisdkConTimeout=15
/UFO_SYNC="\\CS1-PI-INT2\UFO2 Files Share\RDBMSPI_RDBMS_1.dat" /UFO_TYPE=COLD
/UFO_ID=1
/UFO_OtherID=2
/f=00:05:00
/f=00:05:00
/f=00:05:00

Example 1: Second File

String found in differentfile2.txt

"D:\Program Files\PIPC\Interfaces\RDBMSPI\RDBMSPI.exe" 1 /BAD1=1 /BAD2=1 /DEB=0 /DSN=CAMPSPRINGSI /NO_INPUT_ERROR /OUTPUT="D:\Program Files\PIPC\Interfaces\RDBMSPI\Log\RDBMSPI.out" /SQL="D:\Program Files\PIPC\Interfaces\RDBMSPI\SQL" /SUCC1=0 /SUCC2=0 /USER_ODBC=odbc /PS=RDBMS /ID=1 /host=CS1-PI:5450 /pisdk=1 /maxstoptime=120 /pisdktimeout=600 /perf=9 /pisdkConTimeout=15 /UFO_SYNC="\\CS1-PI-INT2\UFO2 Files Share\RDBMSPI_RDBMS_1.dat" /UFO_TYPE=COLD /UFO_ID=1 /UFO_OtherID=2 /f=00:05:00 /f=00:05:00 /f=00:05:00

Desired output in an excel spreadsheet (Column B)
 
/BAD1=1
/BAD2=1
/DEB=0
/DSN=CAMPSPRINGSI
/NO_INPUT_ERROR
/OUTPUT="D:\Program Files\PIPC\Interfaces\RDBMSPI\Log\RDBMSPI.out" /SQL="D:\Program Files\PIPC\Interfaces\RDBMSPI\SQL"
/SUCC1=0
/SUCC2=0
/USER_ODBC=odbc
/PS=RDBMS
/ID=1
/host=CS1-PI:5450
/pisdk=1
/maxstoptime=120
/pisdktimeout=600
/perf=9
/pisdkConTimeout=15
/UFO_SYNC="\\CS1-PI-INT2\UFO2 Files Share\RDBMSPI_RDBMS_1.dat" /UFO_TYPE=COLD
/UFO_ID=1
/UFO_OtherID=2
/f=00:05:00
/f=00:05:00
/f=00:05:00

Each delimited ("/) string takes up a cell in excel and continues vertically until the entire string is in list format. Closes file, opens the next file, does the same thing, and prints the parsed string on the next column in excel.
multi-file-parser.jpg
NanohurtzChromeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mvidasCommented:
Hi NC,

Do you want to list the files in the code, or do you want to be able to click/drag them into the file?

If you want to hard code them, just copy the attached code snippet into a .vbs file, alter the vFiles list at the top, and run.

If you want to click/drag files into the .vbs file in windows explorer, you'll need to make two changes to the code:

1) Remove or comment out the vFiles = ... line
' vFiles = Array("C:\somefile001.txt", "C:\somefile002.txt")

2) Change the For Each line to the following:
 For Each vFile in WScript.Arguments

The only thing I wasn't fully sure on is the first part of the file contents, before the first argument, which lists the executable. Do you really not want to include this in the excel file? If so, use this NanohurtzSplit function instead:
Function NanohurtzSplit(vFilePath)
 Dim FSO, TS, FileCont, vContents, i, vNewArray
 Set FSO = CreateObject("Scripting.FileSystemObject")
 Set TS = FSO.OpenTextFile(vFilePath, 1)
 FileCont = TS.ReadAll
 TS.Close
 Set TS = Nothing
 Set FSO = Nothing
 vContents = Split(FileCont, "/")
 ReDim vNewArray(UBound(vContents) - 1)
 For i = LBound(vContents) + 1 To UBound(vContents)
  vNewArray(i - 1) = "/" & vContents(i)
 Next
 NanohurtzSplit = vNewArray
End Function

Let me know how it fares!
Matt
Dim vArray, xlApp, i, vFile, vFiles
 vFiles = Array("C:\somefile001.txt", "C:\somefile002.txt")
   
 Set xlApp = CreateObject("excel.application")
 xlApp.Workbooks.Add

 i = 1
 For Each vFile in vFiles
  vArray = NanohurtzSplit(vFile)
  xlApp.Cells(1, i).Resize(UBound(vArray) - LBound(vArray) + 1, 1).Value = xlApp.Transpose(vArray)
  i = i + 1
 Next

 xlApp.Visible = True

Function NanohurtzSplit(vFilePath)
 Dim FSO, TS, FileCont, vContents, i
 Set FSO = CreateObject("Scripting.FileSystemObject")
 Set TS = FSO.OpenTextFile(vFilePath, 1)
 FileCont = TS.ReadAll
 TS.Close
 Set TS = Nothing
 Set FSO = Nothing
 vContents = Split(FileCont, "/")
 For i = LBound(vContents) +1 To UBound(vContents)
  vContents(i) = "/" & vContents(i)
 Next
 NanohurtzSplit = vContents
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
Fine work, Mr. Vidas.  I was just about to jump in myself, but I see it's already well-covered :)
0
mvidasCommented:
Well, VBS and Excel combined, I can't pass it up :) I even got to throw in an application.transpose!
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

NanohurtzChromeAuthor Commented:
HOT DOG!!!! <ahem> excuse me... hehe. That is some is some extremely FINE scripting there mvidas. Blew my hair back - EXACTLY what I was looking for.

You just saved me DAYS of work. If I could give you 1000 points I would. Many, MANY thanks!
0
NanohurtzChromeAuthor Commented:
A scripting ninja he is!
0
mvidasCommented:
Glad to help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.