Import and Export data from excel or csv

[md amir]

Hi,

Is there any feature that comes along with Telerik which will help me in reading and writing data from an excel sheet or a CSV file? I tried to read from the csv file with my own piece of code. The problem that i'm facing is, it reads only the first row and doesnt move on to the subsequent rows. The code that I wrote works fine in another application. It just fails here. Could you please throw some light on this issue and help me resolve it ASAp?

Thanks,
Amir

[Missing User]

Hi md amir,

There is a built in Data Driven feature for WebUI using Excel sheets. Please see the attached screen shots for the first steps in adding a data source in both QA and Dev editions.

This video describes the feature in the previous version of WebUI, but the portion of video that describes how to data bind a test step to a column in the data source is still valid.

I'm not sure why the code you have for reading/writing from the spreadsheet might not be working, but you can use the built in WebUI data driven feature that will iterate the entire test for each data row.

It would probably be easiest to modifiy or create a new Excel spreadsheet for this type of testing, but if you wanted to to access multple columns, please bind the test step using the syntax: $(ColName1) $(ColName2)...etc

If you wanted to access multiple rows in the data source, please try the following in a code behind file:

[CodedStep("MyCustom Step Description")]
public void MyCustomStep()
{
DataRow r1 = this.ExecutionContext.DataSource.Rows[0];
String col1 = r1.ItemArray[0].ToString();
DataRow r2 = this.ExecutionContext.DataSource.Rows[1];
String col2 = r1.ItemArray[0].ToString();
}

Regards,
Nelson Sin
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[md amir]

Hi Nelson,

Thanks a lot for suggestion. Just want know what is this 'DataSource' and one more thing i want to share with you is i am using Telerik through VSTS so i am unable to see the same menu that you have shared through these photos. So just let me know throug VSTS how can i get the same menu.

Regards,
Amir

[Cody]

Hi md amir,

Certainly! You start with the VS-AddDataSource.png screen shot for adding the data source. Once you've fully defined your datasource follow these steps to bind a test step property to a value from the datasource:

1. Click on the test step you want to bind data to.
2. In the Properties window you'll see a (Bindings) property. Click on it and then click on the dropdown arrow that appears to open the bindings dialog.
3. Click on the test step property you want to bind to (some test steps have multiple properties you can bind).
4. Replace the test "type expression here" with something like $(Col1). What you enter depends on the actual column names of your datasource. $(Col1) assumes I have a column named "Col1" in my datasource. If your column is named "Input1" then you would enter $(Input1) instead.
5. Click Set to make it active.

That should be it. Let me know if I can be of further assistance.

Sincerely yours,
Cody
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Jinesh Kurian]

Cody,

I dont find any button as shown in vs-adddatasource.png. Does it matter which version of telerik that i'm using?

[Cody]

Hello Jinesh Kurian,

This button and feature has been in the product since our 2010.1.4.12 release just over a month ago. What version are you running? Prior to this version you can still use data binding, but the method is different.

Best wishes,
Cody
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Jinesh Kurian]

Cody,

Thanks for the response. I'm using a version prior to the one that you mentioned. Could you please help me out with the steps that i need to follow to get the data from CSV file?

The version I could see is 2.0.9.0

Thanks,
Jinesh

[Cody]

Hi Jinesh Kurian,

Ah, thanks. In that case this video described how to implement a data bound test applies to your version. http://tv.telerik.com/webui+test+studio/video/webui-test-basics-+-data-driven-tests.

All the best,
Cody
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Jinesh Kurian]

Hello Cody,

Thanks for the video. It was helpful in understanding how it works. But my requirement is a little different. Let me explain in detail.

There are 5 scenarios to be executed, for which atleast 5 steps are common. I copied the common code and created a new function CommonSteps(). Later, I copied the remaining part of the code and made them into a single step. Now, all these 5 scenarios are in a .cs file (not in aii.cs file). I moved the scenarios to a Scenarios.cs file with 5 different functions. So there are totally 6 functions (5 for the scenarios and 1 for the common steps) and retained one step in aii.cs file. I want only one step visible in the test explorer and thats how it shows there too. Inside this step in aii.cs file, i call the methods of the scenarios. I create an object for the Scenarios.cs file in aii.cs and try to access them. They work beautifully if i hardcode the fields that need to be entered in the UI. But my requirement is such that, I should get the value from a CSV. Here is where i face the problem. Please have a look at the code that I'm trying to use.

[TestMethod]
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.CSV", "|DataDirectory|\\AA.csv", "AA#csv", DataAccessMethod.Sequential), DeploymentItem("AA.csv")]
        public void AA_Code1()
        {


            // radcombobox: Type 'A AA' into PARTEditableTextBoxPickertextbox
            _Pages.AccountScreen.SilverlightApp.PARTEditableTextBoxPickertextbox.Text = "";
                       _Pages.AccountScreen.SilverlightApp.PARTEditableTextBoxPickertextbox.User.TypeText(TestContext.DataRow["ActionCode"].ToString(), 50);
            _ActiveBrowser.Manager.Desktop.KeyBoard.KeyPress(ArtOfTest.WebAii.Win32.KeyBoard.KeysFromString("Enter"), 150, 1);
            System.Threading.Thread.Sleep(10000);
            _ActiveBrowser.Manager.Desktop.KeyBoard.KeyPress(ArtOfTest.WebAii.Win32.KeyBoard.KeysFromString("Tab"), 150, 2);
            _ActiveBrowser.Manager.Desktop.KeyBoard.KeyPress(ArtOfTest.WebAii.Win32.KeyBoard.KeysFromString("Ctrl+A"), 150, 1);
            _Pages.AccountScreen.SilverlightApp.PARTEditableTextBoxPickertextbox.User.TypeText(TestContext.DataRow["BeginDate"].ToString(), 100);
            _ActiveBrowser.Manager.Desktop.KeyBoard.KeyPress(ArtOfTest.WebAii.Win32.KeyBoard.KeysFromString("Tab"), 150, 1);
            _ActiveBrowser.Manager.Desktop.KeyBoard.KeyPress(ArtOfTest.WebAii.Win32.KeyBoard.KeysFromString("Ctrl+A"), 150, 1);
                        _Pages.AccountScreen.SilverlightApp.PARTEditableTextBoxPickertextbox.User.TypeText(TestContext.DataRow["EndDate"].ToString(), 100);
            _ActiveBrowser.Manager.Desktop.KeyBoard.KeyPress(ArtOfTest.WebAii.Win32.KeyBoard.KeysFromString("Tab"), 150, 1);
            _ActiveBrowser.Manager.Desktop.KeyBoard.KeyPress(ArtOfTest.WebAii.Win32.KeyBoard.KeysFromString("Ctrl+A"), 150, 1);
                        _Pages.AccountScreen.SilverlightApp.PARTEditableTextBoxPickertextbox.User.TypeText(TestContext.DataRow["Comment"].ToString(), 100);
            _ActiveBrowser.Manager.Desktop.KeyBoard.KeyPress(ArtOfTest.WebAii.Win32.KeyBoard.KeysFromString("Tab"), 150, 1);
            _ActiveBrowser.Manager.Desktop.KeyBoard.KeyPress(ArtOfTest.WebAii.Win32.KeyBoard.KeysFromString("Enter"), 150, 1);
            _ActiveBrowser.WaitUntilReady();
        }

When I use the above code in Scenarios.cs file, it fails at the point             _Pages.AccountScreen.SilverlightApp.PARTEditableTextBoxPickertextbox.User.TypeText(TestContext.DataRow["BeginDate"].ToString(), 100);

Since everything is in a single step, I'm not able to figure out what exactly is the problem there. I tried to copy this function in aii.cs file as well and thats failing too. It reads the csv file, but when it tries to get the value in TestContext, i think there is some problem. Please let me know if what I'm doing is correct. Also please let me know if there is any other way to acheive this. Thanks a lot.

[Cody]

Hello Jinesh Kurian,

Would there be any problem with keeping all 5 of your functions within your .aii.cs file? Just call them from your CodedStep function similar to this approach:

[CodedStep(@"Set 'SbFormQText' text to 'australia vacations' - DataDriven: [$(Col1)]")]
public void BingTestDataDriven_CodedStep()
{
    // Set 'SbFormQText' text to 'australia vacations'
    NewMethod();
}
private void NewMethod()
{
    Pages.Bing.SbFormQText.Text = ((string)(System.Convert.ChangeType(Data["Col1"], typeof(string))));
}

The CodedStep can contain anything you want, call as many methods as you like, and still appear as just one step in TestExplorer.

Don't add a TestMethod or a DataSource attribute to your 5 functions. Let them use BaseWebAiiTest which, when properly data bound, will pull data from your .CSV file for you. Then in your 5 functions you can reference the Data object to obtain your data values.

Sincerely yours,
Cody
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[declan]

Hi

I am using a coded step to write some text into a ck editor text field:

Pages.PublishingCMSDevelopment_8.FrameFrame6.BodyTag.SetValue<

 

string>("innerText","sample bodytxt");

how can I bind to a datasource (ie. a table within WebUI) so that each test iteration inserts a different text block (from the data source)?

thanks
D

 

[Konstantin Petkov]

Hello declan,

Have you seen this video regarding the data-binding abilities of WebUI Test Studio? Please let us know if this helps or if you need further assistance.

All the best,
Konstantin Petkov
the Telerik team

[declan]

hi. yes I have seen this and looked again, to be sure. the issue is that i cannot use the WebUI recorded "set text" action because it's not a simple text input box. We have an asp.mvc app with lots of javascript. the text input fields are CK editor fields. therefore, I can use this statement to set the text:

Pages.PublishingCMSDevelopment_8.FrameFrame6.BodyTag.SetValue<string>("innerText","this is the text i am inputting");

so the question is, what syntax should I used to data drive text input (and verification) where I have to use custom code to get it in there (using SetValue).

thanks
D

[Konstantin Petkov]

Hello declan,

The SetValue method could use similar syntax as posted in this thread previously. The Test.Data property should contain your local test data so you just need to refer the exact column name. The syntax should look like this:

SetValue<string>("innerText", ((string)(System.Convert.ChangeType(Data["MyColumnName"], typeof(string))));

Does that work for you too?

All the best,
Konstantin Petkov
the Telerik team