QTP : Excel As DataBase

 Excel As DataBase

Create an excel book in "c:\1.xlsx" and  name any sheet as "Sheet1" (Default name). In  Sheet 1 , the 1st row is the column names , the rest of the rows forms the data .


CODE BELOW:

Dim objCon, objRecordSet, strExlFile, colCount, row, i

Set objCon = CreateObject("ADODB.Connection")

Set objRecordSet = CreateObject("ADODB.Recordset")

sPath="c:\1.xlsx"

sSheetname="Sheet1"

objCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="& sPath &";Extended Properties=Excel 12.0;Persist Security Info=False"

strSQLStatement = "SELECT * FROM ["& sSheetname &"$]"

objRecordSet.Open strSQLStatement, objCon 'create recordset

colCount = objRecordSet.Fields.count 'Number of columns in the table

While objRecordSet.EOF=false

row=""

For i=0 to colCount-1

row=row &" "& objRecordSet.fields(i)

Next

'Print row 'If you are running this in Qtp you can uncomment this line

msgbox row

objRecordSet.moveNext

Wend

Set objRecordSet = Nothing

objCon.Close

Set objCon = Nothing

Database operation in QTP (borrowed from http://www.qtpschool.com)

DataBase_Connection() creates a new connection to a database.
There are two arguments passed to this function -
1. sessionName - the name of the session (string)
2. connection_string - a connection string, for example the connection_string can be "DSN=SQLServer_Source;UID=SA;PWD=xyz123". Please note that the connection string will vary as per your database details.

Con = <name of the session>
SQL="SELECT * FROM Your_Table"
con_string="DSN=SQLServer_Source;UID=SA;PWD=xyz123"
isConnected = DataBase_Connection (Con , con_string)
'Now check if connection is successful. Function will return zero if connection is successful.
If isConnected = 0 then
    'Execute your SQL statement
    set myrs = Con.Execute(SQL)

    'Retrieve values from the recordset
    print "val - row 0 col 0: " & db_get_field_value( myrs , 0 , 0 )
    print "val - row 0 col 1: " & db_get_field_value( myrs , 0 , 1 )
End If

Con.close
Set Con = Nothing 'Disconnect database
'-----------------------------------------------------------------------------------------------
Function DataBase_Connection(sessionName,connection_string)
    Dim oConnection
    on error Resume next
    ' Opening connection
    set oConnection = CreateObject("ADODB.Connection")

    If Err.Number <> 0 then
        DataBase_Connection= "Error :- " & CStr(Err.Number) & " " & Err.Description
        Err.clear
        Exit Function
    End If
 
    oConnection.Open connection_string
oConnection.CommandTimeout = 120  'modify this value if needed. 
    If Err.Number <> 0 then
        DataBase_Connection= "Error := " & CStr(Err.Number) & " " & Err.Description
        err.clear
        Exit Function
    End If
    set sessionName = oConnection
    DataBase_Connection = 0
End Function

We need another function to retrieve data from record set.

Function db_get_field_value( myrs , rowNum, colNum )
    dim curRow
    myrs.MoveFirst
    count_fields = myrs.fields.count-1
    If ( TypeName(colNum)<> "String" ) and ( count_fields < colNum ) then
        db_get_field_value = -1 'requested field index more than exists in recordset
    Else
        myrs.Move rowNum
        db_get_field_value = myrs.fields(colNum).Value
    End If
End Function