Working with Data Table Utility Object

Working with Data Table Utility Object

The data your test uses is stored in the design-time Data Table, which is displayed in the Data Table pane at the bottom of the screen while you insert and edit steps.

The Data Table has the characteristics of a Microsoft Excel spreadsheet, meaning that you can store and use data in its cells and you can also perform mathematical formulas within the cells. You can use the DataTable, DTSheet and DTParameter utility objects to manipulate the data in any cell in the Data Table. For that you need to follow specific methods and properties to insert or retrieve data from DtSheets.

Before that here is a small comparison of normal Excel and QTP Datatable.

Windows Excel hierarchy	QTP datatable hierarchy
Excel Application Excel Sheet Columns Rows & values	Datatable DtSheet DtParameter Value & Valuebyrow

Applicable Methods and Properties on Data Table Utility Object

Datatable	DtSheet	DtParameter
Methods AddSheet DeleteSheet Export ExportSheet GetCurrentRow GetRowCount GetSheet GetSheetCount Import ImportSheet SetCurrentRow SetNextRow SetPrevRow Properties GlobalSheet LocalSheet RawValue Value	Methods AddParameter DeleteParameter GetCurrentRow GetParameter GetParameterCount GetRowCount SetCurrentRow SetNextRow SetPrevRow Properties Name	Properties Name RawValue Value ValueByRow

These are all the methods and properties have to use for doing any operation in QTP.

How to apply Methods & Properties

For Example you’re creating an Excel sheet in windows with some employee information.

To create this information you need to create a new excel sheet, column and add values in all columns. If we observe the step by step process of this example

Windows	QTP
1. Create an Excel Sheet 2. Add Column to the sheet 3. Add values to the columns	1. Datatable.Addsheet 2. AddParameter 3. Value & valuebyrow

Script Ex:- Adding Sheet

Set dtSheet=Datatable.AddSheet(“Demo”) ‘ Creating Sheet

Set dtEmpid=dtSheet.Addparameter(“EmpId”,””) ‘ Creating Columns

Set dtEmpName=dtSheet.Addparameter(“EmpName”,””)

Set dtEmpSal=dtSheet.Addparameter(“Sal”,””)

For row=1 to 5

dtSheet.setcurrentrow(row)

dtEmpid.value=row ‘ Adding values to the Specific Rows

dtEmpName.value=”emp”&row

dtEmpSal.value= RandomNumber(3000,10000)

Next

Datatable.ExportSheet “C:\Test.xls”,”Demo”

Copy this code in to QTP and execute it. After executing you will find an excel sheet in C:\.

Accessing data from the Sheet

Suppose already you had a sheet in your drive and you want to access that data to use in QTP. In this case you need to import that external sheet into QTP and then you have to use getsheet & getparameter methods to access the data.

Script Ex:- Accessing Data

Datatable.Addsheet(“demo”)

datatable.ImportSheet dtPath, , SheetSource, “demo”

Set dg=datatable.GetSheet(“demo”)

ColumnCount=dg.getparametercount

RowCount=dg.getrowcount

For Column =1 to ColumnCount

Cname=dg.getparameter(Column).name

Set pmg=dg.getparameter(Cname)

For Row =1 to RowCount

Val=pmg.valuebyrow(Row)

Msgbox (val)

next

Next

Script Ex:- Copy Odd Values from one sheet

Suppose if you have some values like this…. In this if you want to copy odd values, and then you need to follow this code.

Set dtSheet=datatable.GetSheet(Sheetname)

Set dtColumn=dtSheet.getparameter(“Numbers”)

RowCount=dtSheet.getrowcount

For Row =1 to RowCount

Val= dtColumn.valuebyrow(Row)

If Val Mod 2 <>0 then

Msgbox (Val)

End If

next

In this Script for Sheetname you have to provide the sheet name from which sheet you’re going to get the data.

Excel Object Model

Excel object Model is to automate the excel operations. Using QTP we can create sheets, But using Excel Object Model we can do full pledged operations what ever we are doing on normal excel sheet in windows.

Here is a sample Script to know how we can work with EOM

Set Excel=CreateObject("Excel.Application")

Set ExcelSheet = CreateObject("Excel.Sheet")

ExcelSheet.Application.Visible = True

For row=1 to 10

ExcelSheet.ActiveSheet.Cells(row,1).Value = "This is column A, row"&row

Next

ExcelSheet.SaveAs "C:\TEST.XLS"

ExcelSheet.Application.Quit

Set ExcelSheet = Nothing

To know more about Excel Object Model go to...

Open Excel->Help menu-> Microsoft Excel Help-> Table of Contents-> Microsoft Excel Visual Basic Reference->Microsoft Excel Object Model

Some Interesting Questions…

1. Copy Odd Rows from a sheet
2. Copy employees whose age is above 50
3. Get Diagonal values from a sheet
4. Copy data from one sheet to another sheet without changing column names
5. Eliminate duplicate rows in a column
6. sort the values in a column

____________________________________________________________________________

Please send your Suggestions and Doubts to my yahoo group http://in.groups.yahoo.com/group/qtpsudhakar