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
QTP Script to Export Database Data to Excel Sheet
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
