Page 2 of 2 FirstFirst 12
Results 21 to 33 of 33

Thread: reading data from excel sheet in QTP

  1. #21
    Junior Member
    Join Date
    Jul 2010
    Answers
    2

    Re: reading data from excel sheet in QTP

    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


  2. #22
    Junior Member
    Join Date
    May 2011
    Answers
    1

    Re: reading data from excel sheet in QTP

    Hey guys try the below code...hope it would help you...
    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 #ESCRIPTION:FIELD CHANGED:CURRENT VALUE:PRCH COMP:ORIGINAL VALUE:USERID"
    strInputValue = "Pilot_Subcenter2:1-Dry Grocery:1334032AWN 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


  3. #23
    Junior Member
    Join Date
    Jul 2008
    Answers
    4

    Re: reading data from excel sheet in QTP

    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


  4. #24
    Junior Member
    Join Date
    Jul 2008
    Answers
    4

    Re: reading data from excel sheet in QTP

    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


  5. #25
    Junior Member
    Join Date
    Sep 2011
    Answers
    12

    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


  6. #26
    Junior Member
    Join Date
    Sep 2011
    Answers
    12

    Re: reading data from excel sheet in QTP

    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


  7. #27
    Junior Member
    Join Date
    Sep 2011
    Answers
    12

    Re: reading data from excel sheet in QTP

    Quote Originally Posted by kodhanrajesh View Post
    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
    ___________________________________________________________________________________________________________________
    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


  8. #28
    Junior Member
    Join Date
    Sep 2011
    Answers
    12

    Re: reading data from excel sheet in QTP

    Quote Originally Posted by lucknow.neeraj View Post
    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

    __________________________________________________________________________________________

    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


  9. #29
    Junior Member
    Join Date
    Sep 2011
    Answers
    12

    Data Warehousing

    Data Warehousing
    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


  10. #30
    Junior Member
    Join Date
    Jul 2010
    Answers
    2

    Re: reading data from excel sheet in QTP

    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


  11. #31
    Junior Member
    Join Date
    Aug 2009
    Answers
    1

    Re: reading data from excel sheet in QTP

    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


  12. #32
    Junior Member
    Join Date
    Oct 2014
    Answers
    2

    Re: reading data from excel sheet in QTP

    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


  13. #33

    Re: reading data from excel sheet in QTP

    Thanks for sharing information.


Page 2 of 2 FirstFirst 12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact