Page 1 of 2 12 LastLast
Results 1 to 20 of 33

Thread: reading data from excel sheet in QTP

  1. #1
    Expert Member
    Join Date
    Oct 2006
    Answers
    209

    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


  2. #2
    mbazith
    Guest

    Re: reading data from excel sheet in QTP

    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


  3. #3
    Junior Member
    Join Date
    Dec 2006
    Answers
    2

    Re: reading data from excel sheet in QTP

    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,


  4. #4
    Junior Member
    Join Date
    Sep 2007
    Answers
    1

    Re: reading data from excel sheet in QTP

    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.


  5. #5
    Contributing Member
    Join Date
    Feb 2006
    Answers
    85

    Re: reading data from excel sheet in QTP

    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.


  6. #6
    Junior Member
    Join Date
    Feb 2009
    Answers
    2

    Re: reading data from excel sheet in QTP

    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.


  7. #7
    Junior Member
    Join Date
    Feb 2009
    Answers
    2

    Re: reading data from excel sheet in QTP

    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.


  8. #8
    Junior Member
    Join Date
    Aug 2008
    Answers
    1

    Wink Re: reading data from excel sheet in QTP

    Thansk for usefull info


  9. #9
    Expert Member
    Join Date
    Oct 2007
    Answers
    126

    Re: reading data from excel sheet in QTP

    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




  10. #10
    Junior Member
    Join Date
    Mar 2009
    Answers
    5

    Re: reading data from excel sheet in QTP

    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.


  11. #11
    Junior Member
    Join Date
    Mar 2009
    Answers
    5

    Re: reading data from excel sheet in QTP

    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


  12. #12
    Junior Member
    Join Date
    Mar 2009
    Answers
    1

    Re: reading data from excel sheet in QTP

    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

    Last edited by archgk; 03-25-2009 at 04:31 AM.

  13. #13

    Re: reading data from excel sheet in QTP

    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


  14. #14
    Junior Member
    Join Date
    Dec 2007
    Answers
    1

    Re: reading data from excel sheet in QTP

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


  15. #15
    Junior Member
    Join Date
    Apr 2010
    Answers
    5

    Question Re: importing data from excel sheet in QTP

    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

    Last edited by sjrani; 04-20-2010 at 01:30 AM.

  16. #16
    Expert Member
    Join Date
    Apr 2008
    Answers
    1,859

    Re: importing data from excel sheet in QTP

    hi friend,

    Quote Originally Posted by sjrani View Post
    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,

    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

    Arise Awake And Stop Not Till The Goal Is Reached

  17. #17
    Junior Member
    Join Date
    Apr 2010
    Answers
    5

    Re: reading data from excel sheet in QTP

    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.

    Last edited by sjrani; 04-20-2010 at 11:01 PM.

  18. #18
    Expert Member
    Join Date
    Apr 2008
    Answers
    1,859

    Re: reading data from excel sheet in QTP

    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

    Arise Awake And Stop Not Till The Goal Is Reached

  19. #19
    Junior Member
    Join Date
    Apr 2010
    Answers
    5

    Re: reading data from excel sheet in QTP

    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


  20. #20
    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

    Last edited by lucknow.neeraj; 07-05-2010 at 05:46 AM.

Page 1 of 2 12 LastLast

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