Getting data from excel and doing tests with it

[NAKI]

Hello
I've just downloaded securecrt script guide and using it with a script i've found on this forum (i.e ExcelSpreadsheets-ReadingAndWriting.vbs)
i've come up with a script that (I think..) get to an excel file and run a telnet to a router and a ping

now my question is since i can't really test it on our actual server, could you show me
1- a way to test or simulate it
2- the probable errors i've made

thank you

# $language = "VBScript" 
# $interface = "1.0" 

' path of excel file
Dim g_shell
Set g_shell = CreateObject("WSCript.Shell")
Dim g_strMyDocs, g_strSpreadSheetPath
g_strMyDocs = g_shell.SpecialFolders("MyDocuments")
g_strSpreadSheetPath = g_strMyDocs & "\ExcelData.xls"


' This specific example uses an excel spreadsheet that has the
' following general format:
'          A       |   B         |     C    		|     D  	   |     E     |    F    | 
'   +--------------+-------=-----+------------------+--------------+-----------+---------+
' 1 | IP Address   | IP Gateway  | VLAN Management  | VLAN Service | IP Edge   |  Port	 | 
'   +--------------+-------------+------------------+--------------+-----------+---------+
' 2 | 192.168.0.1  |             |         			| 		  	   | 		   |  	     |
'   +--------------+-------------+------------------+--------------+-----------+---------+
' 3 | 192.168.0.2  |             |       			| 	    	   | 		   |   	     |
'   +--------------+-------------+------------------+--------------+-----------+---------+
' 4 | 192.168.0.3  |             |      		    | 	    	   | 		   |   		 |
'   +--------------+-------------+------------------+--------------+-----------+---------+
Dim g_objExcel
Dim g_IP_COL, g_PORT_COL, g_USER_COL, g_PASS_COL, g_ACT_COL
' Convert Letter column indicators to numerical references
g_IP_COL           = Asc("A") - 64
g_IPG_COL          = Asc("B") - 64
g_VLANM_COL        = Asc("C") - 64
g_VLANS_COL        = Asc("D") - 64
g_IPEDGE_COL       = Asc("E") - 64
g_PORT_COL         = Asc("E") - 64



Sub Main()

    Set g_objExcel = CreateObject("Excel.Application")
    Dim objWkBook
    Set objWkBook = g_objExcel.Workbooks.Open(g_strSpreadSheetPath)
    Dim objSheet
    Set objSheet = objWkBook.Sheets(1)

    Dim nRowIndex
    nRowIndex = 2
    Dim strIP, strIPG, strVLANM, strVLANS, strIPEDGE, strPORT
   
    Do
        ' If you find an empty value in column #1, exit the loop
        strIP = Trim(objSheet.Cells(nRowIndex, g_IP_COL).Value)
        If strIP = "" Then Exit
        
        Else
        
        	strPort      = Trim(objSheet.Cells(nRowIndex, g_PORT_COL).Value)
        	strVlanM     = Trim(objSheet.Cells(nRowIndex, g_VLANM_COL).Value)
            strVlanS     = Trim(objSheet.Cells(nRowIndex, g_VLANS_COL).Value)
            strIPGateway = Trim(objSheet.Cells(nRowIndex, g_IPG_COL).Value)
            strIPEdge    = Trim(objSheet.Cells(nRowIndex, g_IPEDGE_COL).Value)
            
    			 
				If crt.Session.Connected Then crt.Session.Disconnect
				crt.Screen.Synchronous = True

				On Error Resume Next
		
				'Connection to the equipement per telnet port 23
				'TELNET host /vrf NAME ???
				crt.Session.Connect "/TELNET" & strIP  
				crt.Screen.WaitForString ">"
				crt.Screen.Send "enable" & vbCr
				crt.Screen.WaitForString "#" & chr(13)
				
				'otherwise write in an excel that it can't telnet
				' Capture error code and description (if any)
				nError = Err.Number
				strErr = Err.Description
				On Error Goto 0
					If nError <> 0 Then
					' Handle the error (log to a file, etc.) msgbox ???
					
                   
					Else
            
                    ' if telnet is ok
					
					'TEST 1: compare parameters of service of client with th running config
					'acces a la base
					crt.Screen.Send "show running-config"
					crt.Screen.WaitForString "--More--"
					Do crt.Screen.Send chr(10)
					Loop While crt.Screen.WaitForString "#"
					'get the intended parameters
					'write down faults
            		
            
            		
					'TEST 2: ping the @ IPedge 
					bpingstatus= False
					crt.Screen.Send "ping 172.x.x.x" & vbCr
					If crt.Screen.WaitForString ("!",5) & chr(13) Then
					bpingstatus = True
					If bpingstatus = True Then strstatus = "Succes"
					Else
				    strstatus = "Fail"		
					End If
					'write it down in an excel file
			
            
    		nRowIndex = nRowIndex + 1 
    	End If 
    While EndOfFile Excel

[bgagnon]

Hi NAKI,

now my question is since i can't really test it on our actual server, could you show me
1- a way to test or simulate it

There is not really any way for us to know your network environment or the restrictions of your situation well enough to tell you a way to test your script. You will need to develop an environment that is conducive to testing the script as you refine it.

2- the probable errors i've made

For testing scripts in general, you would want to take note of the lines referenced in the error message if you receive any errors when you run the script. Or, if the script appears to be hanging, choose Cancel from the Script menu and then take note of referenced line numbers.

Unfortunately, the script is greatly changed from the example script and seems a bit incomplete, so it is difficult even to provide any helpful suggestions at this juncture.

Is there a reason you removed the Connect() function entirely?

The Connect() function included the error handling for the script. Some other basic points I noticed:

• Lines 31 and 32 both reference Column E
• Line 69 add space after "/TELNET" (ie: "/TELNET ")
• Line 72 Waiting for a hash followed by a CR
• While you have several comments regarding writing the results, it does not appear there is any writing to Excel yet in this script

The entire Loop construct for lines 89-92 does not seem correct. At a minimum you should use WaitForStrings or add a timeout or it is likely the condition may never be met.

The concept of handling a "--More--" prompt situation is covered in the Scripting Essentials manual (section 4.3). While that example captures the data using ReadString(), you can apply the same concept with WaitForStrings.

Once you have refined the script and, more importantly, created an atmosphere where you can test it, if you have any specific questions or issues that occur, feel free to post a new version of the script and include a summary of the behavior.

[NAKI]

Hello

Thanks for your reply,

i'm restarting a new script from the scratch, it seems i'm lost and don't have really any skills in scripting

The basic corrections are taken care of
and yes the script don't write in an Excel file (a new one) yet !

is there any other example that i can use to get data from an Excel file and process it then open a new Excel file and write results?

thanks

[bgagnon]

Hi NAKI,

The "ExcelSpreadsheets-ReadingAndWriting.vbs.txt" example would be the correct example to get you started, as explained in this other forum post.

The If Not Connect() function has some elements you can still use. The main thing is just to build in the Sends for credentials and the resulting WaitForString prompts you expect since you are using Telnet, not SSH.

[NAKI]

Hello thank you i'm using it

but i have a little issue, how i can actually tell to securecrt that it reached an end of excel file ??

something like

Do


'processing blablbalabla

nRowIndex = nRowIndex + 1        
    Loop   or  Loop While objSheet.AtEndOfStream  ???? to indicate the end of excel file as break condition for the loop

Thank you
Regards
NAKI

[NAKI]

Hello again

I found it but it's not the perfect solution
the condition is when he found an empty cell, but it won't work for example if you have discontinous cells....

Do 
'bblqbql

 Loop  While strIPA <> ""

with strIPA =Trim(objSheet.Cells(nRowIndex, g_IPA_COL).Value)
thanks anyaway

[bgagnon]

Hi NAKI,

As in the original Excel example, you would want to include a check for valid data inside a Do ... Loop.

In other words, if there are no more lines to read from the Excel file, there's no need to continue.

Do 

    ' If you find an empty value in column #1, exit the loop
    strIP = Trim(objSheet.Cells(nRowIndex, g_IP_COL).Value)
    If strIP = "" Then Exit Do

    .
    .
    .

    ' All other work
    .
    .
    .

Loop

Regarding your later post, I am not sure what you mean by discontinous cells.

What problem are you trying to solve at this point?

What do you mean by "discontinous cells"?

[bgagnon]

Hi NAKI,

After conferring with a colleague, I think I know what you are trying to do. It sounds like you want to handle the possibility of blank rows in your Excel spreadsheet.

The suggestion is to have a counter that gets incremented when a blank row is encountered and "zeroed out" if a non-blank row is encountered (valid data). Then you can exit the loop if the counter reaches whatever threshold you determine. For example, if you would expect you have reached the end of data if 7 empty rows are found (or 9, 10, etc.).

PHP Code:

Do

    ' If you find an empty value in column #1, exit the loop '
    strIP = Trim(objSheet.Cells(nRowIndex, g_IP_COL).Value)

    If strIP = "" Then 
        nConsEmptyRows = nConsEmptyRows + 1
    Else
        nConsEmptyRows = 0
    End If 

    If nConsEmptyRows >= 7 Then Exit Do
    .
    .
    .

    ' Continue Work '
    .
    .
    .

Loop 


Another possibility (if you are also the Excel Spreadsheet author) is to put a tag at the bottom of the spreadsheet after the last legitimate data field that says something unique you can queue on, like __END__OF__DATA__. Then, if you detect that tag, exit the loop.

[upman_jeffrey]

Moderators: if it is wrong for open a thread from 2012...let me know and I will open a new thread.

Long story short, our group is looking to use CRT and VB (maybe python) to create base configuration file for building out Cisco devices. And data is listed in a XLS file, however the data is row base (not column base like the example.) As we are already a bit behind on getting the scripts build, I want to the if the example config (listed in this thread) works without a ton of changes (or does it just need a ton of changes?)

Our XLS looks like:
' following general format:
' | A | B | C | D | E | F |
' --------------+-------------------------------------------------------------------------
' 1 | | Site NameA | Site NameB | Site NameC | Site NameD | Site NameE |
' --------------+-------------------------------------------------------------------------
' 2 | HostName | NameA | NameB | NameC | NameD | NameE |
' --------------+-------------------------------------------------------------------------
' 3 | IP Address | 192.168.0.1 | 192.168.1.1 | 192.168.2.1 | 192.168.3.1 | 192.168.4.1 |
' --------------+-------------------------------------------------------------------------
' 4 | 1IP Gateway | 192.168.0.1 | 192.168.1.1 | 192.168.2.1 | 192.168.3.1 | 192.168.4.1 |
' --------------+-------------------------------------------------------------------------
' 5 | Subnet Mask| /24 | /24 | /24 | /24 | /24 |
' --------------+-------------------------------------------------------------------------
' 6 | Radius_Key | aaaaa | bbbbb | cccc | dddddd | eeee |
' --------------+-------------------------------------------------------------------------

[ekoranyi]

Hi upman_jeffrey,

Is this an Excel sheet you can modify? The easiest option may be to simply modify the Excel sheet to be row based. I found a link from Office Support that demonstrates how to easily rotate your data in Excel from columns to rows. Click Here to follow the link.

Is this an option that will work for you?

[upman_jeffrey]

Originally Posted by ekoranyi

Hi upman_jeffrey,

Is this an Excel sheet you can modify? The easiest option may be to simply modify the Excel sheet to be row based. I found a link from Office Support that demonstrates how to easily rotate your data in Excel from columns to rows. Click Here to follow the link.

Is this an option that will work for you?

That is one of the options I have been thinking about. Not knowing that much about VB and excel, thought there maybe an easy way to change the VB code to match the current setup of the excel document.

Will look into it.

[ekoranyi]

Hi upman_jeffrey,

I look forward to hearing your results. It appears that transposing the data from columns to rows is nearly as simple as copying and pasting the data to a new sheet.