Thread: 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

Thansk for usefull info

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.

here we can use two ways of coding.
1st method:
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

2nd method:
set objexcel = createobject("excel.application")
Set objWorkbook = objExcel.WorkBooks.Open("path of the file.xls")
Set objDriverSheet = objWorkbook.Worksheets("name of the sheet")
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

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

Pls I need you help, I m trying to loop values from an external excel sheet in a call function but its only picking the first rows of data, can you help please. I have researched and used several options but no JOY!. See code below:

=====================================================================================================================

Function fnFetchDataFromExcelSheet(strColumnName)

Dim sColValue

'Initialize the return the value to "empty" string
fnFetchDataFromExcelSheet = "empty"

'Add a new blank sheet into the QTP data table
'Excel data will be copied into this blank sheet
DataTable.AddSheet("dtDataSheet")

'Import the data from the excel sheet into the QTP data table
DataTable.ImportSheet sExcelWorkbookPath, sTestCaseName, "dtDataSheet"

'Find the value from the data table
sColValue = DataTable.Value(strColumnName, "dtDataSheet")

'Return the value back to the calling function
fnFetchDataFromExcelSheet = sColValue

'Remove Reference
DataTable.DeleteSheet("dtDataSheet")


End Function

Thanks for sharing information.

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.