How do I take captured text and put it into Excel?

[rking66]

I am hoping that someone can help me grasp the method of taking simple text from a capture file and putting it into Excel so that the data can be manipulated or reported on? Here is one basic scenario... I want to take the following text and put it in Excel, via Aspect scripting:

REQ SLT
ANALOGUE TELEPHONES 639 LEFT 47 USED 592
CLASS TELEPHONES 0 LEFT 0 USED 0
DIGITAL TELEPHONES 2000 LEFT 149 USED 1851
DECT USERS 0 LEFT 0 USED 0
IP USERS 96 LEFT 28 USED 68
BASIC IP USERS 0 LEFT 0 USED 0
TEMPORARY IP USERS 0 LEFT 0 USED 0
DECT VISITOR USER 0 LEFT 0 USED 0
ACD AGENTS 80 LEFT 58 USED 22

PCA 0 LEFT 0 USED 0
ITG ISDN TRUNKS 0 LEFT 0 USED 0
H.323 ACCESS PORTS 96 LEFT 0 USED 96
AST 20 LEFT 20 USED 0
SIP CONVERGED DESKTOPS 0 LEFT 0 USED 0
SIP CTI TR87 0 LEFT 0 USED 0
SIP ACCESS PORTS 0 LEFT 0 USED 0
RAN CON 14 LEFT 14 USED 0
MUS CON 40 LEFT 40 USED 0

TNS 32760 LEFT 29553 USED 3207
ACDN 24000 LEFT 23985 USED 15
AML 16 LEFT 15 USED 1
IDLE_SET_DISPLAY CSC
LTID 32760 LEFT 32760 USED 0
RAN RTE 512 LEFT 512 USED 0
ATTENDANT CONSOLES 32760 LEFT 32760 USED 0
BRI DSL 10000 LEFT 10000 USED 0
DATA PORTS 32760 LEFT 32760 USED 0
PHANTOM PORTS 32760 LEFT 32612 USED 148
TRADITIONAL TRUNKS 32760 LEFT 32480 USED 280
DCH 255 LEFT 246 USED 9



I think that if I can wrap my brain around how to put the above text into their own separate cell arrays, I should be able to apply that knowledge to other PBX output like:

FEAT

FEAT CUST TOTAL SL1 500 2500 3000 2000 3900 ISET DCS PCA ATT BRI

SETS 00 CNT 2687 0 15 725 0 47 1832 68 0 0 0 0


NACT
REQ CNT
CUST 0
DATE
PAGE
DES
FEAT DCFW
FEAT

FEAT CUST TOTAL SL1 500 2500 3000 2000 3900 ISET DCS PCA ATT BRI

DCFW 00 CNT 144 0 0 144 0 0 0 0 0 0 0 0


NACT
REQ CNT
CUST 0
DATE
PAGE
DES
FEAT ACD
FEAT

FEAT CUST TOTAL SL1 500 2500 3000 2000 3900 ISET DCS PCA ATT BRI

ACD 00 CNT 50 0 0 0 0 44 6 0 0 0 0 0


Thanks in advance for any assistance that anyone can give.

 

[kodr]

How far are you on this? Do you have a working shell to capture the above data in place already?

Check out Knob's site (

http://www.aspectscripting.com)

I believe. He has some good examples there.

If you don't find what you need there, I could probably scrounge up a sample program or two for a demonstration.

 

[kodr]

Lol, I alway's overlook the easiest solutions....

Quick and dirty way to do what you want? Output your data as a .CSV file. Excel can open that natively. Unless you have a need to have Excel open and running, and displaying the data as the program is being executed (can't think of a reason why offhand.)

Now, as far as breaking your data up into discreet values? Are the values seperated by spaces or tabs? If it is by tabs, you can use strtok (string to token) to break the data into individual elements.

`t is the symbol for a tab (the back tick under the tilde symbol, not a single quote.)

 

[knob]

Just to add on to what kodr said, DDE is what you would use to go from Procomm to Excel (if you needed to!) but I would look at his CSV suggestion first and see if that will work for your needs. I have a few DDE sample scripts on my site, and there are several threads and samples in the forums here as well once you determine if that is the method you need to use.

http://www.aspectscripting.com

 

[rking66]

Thanks for the quick responses, Kodr and Knob! I actually do have a fair amount of this scripted to capture the data into text files, but it hadn't occurred to me that the data could be saved as csv? Great idea! I am going to give that a shot in the morning and I'll post the results. I am also going to check out Knob's site to look at the DDE sample scripts, too. Thank you!

 

[jamie2]

rking66... Most of what you want can be gathered by printing your TNB in LD 20 and saving it as a text file. Then go to

http://www.pepedog.com/meridian

and download the mv.xls file and it will parse the information into a spreadsheet for you.

 

[rking66]

Thanks Jamie. I actually do make use of Dave Higham's Excel parser utilities quite often, but in many cases running an entire TNB can take several hours. My reporting needs don't need to be quite so granular, and since I have to run these 'port counts' from hundreds of PBXs, time is a big factor. I'm still looking for a workable solution.