QTP Script to Export Database Data to Excel Sheet

Import data from database to datatable

Script to export database data to excel sheet in qtp.

Dim con,rs
Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")
con.provider="microsoft.jet.oledb.4.0"
con.open"d:\db.mdb"
rs.open"select*from EMP",con
Set ex=createobject("Excel.Application")
Set a=ex.workbooks.open("D:\Lak.xls")
Set b=a.worksheets("sheet1")
i=1
Do While Not rs.EOF
b.cells (i,1).value=rs.fields("empno")
b.cells(i,2).value=rs.fields("empname")
b.cells(i,3).value=rs.fields("empsal")
rs.movenext
i=i+1
Loop
a.save
a.close

Above written script is for exporting database data to already exisiting excel sheet.
Database has a table called EMP with columns empno,empname,empsal.The values of these columns are exported to excel sheet.

Excel Sheet Creation and Writing Data Into It

Data Driven Using Excel Sheet

Data Driven Using Notepad

SQL Queries Collection

Movie Songs Lyrics

Script to Get Cell Value of Particular Row and Column from Data Table in QTP

How to get data from a particular row and a particular column from Data Table in QTP?

Check this Post for Datatable Methods.

QTP Script to get particular cell value from datatable in the specified row.

Below script is for retrieving 2rd row,2rd column cell value from datatable.

In datatable we have values as shown below

A B C
1 QQ xx
2 TT yy
3 PP zz

Script to get value "TT" from datatable is

Method 1

Syntax:Datatable.getsheet("Sheet Name"/Sheet Id).getparameter("Column Name").valuebyrow(Row Number)

msgbox Datatable.getsheet("Global").getparameter("B").valuebyrow(2)

Method 2

Syntax:datatable.SetCurrentRow(rownumber)

Cell Vaue=datatable.Value("ColumnName"/Column Number,"SheetName"/Sheet Id)

datatable.SetCurrentRow(2)

msgbox datatable.Value(2,1)


QTP Script to get RowCount of DataTable for Specific Column


QTP Script to get columncount,rowcount,column names of datatable

Script to Import Data From Database to Datatable in QTP

Below Script is for importing database(MS Access)data to runtime datatable in QTP.

Dim con,rs
Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")
con.provider="microsoft.jet.oledb.4.0"
con.open"d:\db.mdb"
rs.open"select*from EMP",con
datatable.GetSheet(1).addparameter"EMP_No",""
datatable.GetSheet(1).addparameter"EMP_Name",""
datatable.GetSheet(1).addparameter"EMP_Sal",""
row=1
Do While Not rs.EOF
datatable.SetCurrentRow(row)
datatable.Value (1,1)=rs.fields("empno")
datatable.Value(2,1)=rs.fields("empname")
datatable.Value(3,1)=rs.fields("empsal")
row=row+1
rs.movenext
Loop

"db" is one of the database in MS Access and "EMP" is table in db.
EMP Table has 3 columns(empno,empname,empsal).

empno empname empsal
1 aaa 1000
2 bbb 2000
3 ccc 3000

After running the script u can see EMP table data in runtime datatable.

Output:
EMP_No EMP_Name EMP_Sal
1 aaa 1000
2 bbb 2000
3 ccc 3000

QTP Script to Get Created Date and Time,Modified Date and Time & Accessed Date and Time of a File.

How to get created date and time of a particular file?

Set f1 = CreateObject("Scripting.FileSystemObject")
Set f2 = f1.GetFile("E:\Lak.txt")
S = "File was Created on: "&f2.DateCreated
Msgbox S

Output->File was Created on: 10/12/2008 3:45:16 PM

How to get last Modified date and time of a particular file?

Set f1 = CreateObject("Scripting.FileSystemObject")
Set f2 = f1.GetFile("E:\Lak.txt")
S = "File was Last Modified on: "&f2.Datelastmodified
Msgbox S

Output->File was Last Modified on: 10/12/2008 3:51:24 PM

How to get last accessed date and time of a particular file?

Set f1 = CreateObject("Scripting.FileSystemObject")
Set f2 = f1.GetFile("E:\Lak.txt")
S = "File was Last Accessed on: "&f2.Datelastaccessed
Msgbox S

Output->File was Last Accessed on: 10/12/2008 3:55:15 PM


Movie Songs Lyrics

SQL Queries

QTP Script to Get Names of Subfolders in a Folder

Script in QTP to get Collection(names) of Subfolders in a Folder.

Below script is to get Subfolders names in Folder V1
Where Folder V1 is in Fdrive.
Dim a,b, c, d, e
Set a = CreateObject("Scripting.FileSystemObject")
Set b = a.GetFolder("F:\V1")
Set c = b.SubFolders
For Each d in c
e=e&d.name&vbnewline
Next
msgbox e

Movie Songs Lyrics
SQL Queries

Related Posts Plugin for WordPress, Blogger...