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

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

[B]Hey guys try the below code...hope it would help you...[/B]
set objexcel = createobject("excel.application")
Set objWorkbook = objExcel.WorkBooks.Open("C:\GLS\glsDailyItemProfilingHistory")
Set objDriverSheet = objWorkbook.Worksheets("Sheet1")
columncount = objDriverSheet.usedrange.columns.count
rowcount = objDriverSheet.usedrange.rows.count
columnendvalue = int(columncount)
rowendvalue=int(rowcount)
strObjectName = "SUB CENTER:WHSE AREA:ITEM #:DESCRIPTION:FIELD CHANGED:CURRENT VALUE:PRCH COMP:ORIGINAL VALUE:USERID"
strInputValue = "Pilot_Subcenter2:1-Dry Grocery:1334032:DAWN AB APPLEBLSM:WHPKWIDQTY:12.0000:WMT USA:8.0000:isd"
columnheader = split(trim(strObjectName), ":")
columnvalue = split(trim(strInputValue), ":")
ReDim cellvalue(ubound(columnheader ))
a=ubound(columnheader) + 1
For i = 1 to rowendvalue
flag=-1
For j =1 to a
cellvalue(j-1)= objDriversheet.cells(i,j)

If columnvalue(j-1) = cellvalue(j-1) then
flag=flag+1
end if

if flag = ubound(columnheader) then
msgbox "match found"
end if

Someone can help me with the QTP code for below case scenario. Thanks in Advanvce!!

Example:
Emp #, EMP Name, City, State, County, Amount
1 Rajesh Bangalore KA IN 2000
2 Majesh Bangalore KA IN 5000
1 Rajesh Bangalore KA IN 2000

Expecting result should be
Emp #, EMP Name, City, State, County, Amount
1 Rajesh Bangalore KA IN 4000
2 Majesh Bangalore KA IN 5000

Could you please help me to comparing two excel sheet row by row and cell by cell in QTP

Data may be we have n rows and n columns. Thank you lot for your great help

I hope this way it will work
________________________________________________________________________________
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)
Next

Browser("Logon ProjectObjects").Page("Logon ProjectObjects").Link("Login").Click
Browser("Logon ProjectObjects").Page("Home Page").Link("Logoff").Click

End Function
___________________________________________________________________________________________
Please let me know if it doesn’t work I have another solution for your login script.Thanks and best regards,
Ahmed Shamim
QA Analyst
202-509-3188

Re: reading data from excel sheet in QTP
I hope this way it will work
________________________________________________________________________________
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)
Next

Browser("Logon ProjectObjects").Page("Logon ProjectObjects").Link("Login").Click
Browser("Logon ProjectObjects").Page("Home Page").Link("Logoff").Click

End Function
___________________________________________________________________________________________
Please let me know if it doesn’t work I have another solution for your login script.Thanks and best regards,
Ahmed Shamim
QA Analyst
202-509-3188

[QUOTE=kodhanrajesh;60556]Could you please help me to comparing two excel sheet row by row and cell by cell in QTP

Data may be we have n rows and n columns. Thank you lot for your great help[/QUOTE]
___________________________________________________________________________________________________________________
This code will compare each sheet cell by cell. if any mismatch between tow excel files then that will highlighted with red color.

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:\file01.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:\file02.xls”)
Set objWorksheet1= objWorkbook1.Worksheets(1)
Set objWorksheet2= objWorkbook2.Worksheets(1)
For Each cell In objWorksheet1.UsedRange
If cell.Value <> objWorksheet2.Range(cell.Address).Value Then
cell.Interior.ColorIndex = 5
Else
cell.Interior.ColorIndex = 0
End If
Next
set objExcel=nothing


_________________________________________________________________________________
Please let me know if you need any other help.

Thanks and best regards,
Ahmed Shamim
QA Analyst
202-509-3188

[QUOTE=lucknow.neeraj;55758]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[/QUOTE]


__________________________________________________________________________________________

I hope this way it will work
________________________________________________________________________________
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)
Next

Browser("Logon ProjectObjects").Page("Logon ProjectObjects").Link("Login").Click
Browser("Logon ProjectObjects").Page("Home Page").Link("Logoff").Click

End Function
___________________________________________________________________________________________
Please let me know if it doesn’t work I have another solution for your login script.Thanks and best regards,
Ahmed Shamim
QA Analyst
202-509-3188

[B]Data Warehousing[/B]
A Data warehouse is a repository of integrated information, available for queries and analysis. Data and information are extracted from heterogeneous sources as they are generated. This makes it much easier and more efficient to run queries over data that originally came from different sources". Another definition for data warehouse is: " A data warehouse is a logical collection of information gathered from many different operational databases used to create business intelligence that supports business analysis activities and decision-making tasks, primarily, a record of an enterprise's past transactional and operational information, stored in a database designed to favour efficient data analysis and reporting (especially OLAP)". Generally, data warehousing is not meant for current "live" data, although 'virtual' or 'point-to-point' data warehouses can access operational data. A 'real' data warehouse is generally preferred to a virtual DW because stored data has been validated and is set up to provide reliable results to common types of queries used in a business

What are the steps to build the data warehouse?
1. Requirement gathering for BI reports
2. Identify the source databases
3. Designing source to target mappings
4. Designing DWH
5. Designing ETL jobs
6. Development of ETL jobs
7. Data load into DWH

using ODBC we can get data from excel sheet in QTP in dictionary object and iterate over dictionary object keys.

sConfigFile = "D:\QTP\Demo\Files\Driver\Demo_Driver_File.xlsx"

sConfigQuery = "Select * from [DriverData$] where RUN='Y'"
Set dicConfigData = CreateObject("Scripting.Dictionary")
nNumberOfSelectedData=GetDataFromExcel(sConfigFile,sConfigQuery,dicConfigData)

For nCurrentSelectedData=1 to nNumberOfSelectedData
ENVIRONMENT("CURRENT_URL") = Trim(dicConfigData.Item("URL"&nCurrentSelectedData))
ENVIRONMENT("CURRENT_TEST_FILE_URL") = Trim(dicConfigData.Item("TEST_FILE_URL"&nCurrentSelectedData))
ENVIRONMENT("CURRENT_TRAN_ID") = Trim(dicConfigData.Item("TID"&nCurrentSelectedData))
ENVIRONMENT("CURRENT_APPLICATION") = Trim(dicConfigData.Item("APPLICATION"&nCurrentSelectedData))
print ENVIRONMENT("CURRENT_TRAN_ID")
print ENVIRONMENT("CURRENT_APPLICATION")

Next




Set dicConfigData = Nothing
Set objFso = Nothing


Public Function GetDataFromExcel(sFile,sQuery,dictionary)
set DB_CONNECTION=CreateObject("ADODB.Connection")
'DB_CONNECTION.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&sFile&";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
DB_CONNECTION.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sFile & ";Extended Properties=Excel 12.0;"
set Record_Set1=DB_CONNECTION.Execute(sQuery)
set Record_Set2=DB_CONNECTION.Execute(sQuery)
i=0
Do While Not Record_Set2.EOF
j=0
FOR EACH FIELD IN Record_Set1.FIELDS
column_name1=FIELD.NAME& (i+1)
row_value1=Record_Set2(j)
If IsNull(row_value1) Then
row_value1=""
End If
row_value1= Cstr(row_value1)
dictionary.Add column_name1,row_value1
j=j+1
NEXT
Record_Set2.MoveNext
i=i+1
Loop
GetDataFromExcel=i
Record_Set1.Close
Set Record_Set1=Nothing
Record_Set2.Close
Set Record_Set2=Nothing
DB_CONNECTION.Close
set DB_CONNECTION=Nothing
End Function

QTP Script for Excel sheet How to add the value of column 1 and column 2 and display the result in column 3 ???
Please do needful

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.