NanohurtzChrome
asked on
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\RDBM
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\RDBM
/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\U
/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\RDBM
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\RDBM
/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\U
/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
Example 1: First File
String found in somefile001.txt
"D:\Program Files\PIPC\Interfaces\RDBM
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\RDBM
/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\U
/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\RDBM
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\RDBM
/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\U
/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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Patrick Matthews
Fine work, Mr. Vidas. I was just about to jump in myself, but I see it's already well-covered :)
Well, VBS and Excel combined, I can't pass it up :) I even got to throw in an application.transpose!
ASKER
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!
You just saved me DAYS of work. If I could give you 1000 points I would. Many, MANY thanks!
ASKER
A scripting ninja he is!
Glad to help!