Results 1 to 14 of 14

Thread: How to Import the data from MS-Excel in QTP?

  1. #1
    Junior Member
    Join Date
    Oct 2007
    Answers
    27

    How to Import the data from MS-Excel in QTP?

    Pls

    Anyone tell me How to import a single data from the Excel in QTP.

    Give me exact coding for the sample flight application......

    Last edited by ramyamca1984; 12-04-2007 at 08:06 AM. Reason: Mistake in typing

  2. #2
    Contributing Member
    Join Date
    Sep 2007
    Answers
    62

    Re: How to Import the data from MS-Excel in QTP?

    Hi ramyamca1984

    For import the data from excel in QTP you have to write one line on the top of your qtp script and that is:

    DataTable.Importsheet"C:\Path name(Ex:Mar_Qtp)\Excelsheet name(Ex:abc.xls)",1,Global


    Now run the script.While run time you can see the import data on you data table in QTP.

    Hope It will help you
    Newhere


  3. #3
    Expert Member
    Join Date
    Oct 2007
    Answers
    375

    Re: How to Import the data from MS-Excel in QTP?

    Hi,

    As Ramya suggested Importing data from excel sheet into your datatable can be achieved.

    But incase you wish to read Excel values into your script, try this....

    dim sExcelObj
    dim sWorkBook
    dim sWorkSheets
    dim sValue

    set sExcelObj = CreateObject("Excel.Application")
    sExcelObj.Visible = true
    set sWorkBook = sExcelObj.Workbooks.Open ("path of file")
    set sWorkSheets = sWorkBook.ActiveWorkbook.worksheets ("sheetname")

    sValue = sWorkSheets.cells(row,col).value

    You can also create an array to continously loop through your excel sheet and read all required values.

    Cheers...


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

    Re: How to Import the data from MS-Excel in QTP?

    Hi,
    I am new to QTP. I did like you explained. But it is not working.What happening here is, always script is running from first line means, Next iteration also running from first line.Every time it is taking value from from first row of excel sheet. It is not going to second line. Please explain me, how to reach next row.
    Thanks in advance
    Quote Originally Posted by newhere View Post
    Hi ramyamca1984

    For import the data from excel in QTP you have to write one line on the top of your qtp script and that is:

    DataTable.Importsheet"C:\Path name(Ex:Mar_Qtp)\Excelsheet name(Ex:abc.xls)",1,Global


    Now run the script.While run time you can see the import data on you data table in QTP.

    Hope It will help you
    Newhere



  5. #5
    Junior Member
    Join Date
    Oct 2007
    Answers
    27

    Re: How to Import the data from MS-Excel in QTP?

    Pls tell How to Import the data from MS-Access in QTP?


  6. #6
    Contributing Member
    Join Date
    Jan 2007
    Answers
    33

    Re: How to Import the data from MS-Excel in QTP?

    Quote Originally Posted by thatiper View Post
    Hi,
    I am new to QTP. I did like you explained. But it is not working.What happening here is, always script is running from first line means, Next iteration also running from first line.Every time it is taking value from from first row of excel sheet. It is not going to second line. Please explain me, how to reach next row.
    Thanks in advance
    hi thatiper
    QTP records your actions on the application in the form of vbscript
    now once the recording has been done ...usually we enhance the code accoring to our requirements and need by modifying using control constructs in the script
    we add few lines of code too ..to make QTP behave as desired

    In general if you want QTP to iterate over all rows in datatable then go to
    file> settins > Run and check on the radiobutton "run on all rows"
    probably you willl be able to solve the problem
    hope the iteration doesn't become indefinite

    Last edited by admin; 12-14-2007 at 07:28 AM.

  7. #7
    Junior Member
    Join Date
    Dec 2007
    Answers
    3

    Re: How to Import the data from MS-Excel in QTP?

    Quote Originally Posted by thatiper View Post
    hi, i am new to qtp. I did like you explained. But it is not working.what happening here is, always script is running from first line means, next iteration also running from first line.every time it is taking value from from first row of excel sheet. It is not going to second line. Please explain me, how to reach next row. Thanks in advance
    its as simple as this....

    To add a sheet
    datatable.addsheet("sheetname")

    To import sheet datatable.importsheet "c:\excelfile.xls", "sheet1"

    To get a value form excel file..
    Empno = datatable.value("empno", "sheet1")

    To run the script for all the iterations..
    For i = 1 to datatable.getsheet("sheet1").getrowcount
    msgbox datatable.value("empno", "sheet1")
    Next

    OR

    For i = 1 to datatable.getrowcount
    datatable.setcurrentrow(i)
    datatable.getcurrentrow
    msgbox datatable.value("empno", "sheet1")
    datatable.setnextrow
    Next


  8. #8
    Junior Member
    Join Date
    Sep 2005
    Answers
    3

    Re: How to Import the data from MS-Excel in QTP?

    can any body show me the test script for functional testing in qtp on any topic


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

    Re: How to Import the data from MS-Excel in QTP?

    Quote Originally Posted by bizzzzzare View Post
    Hi,

    As Ramya suggested Importing data from excel sheet into your datatable can be achieved.

    But incase you wish to read Excel values into your script, try this....

    dim sExcelObj
    dim sWorkBook
    dim sWorkSheets
    dim sValue

    set sExcelObj = CreateObject("Excel.Application")
    sExcelObj.Visible = true
    set sWorkBook = sExcelObj.Workbooks.Open ("path of file")
    set sWorkSheets = sWorkBook.ActiveWorkbook.worksheets ("sheetname")

    sValue = sWorkSheets.cells(row,col).value

    You can also create an array to continously loop through your excel sheet and read all required values.

    Cheers...
    I've a question for bizzzzzare: Per your mention above(highlighted in red)Can you please guide me with creating an array of all the rows matching specified criteria by continously looping through the used range in an external excel sheet instead of the datatable.

    I'll really appreciate it.
    Thanks.


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

    Re: How to Import the data from MS-Excel in QTP?

    Hi bizzzzzare! I've a question for you: Can you please tell me how can I create an array to continously loop through an excel sheet and read all required values. I'd really appreciate it. Thanks. I'm in the constant process of learning coding in QTP.


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

    Re: How to Import the data from MS-Excel in QTP?

    Hi boss
    the mentioned code is a sample code. just modify it according ur requirements.

    ============================================================
    testSheetPath = 'address off the sheet
    testSheetName = 'name of the sheet
    columnno = 1 'In excel sheet you are selecting A1 cell
    For start =1 to end 'Starting from 1st cell to end point
    start = start+1 'incrementation
    for i = 1 to rowscount
    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
    next
    next
    end if
    next

    For each element in arrField 'for statement to retrieve the value
    arrFieldNameAndValue = split(element,";",-1)
    strFieldName = arrFieldNameAndValue(0)
    strFieldValue = arrFieldNameAndValue(1)
    strFieldName = removespace(lcase(strFieldName)) 'removing all the spaces

    Select Case strFieldName
    Case "Cellvalue"
    strcellvalue = strFieldValue
    Case " "
    end select
    next


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

    Re: How to Import the data from MS-Excel in QTP?

    Hello Bhaskar,

    I can't thank you enough for your help. I'm a little confused with the array part. I saw your answer for another post which was more clear, but unfortunately it did not have the explanation of the array part.

    http://www.geekinterview.com/talk/13...sheet-qtp.html

    Can you please explain clearly the array part. I'll really really appreciate it. Thanks so much in advance.


  13. #13
    Junior Member
    Join Date
    Dec 2013
    Answers
    2

    Re: How to Import the data from MS-Excel in QTP?

    datatable.AddSheet("AAAA")
    datatable.ImportSheet "D:\shiva\other\Keyword.xls","AA","AAAA"
    rc=datatable.GetSheet("AAAA").GetRowCount

    msgbox rc

    For i=1 to rc
    datatable.GetSheet("AAAA").SetCurrentRow(i)
    a=datatable.Value("shiva","AAAA")
    msgbox a
    Next


  14. #14
    Junior Member
    Join Date
    Dec 2013
    Answers
    2

    Re: How to Import the data from MS-Excel in QTP?

    Hi any one tell me that how to make word gabling??? shiva--->vasih (or) ivahs like this???


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