Retrieving test data from Excel sheet

[janriis]

Hi all

I need to retrieve test data from an Excel sheet instead of using the build in Data table. For each iteration passed, i also need to return an "OK" to the Excel sheet.

Could anyone tell me how that is done ?

[sreekanth chilam]

Hi,

Search for "Excel COM" in this forum.

You can do it easily.

[janriis]

Searched all over this forum and found lots of bits of code which required that you knew the rest of the code.

I want to be more specific about my question

I have an Excel file called: testdata.xlsx in the folder "C:\temp"
In this file I have a sheet called "release 1". In the sheet I have 3 columns which looks as follows:
Column A: UserID
Column B: FirstName
Column C: LastName

In my web application, I have 3 WebEdit fields also called UserID, FirstName and LastName

So how should the code look like if I want to retrieve the data from the 3 columns in Excel and populate the WebEdit's in my web application ?

[tarun]

first you need to change the excel to 2003 format as QTP does not support 2007 format.
then you can use Datatable.importsheet or datatable.import statements to import the sheet to your test. The second way is to change the "Resource" settings for the test (right click on test in test flow tab and select settings), under the data table options give the path of your excel file.

Hope this helps

[sreekanth chilam]

Hi,

As tarun suggested, use .xls format file instead .xlsx format excel files as they are not supported by QTP.

Code:

Dim xl
Set xl=CreateObject("Excel.Application")
xl.WorkBooks.Open "your excel file path"
xl.Sheets("Sheetname").Select
UserId=xl.Cells(1,1).value
FirstName =xl.Cells(1,2).value
LastName=xl.Cells(1,3).value

Browser("...").Page("...").WebEdit("UserId").Set UserId
Browser("...").Page("...").WebEdit("FirstName ").Set FirstName
Browser("...").Page("...").WebEdit("LastName").Set LastName

xl.ActiveWorkBook.Save
xl.Application.Quit
Set xl=nothing

[janriis]

Thx for your reply, but wont it just take the first row of data ? The next run should fetch data from the second row and so on.

[tarun]

Use the statement

DataTable.ImportSheet SheetPath, SheetSource, Sheetdestination statement in your QTP code to import the excel sheet to your test case e.g.

DataTable.ImportSheet "c:\test.xls","TestData","Login"

This statement will import data from TestData worksheet of test.xls excel sheet to data table of Login action. if you want to import to global sheet then use

DataTable.Import "c:\test.xls"

[sreekanth chilam]

Hi Janris,

In your question given above you have just asked for 3 columns A,B,C. So I took that example code

You can use the below statements to the above code.
Row_Count=xl.ActiveSheet.UsedRange.Rows.Count

Code:

For i=1 to Row_Count
    UserId=xl.Cells(i,1).value
    FirstName =xl.Cells(i,2).value
    LastName=xl.Cells(i,3).value

     Browser("...").Page("...").WebEdit("UserId").Set UserId
     Browser("...").Page("...").WebEdit("FirstName ").Set FirstName
     Browser("...").Page("...").WebEdit("LastName").Set LastName     Next
xl.ActiveWorkBook.Save
xl.Application.Quit
Set xl=nothing

[Zelfiris]

Don't mean to hijack your thread, but I also have an excel question.

I'm trying to get QTP to read "0002" from my spreadsheet but I'm not sure how to do it.

When I used .csv with a different application it saves the zeros but with .xls it does not seem to be able to.

Thanks!

[tarun]

When you are saving data to excel sheet try to "'" before the data