reading data from excel sheet in QTP

How to retrieve data from excel sheet by checking column name and its value & next column and its value(without using keyword view) scripting only

This is the question asked by visitor Nags

Hi,

Import the excel sheet to your datatable. Then use a for loop like
row_cnt=datatable.getrowcount
for i=1 to row_cnt
datatable.getsheet(i)
a=datatable.value("header",1)
b=datatable.value("header1",1)
.
.
datatable.setnextrow
Next

You can simply access the excell sheet (not import) by using the script. See bellow example:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.WorkBooks.Open("path of yourfile.xls")
Set objDriverSheet = objWorkbook.Worksheets("name of the sheet")

then just acces the cell itself :
row = 1
objDriverSheet.Cells(row,"A").Value

I used just default column names by A,B,C,......

Hope that helps,

I am using the For Loop to get data from the data table. The summary code is:
C=datatable.getrowcount
For i=1 to c
msgbox Datatable.Value("Column1")
Next
'The message box always returns data of row 1. Irrespective of the iteration. How is it possible.

the reason may be cos you r not updating the current row value :
May be this will give you some idea:

i=1
RowCount = DataTable.GetSheet("Global").GetRowCount
CurrentRow = DataTable.GetSheet("Global").GetCurrentRow
For j =1 to RowCount
...
Do whatever you want to do with that value here
...
DataTable.SetCurrentRow (i) 'set the current row to the next one
Next


Thanks,
Kunal.

Please check the below script for reading/Accessing data from excell sheet.

n = DataTable.getSheet("Action1").GetRowCount

For i = 1 to n dataTable.set currentrow(i)

x = dataTable.value("data", 1)
if x = "1" Then
msgbox x
else
msgbox " not a valid data"
end if

Next.

Please check the below script for reading/Accessing data from excell sheet.

n = DataTable.getSheet("Action1").GetRowCount

For i = 1 to n dataTable.set currentrow(i)

x = dataTable.value("data", 1)
if x = "1" Then
msgbox x
else
msgbox " not a valid data"
end if

Next.

Thansk for usefull info

here we can use two ways of coding.
[COLOR=Red][B]1st method:[/B][/COLOR]
datatable.importsheet "path of the excel file.xls",source sheetID,desination sheetID
n = datatable.getsheet("desination sheetname").getrowcount
for i = 1 to n
columnname = datatable.getsheet("destination sheetname").getparameter(i).name
if colunmname = knowncolumnname then
value = datatable.getsheet(destinationsheetname).getparameter(i)
end if
next

[B][COLOR=Red]2nd method:
[/COLOR][/B][COLOR=Red][COLOR=Black]set objexcel = createobject("excel.application")
[/COLOR][/COLOR] Set objWorkbook = objExcel.WorkBooks.Open("path of the file.xls")
Set objDriverSheet = objWorkbook.Worksheets("name of the sheet")
[COLOR=Red][COLOR=Black]columncount = objDriverSheet.usedrange.columns.count
rowcount = objDriverSheet.usedrange.rows.count
for i = 1 to colunmcount
columnname = objDriversheet.cells(i,1)
if columnname = knowncolumnname then
for j = 1 to rowcount
fieldvalue = objdriversheet.cells(j,i)
next
end if
next

Thanks
Bhaskar kumar

[/COLOR][/COLOR]

Hello Bhaskar,

I used your 2nd method to read data from excel sheet. Can you please tell me how can I create an array to continously loop through excel sheet and read ONLY required values.

Shouldn't I use an array inside the for loop or outside? Please help.

Thanks much.

Hi Bhaskar,
I used your 2nd method, but I'm not able to get the column count and row count of the used range of the excel sheet. Could you please help. I'll really appreciate it. Thanks.

Below is my code:
Set objExcelObj = CreateObject("Excel.Application")
objExcelObj.Visible = True
Set objWorkbook = objExcelObj.Workbooks.Open(strParticipantDataSetupFile)

Set objParticipantSheet = objWorkbook.Worksheets(strSheetName)
columncount = objParticipantSheet.usedrange.columns.count
rowcount = objParticipantSheet.usedrange.rows.count

For i = 1 to columncount
columnname = objParticipantSheet.cells(i,1)
If columnname = "PlatformName_vc" Then
For j = 1 to rowcount
fieldvalue = objParticipantSheet.cells(j,i)
Next
End If
Next

Hi ,
I have imported the file & tried with your code.
Suppose there are 3 records in my file & I want to retrieve the values from column 'Name'.
But I am getting the required output 3 times since there are 3 records. I want the required O/P only once. Could u pls guide me on this? Below is my code.

DataTable.Import "C:\1.xls"
row_cnt=datatable.getrowcount
for i=1 to row_cnt
datatable.getsheet(1)
a=datatable.value("DbTable_Row_1_Col_1_out",1)
'b=datatable.value("header1",1)

msgbox a
datatable.setnextrow
Next

Hi All,

Just try below code , hope this will help you

''opens excel file
Set xlApp = GetObject("","Excel.Application")
xlApp.visible = true
Set xlWrkbk = xlApp.Workbooks.Open("abc.xls")
Set xlWrksht = xlWrkbk.Worksheets("Data") ' Data is the name of the sheet
intStartRow = 2 ' Row from whcih you need to start

For intRow = intStartRow to xlWrksht.UsedRange.Rows.Count

strAccountNumber = Trim(xlWrksht.Range("A" & intRow))
strAccuntName = Trim(xlWrksht.Range("B" & intRow))
dtDate = Trim(xlWrksht.Range("C" & intRow))

Next

Hi Bhaskar,
Your way of explanation is very good.
Thanks so much.

Hi Iam trying to import data from excel sheet
iam using
datatable.Import "C:\Automation\Automation\project centre\result.xls"
but when running the test
A general error occurred while importing file C:\Automation\Automation\project centre\result.xls.

Line (109): "datatable.Import "C:\Automation\Automation\project centre\result.xls"".
occurring.Can anyone solve my problem

hi friend,

[QUOTE=sjrani;54001]Hi Iam trying to import data from excel sheet
iam using
datatable.Import "C:\Automation\Automation\project centre\result.xls"
but when running the test
A general error occurred while importing file C:\Automation\Automation\project centre\result.xls.

Line (109): "datatable.Import "C:\Automation\Automation\project centre\result.xls"".
occurring.Can anyone solve my problem[/QUOTE]

hi friend,

As of now I can give two suggestions

1.Without adding placing results.xls in folders directly use the below code and check whether the data is getting imported.

Datatable.Import "C:\results.xls"

If the above is successful then check out the path of the excel sheet which is present with in three folders (Automation\Automation\project centre) are you sure you have three folders, i doubt you would have a single folder named Automation am I right ????Check out the names of the folders and make sure the folders is not spelled wrongly.

Thanks,
Deepa

Hi deepa

Iam sure about the folders because i copied the address from the addressbar.

If iam trying to copy it in the Folder C then iam getting the following error
'C:\result.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct.

If i change the file name then its working. Do you have any idea why its not working with other files

If i am using a file that created by the application then iam getting the error

The DataTable.Import (file) operation failed. Invalid file.


Line (109): "datatable.Import "\\aumail02\users\sailaja.javvaji\My Documents\excel\xx.xls"".

If i change the name of the file its working.

hi friend,

As of now one guess from my side. The file may be read only. I mean already some one has opened this file, else the excel sheet itself sometimes will create the problem. Just create a new excel sheet will resolbve your issue.

Thanks,
Deepa

Hi Deepa,

As my script needs to check whether the imported excel file contains right info or not so if i need to change the excel file name manually we cant add this script to batch run. So i just want to know if there is any solution to this prblm with out manual intervention

This is the login funtion i have created. It is running well, but the problem is that i can not stop the loop. any one can solve my problem?
Function login()
datatable.Import "E:\Neeraj_data\QTP prac\QTP framwork\PO7\Data Pool\LoginData.xls"
a=datatable.GetRowCount()
For i= 1 to a
datatable.SetCurrentRow(i)
Browser("Logon ProjectObjects").Page("Logon ProjectObjects").WebEdit("UserId").Set datatable.Value(1,1)
Browser("Logon ProjectObjects").Page("Logon ProjectObjects").WebEdit("password").Set datatable.Value(2,1)
Browser("Logon ProjectObjects").Page("Logon ProjectObjects").Link("Login").Click
Browser("Logon ProjectObjects").Page("Home Page").Link("Logoff").Click

Next

End Function