Welcome to the VanDyke Software Forums

Join the discussion today!


Go Back   VanDyke Software Forums > Scripting

Notices

Reply
 
Thread Tools Rate Thread Display Modes
  #1  
Old 04-18-2012, 07:34 AM
NAKI NAKI is offline
Registered User
 
Join Date: Apr 2012
Posts: 5
Getting data from excel and doing tests with it

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
Code:
# $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
Reply With Quote
  #2  
Old 04-18-2012, 12:07 PM
bgagnon bgagnon is offline
VanDyke Technical Support
 
Join Date: Oct 2008
Posts: 4,636
Hi NAKI,

Quote:
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.

Quote:
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.
__________________
Thanks,
--Brenda

VanDyke Software
Technical Support
support@vandyke.com
(505) 332-5730
Reply With Quote
  #3  
Old 04-19-2012, 08:59 AM
NAKI NAKI is offline
Registered User
 
Join Date: Apr 2012
Posts: 5
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
Reply With Quote
  #4  
Old 04-19-2012, 09:59 AM
bgagnon bgagnon is offline
VanDyke Technical Support
 
Join Date: Oct 2008
Posts: 4,636
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.
__________________
Thanks,
--Brenda

VanDyke Software
Technical Support
support@vandyke.com
(505) 332-5730

Last edited by jdev; 03-02-2018 at 05:42 PM.
Reply With Quote
  #5  
Old 04-26-2012, 08:21 AM
NAKI NAKI is offline
Registered User
 
Join Date: Apr 2012
Posts: 5
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

Code:
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
Reply With Quote
  #6  
Old 04-26-2012, 09:08 AM
NAKI NAKI is offline
Registered User
 
Join Date: Apr 2012
Posts: 5
Smile

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....

Code:
Do 
'bblqbql

 Loop  While strIPA <> ""
with strIPA =Trim(objSheet.Cells(nRowIndex, g_IPA_COL).Value)
thanks anyaway
Reply With Quote
  #7  
Old 04-26-2012, 09:16 AM
bgagnon bgagnon is offline
VanDyke Technical Support
 
Join Date: Oct 2008
Posts: 4,636
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.

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 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"?
__________________
Thanks,
--Brenda

VanDyke Software
Technical Support
support@vandyke.com
(505) 332-5730
Reply With Quote
  #8  
Old 04-26-2012, 10:24 AM
bgagnon bgagnon is offline
VanDyke Technical Support
 
Join Date: Oct 2008
Posts: 4,636
Lightbulb

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(nRowIndexg_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.
__________________
Thanks,
--Brenda

VanDyke Software
Technical Support
support@vandyke.com
(505) 332-5730
Reply With Quote
  #9  
Old 09-18-2017, 12:39 AM
upman_jeffrey upman_jeffrey is offline
Registered User
 
Join Date: Sep 2017
Posts: 2
requesting help

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 |
' --------------+-------------------------------------------------------------------------
Reply With Quote
  #10  
Old 09-18-2017, 09:01 AM
ekoranyi ekoranyi is offline
VanDyke Technical Support
 
Join Date: Jan 2017
Posts: 654
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?
__________________
Thanks,
--Eric

VanDyke Software
Technical Support
support@vandyke.com
(505) 332-5730
Reply With Quote
  #11  
Old 09-18-2017, 09:24 AM
upman_jeffrey upman_jeffrey is offline
Registered User
 
Join Date: Sep 2017
Posts: 2
Quote:
Originally Posted by ekoranyi View Post
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.
Reply With Quote
  #12  
Old 09-18-2017, 09:31 AM
ekoranyi ekoranyi is offline
VanDyke Technical Support
 
Join Date: Jan 2017
Posts: 654
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.
__________________
Thanks,
--Eric

VanDyke Software
Technical Support
support@vandyke.com
(505) 332-5730
Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -6. The time now is 05:34 PM.