Introduction of External Data Sheet
In QTP, we have an option for Data Table which comes very useful when the user wants to parameterize his/her test. The parameters set by user appear by default in the Columns of the sheet. Not only is this helpful for parameterized operation of your test also when you want to work with the Output function of QTP you can use this table since the corresponding outputs can be viewed over here.
This option can be as per the user requirement and in this the user can make an Excel sheet with the required parameters in it. Also one can import and export the contents to his test while running it.
Import External Data File
The user shall perform the following operations:
In this once the user has created the Excel sheet and has recorded the test, (s)he shall right click on any of the cells in the Data Table and select the File>Import, user will get a window where (s)he will have to select the exact location of the excel sheet.
On Clicking the option, user will get a window pop-up box asking whether you will want to replace the contents of the current data table with the ones in the excel sheet. Click OK and proceed.
Select the exact location of the excel file on your PC which you want to import.
The contents of the excel sheet shall appear in the Data Table of QTP once you selected the required excel sheet.
Remember to use the same naming convention through-out the script, you have used in the file. Each of the column name in your excel file will become one parameter.
Secondly the actions for which the user shall be setting parameters need to be given the same name as in the imported excel file. As shown below, while recording the file the user has selected a particular value for the Action which he wants to parameterize which will appear in the constant option.
The user has to choose the Parameter option and then type the exact name as in the Data table for e.g.: “No_Pass”, here we are parameterizing the no of passengers for whom the tickets have to be booked and will re-run the script for that much time.
Points to note regarding which location to select OR whether to make a sheet global or local.
- Globally implies the user has the Global sheet of the data table filled up with certain data. Now when he runs the test it will operate for the amount of rows that are filled in this sheet.
- If you want a condition that for single row run of the global sheet there should be run of another sheet namely the “Action 1” of the data table we call the operation as Local.
- In this it is important that the user selects the Parameter option as local and not global as in the above condition and the required contents will come in Action 1 sheet.
- Now initially if while you are importing if there were two sheets created by you then by default the contents of the second sheet will be Action 1. It is only that the corresponding Action be parameterized properly.
Now, In the keyword view, Right click on the Action 1 and select Action Call Properties and then in the Run Tab select the no of rows for which you want to run the Local sheet.
When the user runs this script, for every single row of the Global sheet, the Action 1 sheet will run for all of its respective columns.
A similar method can be used to import a single sheet or a database file.
Also from within the script you can use Datatable.Import(“path of file”)
Export External Data File
Like importing data sheet we can also export the entire data table into an external excel file. This is basically useful if one wants to use the required parameters in some different test run within QTP.
Similar to the exporting of complete file we can also export an single sheet from the data table
Also from within the script you can use Datatable.Export(“path of file”)
Next post we shall see the different operations which can be performed on data sheet.
Related: QTP and Excel Part1
If you want to keep track of further articles on QTP. I recommend you to subscribe via RSS Feed. You can also subscribe by Email and have new QTP articles sent directly to your inbox.
Please use the QTP forum for posting questions now!
Hi Ankur,
While Importing Xl Sheet to QTP 9.2 Datatable it’s Supporting 97-2003 Format only… Also after updating the Sheet to global I couldn’t use the Datatable… I mean I Datatable(Others also Like Action1, Action2)…. May I know why?Plz……
I used method is: DataTable.ImportSheet “C:\name.xls” ,1 ,”name”
Hi Ankur,
I am trainer in various automation testing tools . Before I was teaching only in which way we are using the options available in tool bars. But after going through your site “LearnQTP” it forced me to go dipper and dipper. Now I am updating myself with automating word,excel and other applications through scripting.
Trying to understand the Automation Frame Work also.
For this I am very grateful to you.
Thanks and Regards,
Gayatri
Hi Ankur,
Can you please tell me how to export an Excel sheet to QTP using an Excel Macro??
Thanks
Priya
Hi Ankur,
I have one query.
In run time I am facing one issue while using the datatable in QTP.
I am adding one sheet name Login in Run time and also exporting some data in the same.
Now I want to use the data in Login as input for my testing but i am getting the message that colum “xyz” doesn’t exist.
I have added same colum in local sheet which working fine but for Login sheet I am getting this error. While in debug mode i have verified that Login sheet is creating properly & also data is exported properly.
Syntax are as follow
temp = datatable(“XYZ”,Login) (Getting error that USER Column does not exist)
temp = datatable(“XYZ”,dtLocalSheet) (This is working fine)
Hi,
How to close an ActiveWorkBook(Saving) through QTP?
Hi Ankur
I did import data from excel sheet to QTP but not sure how would I get combination of different row and different column values. I dont want mention it manually like cells(1,3). Here is the code I am using any suggestions would be helpful.
getCurrentlyOpenPage().Image(“href:=.*bluecycle.com/register.jsp”).Click
getCurrentlyOpenPage().Link(“name:= Register now”).Click
Set xlApp = GetObject(“”,”Excel.Application”)
xlApp.Visible = True
Set xlWrkbk = xlApp.Workbooks.Open(“R:InformationTechnology4. DepartmentTestingQTPX-ModActionsAndValuesSushmaRegistrationNeroNero.xls”)
Set xlWrksht = xlWrkbk.Worksheets(“Nero”) ‘ Data is the name of the sheet
intStartRow = 2 ‘ Row from which you need to start
For intRow = intStartRow to xlWrksht.UsedRange.Rows.Count
Set currentRecord = new RecordObject
currentRecord.title = Trim(xlWrksht.cells(intRow, “Title”)).Value
‘getCurrentlyOpenPage().WebList(“name:= name.prefix”).Select strTitle
currentRecord.fname = Trim(xlWrksht.cells(intRow, “Name”)).Value
‘getCurrentlyOpenPage().WebEdit(“name:= name.first”).Set strFName
currentRecord.surname = Trim(xlWrksht.cells(intRow, “Surname”)).Value
‘getCurrentlyOpenPage().WebEdit(“name:= name.last”).Set strSName
currentRecord.dob = Trim(xlWrksht.cells(intRow, “DOB”)).Value
currentRecord.homeno = Trim(xlWrksht.cells(intRow, “HomeNumber”)).Value
currentRecord.street = Trim(xlWrksht.cells(intRow, “Street”)).Value
currentRecord.city = Trim(xlWrksht.cells(intRow, “City”)).Value
currentRecord.postcode = Trim(xlWrksht.cells(intRow, “PostCode”)).Value
currentRecord.months = Trim(xlWrksht.cells(intRow, “Months”)).Value
currentRecord.homephone = Trim(xlWrksht.cells(intRow, “HomePhone”)).Value
currentRecord.emailadd = Trim(xlWrksht.cells(intRow, “EmailAddress”)).Value
currentRecord.confirmemailadd = Trim(xlWrksht.cells(intRow, “ConfirmEmail”)).Value
currentRecord.applicationtype = Trim(xlWrksht.cells(intRow, “ApplicationType”)).Value
currentRecord.username = Trim(xlWrksht.cells(intRow, “UserName”)).Value
Next
Class RecordObject
Dim title
Dim fname
Dim surname
Dim dob
Dim homeno
Dim street
Dim city
Dim postcode
Dim months
Dim homephone
Dim emailadd
Dim confirmemailadd
Dim applicationtype
Dim username
End Class
I have found a way to get around the ‘import only allows one sheet’ problem. Just to share, not sure if it’s useful for people.
DataTable.AddSheet(“importedSheet”)
DataTable.ImportSheet filePath, “sourceSheetName”,”importedSheet”)
It is to create the sheet first, and then imported the particular sheet from a file to the created sheet.
Regards,
Nyssa
I have the same issue with the
DataTable.Import command as Sourav mentioned. If I import a data file using DataTable.Import(“filepath/filename”), only the first row is read repeatedly. I went thru this forum and I could not find an answer for Sourav’s question. Please help
Hi All,
I got type mismatch error whilr executing this script line…
SwfWindow(“Waban SMS Admin Tool”).SwfEdit(“SwfEdit”).Set paramval(“C:\Documents and Settings\user\Desktop\Objects\INPUT.xls”,”INPUT”)
Could you please provide me the reason why this happening ?
Hi Ankur,
I have a excel sheet with some values and a graph , I want to import the excel with both the graph and values corresponding to it and with macros if any.
Could you please guide me?
Regards,
New to QTP
Hi Ankur…is there a way to export all data from a row (having multiple columns) from data table to external application (not excel) in one click/executable statement..What i am looking for is suppose i have 5 edit boxes, i want rather then QTP identifyng each edit boxes one by one i want data to be pasted/forwarded in all edit boxes from data table rows at same time….
Hi Ankur!
I read your script is really good but i would like ask you something:
I need to get values from Spreedsheet but its stored in Quality Center.
I tried to map the QC path in my machine but I could not.
Do you have any suggest?
Thanks.
Jorge Quiroz
Hi Ankur,
When I’m exporting an excel file into DataTable, I’m not able to run all the rows present in the xls.
For e.g. let us consider this code (login to Flight App)
‘Importing excel
DataTable.Import(“C:\Users\user\Documents\Mercury Interactive\QuickTest Professional\Tests\Import_xls\Flight.xls”)
‘…Launching the application
SystemUtil.Run “D:\Program Files\Mercury Interactive\QuickTest Professional\samples\flight\app\flight4b.exe”,””,”D:\Program Files\Mercury Interactive\QuickTest Professional\samples\flight\app”,””
If (Dialog(“Login”).Exist(5)) Then
Reporter.ReportEvent micPass,”Login window should open”,”Login window is opened”
Dialog(“Login”).WinEdit(“Agent Name:”).Set DataTable(“AgentName”, dtGlobalSheet)
Dialog(“Login”).WinEdit(“Agent Name:”).Type micTab
Dialog(“Login”).WinEdit(“Password:”).Type DataTable(“Password”, dtGlobalSheet)
Dialog(“Login”).WinButton(“OK”).Click
If (Window(“Flight Reservation”).Exist(20)) Then
Window(“Flight Reservation”).Close
Else
ExitTest
End If
Else
ExitTest
End If
say the user names for 3 rows are Jim, Tom, Sam. When I’m running this script with “Run on all rows” enabled, its login into Flight with Jim only, although its running 3 iterations.
Can you tell me, why this is happenning and how can I resolve this?
Note: I don’t want to use For loop.
Thanks,
Sourav
Hi Ankur,
Thanks for your effort. I have one question: When I tried to save an excel file and the file is already exists at the location. Then an alert comes “File is already exists, Do you want to replace it?” with button “Yes | No | Cancel”. Then I am to choosing option manually, I want to do it by scripting. Please suggest the soultion. I am using the following code to save the file ExcelObj.ActiveWorkbook.SaveAs “filenamewithpath”
Thanks
@Shrini:
I will answer your questions bottom-up.
>> What are your ideas of handling this issue …
I would suggest use AOM(Automation Object Model) to call excel file and read values one by one from it. Using this approach, you would be saved from importing files again and again (if your are doing it through scripting), also maintaining a file and updating values from outside is much easier than from inside data table.
I hope you know how to do that but just in case and for other readers I will come up with a post sometime this week or next..
>>I can not import this excel data into a DataTable … When imported only first sheet gets imported.
That is the way QTP is designed, you have 1 global sheet and other sheet for individual actions…but yes if you want to make some particular sheet of your workbook as a global sheet QTP does provide you an option to do that…
Right Click on data table > Sheet> Import> From File (Choose your sheet)
Ankur —
I know of a limitation where an excel work book with more than multiple work sheets can not be imported as is …
Let us say I have an external sheet (want to use it as “Global” sheet) with 5 worksheets namely
“customer” “order” “location” “costing” and “Reports” …
I can not import this excel data into a DataTable … When imported only first sheet gets imported.
This means .. all the data I need to import has to be in only one sheet.
Why is this “severe” restriction?
Alternative suggested is make those sheets as local sheets and add as many as actions as no of sheets .. this is another stupid idea as my tests are not not organized into actions but are split accross manny VBS files….
What are your ideas of handling this issue …
Kiran, This might help you,
Set ExcelObj = CreateObject(“Excel.Application”)
ExcelObj.Workbooks.Add
Set NewSheet = ExcelObj.Sheets.Item(1)
NewSheet.Name = “Nameofthesheet”
NewSheet.Cells(row,col) = variable or string
ExcelObj.ActiveWorkbook.SaveAs “filenamewithpath”
ExcelObj.Quit
Cheers,
Raj
Hi Ankur,
how to call excel sheet while parameterizing,(external data sheet)
Hi Ankur;
Is thr any way that we can export a runtime variable value to an external excel file(i.e. file in total different drive and not having QTP connection at all)?
I have managed to get hold of that value but to I am not able to figure out how to export to specified cell in excel file.
Cheers
Kiran
Hi Ankur!
I am Pavan Turlpaati.
This particular post written by you is very clear and explained in a very detailed level.I feel very fortunate to get the blog from search engine.
Keep rocking brother and lead our way.
Regards,
Pavan Turlapati
http://truetesting.blogspot.com