Selecting random value from Excel spreadsheet

Ask general questions here.
JimLin
Posts: 38
Joined: Mon Jun 02, 2014 4:23 pm

Selecting random value from Excel spreadsheet

Post by JimLin » Wed Jun 04, 2014 1:21 pm

Hi,

I posted this in the Automation Tools subforum on Monday and got a response that said it was going to be sent for moderation for some reason, but so far it hasn't been posted into the subforum, so I am having another go here.

I'm currently doing an evaluation of Ranorex with the aim of implementing it in our company. I am impressed with what I have seen so far, but have just hit a brick wall with this requirement.

I have a spreadsheet with a column containing a list of forenames and a column containing a list of surnames. Ultimately the intention is to add other datasets to it as well when the need arises, such as postcodes. At some point I will need to be able to select random data from a much, much larger dataset as well, so I need to establish a way of doing this.

I have successfully created an Excel connector and can use the data from each column as a variable and feed that in to forename and surname text boxes and cycle through the list as part of a test run, so 5 iterations of the same test each using the forename/surname combination in sequence, however, this isn't the way I want to use it.

Instead, rather than cycle through the entire list in sequence, what I would like to do on a test run is randomly just select one forename and one surname and use it in one test run as a random username, then on another run choose a different combination. This option doesn't seem to exist in Ranorex Studio to do it from a Recording module, so I guess it needs to be done via a code module or user code test step. I have limited experience in programming, especially in C#, but I should be able to implement it if a code snippet was provided.

I guess it’s something along the lines of reading in the entire list and then using a randomly generated integer to choose a particular name string and storing it in a variable, but coding that is beyond my skills at this point, even if it only requires basic programming skills.

I don't know if this is going in the right direction, but I have tried using the 'ExcelReadSingleCell' code from the ExcelFramework document that is referenced a lot in these forums with the intention of eventually passing a random cell reference into it, and although it seems to run without throwing any errors, I can't work out how to pass the variables strExcelFileName, strCell and strSheet into the method so it knows where to access the file and what to select.

I have attached the example spreadsheet I need to get the data from and the code from the user module I have tried to create.

I hope that all makes sense.

Regards,
James
Attachments
ExcelReadSingleCellCsharpCode.cs
ExampleListOfNames User Code Module
(8.52 KiB) Downloaded 235 times
ExampleListOfNames.zip
ExampleListOfNames.xls
(3.67 KiB) Downloaded 147 times

JimLin
Posts: 38
Joined: Mon Jun 02, 2014 4:23 pm

Re: Selecting random value from Excel spreadsheet

Post by JimLin » Thu Jun 05, 2014 3:18 pm

Hi,

I have managed to progress this issue a bit since I first submiited the question.

This is the code I have written / cobbled together from various forum posts here and elsewhere on the internet. As I said in my original post I am not a developer, but it seems to work up to a point:

Code: Select all

void ITestModule.Run(){
        
        
			// Get TestSuite Excel Data Connecter and load into the Testsuite Datacache
            Ranorex.Core.Data.DataCache MyDataCache = DataSources.Get("ExcelListOfForenames");  
  
            // Get row count for WorkSheet1 
            string myRowCount = MyDataCache.Rows.Count.ToString();

            // Convert row count into an integer
            int myRowCountAsInt = int.Parse(myRowCount);
            
			// Genterate a random integer using the row count as the 'to' value            
            Random rnd = new Random();
			int randomRowValueAsInt = rnd.Next(0, myRowCountAsInt); // creates a number between 0 and row count
            
			// Get row value in spreadsheet using random 
            public string myRandomRowContent = MyDataCache.Rows[randomRowValueAsInt].Values[0];
                        
			// Generate info for report
            Report.Info("My Row Count: " + myRowCount);
            Report.Info("My Row Count As Int: " + myRowCountAsInt);
            Report.Info("My Random Row Value As Int: " + randomRowValueAsInt);
            Report.Info("My Random Row Content: " + myRandomRowContent);


        } 
I have split the Excel into two seperate spreadsheets (ExcelListOfForenames and ExcelListOfSurnames) and created an individual data connecter for both.

I am pleased that I have got this far, but do need some assistance with a couple of things, but mainly due to my shakey understanding of C# scripting.

Issue 1:
'myRandomRowContent' is the forename string that I want to pass into the name field in a user module.It is declared as a string 'myRandomRowContent' in the code, but how do I get this into a variable or parameter that can be available to the other tests?

Issue 2:
I would like to move towards having the data in two worksheets in one Excel file. Having got as far as I can in getting the values from rows in worksheet1, I can't work out how to develop the script to allow me to access different worksheets.

Cheers,
James

mzperix
Posts: 137
Joined: Fri Apr 06, 2012 12:19 pm

Re: Selecting random value from Excel spreadsheet

Post by mzperix » Fri Jun 06, 2014 9:50 am

Hi James,

I would suggest a different way of handling this problem. Since what you want are simply randomly generated names, I would suggest to use the power of excel to achieve this randomness :)

In general, try to seapare test data from code.

A more stable solution would be to use database to store data, and do the datahandling from there.

For example, you could make a table that contains the proper data, and do a proper SQL, or better use stored procedures to generate data. Like this one: http://stackoverflow.com/questions/5806 ... ows-in-sql

Best Regards,
Zoltan

Ps. see the screenshot on what to do with excel.
Attachments
random_names.PNG
random_names.PNG (52.59 KiB) Viewed 3415 times

JimLin
Posts: 38
Joined: Mon Jun 02, 2014 4:23 pm

Re: Selecting random value from Excel spreadsheet

Post by JimLin » Mon Jun 09, 2014 3:34 pm

Hi Zoltan, sorry for the delay in getting back.

The SQL db suggestion is a good solution, but the test environment VM I am working on has so little capacity we don't have space to even install the 'light' version of SQL, but hopefully I will have access to a much larger VM in the next couple of weeks so will be able to have a look then.

I have got your Excel solution working so that it produces a random name when I open the file in Excel, but I can't work out how to trigger this function from within Ranorex. Do you have any pointers or suggestions?

Cheers, James

krstcs
Ranorex Guru
Posts: 2683
Joined: Tue Feb 07, 2012 4:14 pm
Location: Austin, Texas, USA

Re: Selecting random value from Excel spreadsheet

Post by krstcs » Mon Jun 09, 2014 4:31 pm

I would second Zoltan on the use of a DB.

One thing I would say though is that you should not install the database on your TEST system. It should be installed on a proper server. If you do something in your test that causes the system to crash, you could lose your test data as well.

Also, Microsoft suggest running SQL Server on a real (non-VM) server as SQL Server (the actual database engine) is not guaranteed to work correctly on a virtual machine in many conditions, so use caution (on a personal note, I run my instance of SQL Server Express on a virtualized server and have had no problems, but results may vary... :D).
Shortcuts usually aren't...

JimLin
Posts: 38
Joined: Mon Jun 02, 2014 4:23 pm

Re: Selecting random value from Excel spreadsheet

Post by JimLin » Mon Jun 09, 2014 6:28 pm

Thanks for your thoughts krstcs.

We are looking at an MS implementation linking Ranorex with TFS, MTM ans VS 2013 all on MS Auzure in the cloud. Hopefully getting access to a SQL server won't be too difficult.

mzperix
Posts: 137
Joined: Fri Apr 06, 2012 12:19 pm

Re: Selecting random value from Excel spreadsheet

Post by mzperix » Tue Jun 10, 2014 8:45 am

Hi JimLin,

The Excel file updates itself and generates new data every time you run the TestSuite (not updates itself within an iteration, though).

Best Regards,
Zoltan

JimLin
Posts: 38
Joined: Mon Jun 02, 2014 4:23 pm

Re: Selecting random value from Excel spreadsheet

Post by JimLin » Fri Jun 13, 2014 11:50 am

Thanks to everyone who commented on this post.

I have solved it by using the 'SQLDataConnectorSolutionDemo' provided by krstcs in this post Accessing parent test case parameter via code. It's slightly cumbersome for the limited thing I need it to do, but it will be easier if it becomes possible for Ranorex provide an easier way for parameters to be passed into stored procedures as is mentioned in the post.

On the upside I have learnt how to create stored procedures :-).