Generic Function to read the data from Excel/sql database using ADODB connection

[supputuri]

Hi guys,

this function will increase the performance of your script as we are using the ADODB connection to retrieve the values from excel (treated as DB).

Code:

'********************************************************************************************************************************
'     Function Name: DataReader()
'     Purpose:  Returns query output from either SQl database or Excel spreadsheet using ADODB connection
'                        
'     Inputs:    DataPath as string
'                 Table as string
'                OutptValues as string
'               ConditionNm as string
'               ConditionValue as string
'    Return Values:    Semi-colon seperated result set as string
'     Author: Sridhar Upputuri
'     Creation Date:  5/25/2012                                            
'    Requirement Files:
'    Add-in: Web,    .NET
'    Modification History
'    Date                    Author                Change
'
'********************************************************************************************************************************
Public Function DataReader(arrVar)
    DataPath = Valid(arrVar,0)
    Table = Valid(arrVar,1)
    OutputValues = Valid(arrVar,2)
    ConditionNm = Valid(arrVar,3)
    ConditionValue = Valid(arrVar,4)
    DataType = Valid(arrVar,4)
    Dim rs, rsC, cnn, firstCol
    Dim myPath
    Dim strClose
    Dim fld
    Dim fldCat
    Dim intCount
    Dim DataFormat, boolNegative

    Set cnn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    Set rsC = CreateObject("ADODB.Recordset")

    'Check if it is a Excel file path or SQL instance and specify the connection string appropriately
    If InStr(DataPath,".xls") > 0 Then
        'It is an Excel file path
        cnn.Open("Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & DataPath & ";ReadOnly=1; Extended Properties='IMEX=1'")
        strClose = "$]"
    Else
        'It is a SQL instance
        cnn.Open("Provider=SQLOLEDB;Integrated Security=SSPI; PersistSecurity Info=False; Initial Catalog=" &DataTable("Database",dtGlobalSheet) & "; Data Source=" & DataPath)
        strClose = "]"
    End If

    

    'Construct the query string
    If OutputValues = "" Then OutputValues = "*"

    If Not ConditionNm = "" Then
        arrConditionNm = Split(ConditionNm, ";")
        arrConditionValue = Split(ConditionValue, ";")
        intLoop = 0
        rsC.Open "SELECT * FROM [" & Table & strClose, cnn
        For each strConditionNm in arrConditionNm
            boolNegative = False
            '    Identify negative condition
            If LCase(Left(strConditionNm,4)) = "not " Then
                boolNegative = True
                strConditionNm = Trim(Mid(strConditionNm, 5))
            End If
           'Find the dataType for the Condition name
           For intCount = 0 To rsC.Fields.Count -1
                If LCase(rsC.Fields(intcount).name) = LCase(strConditionNm) Then
                    DataFormat= CStr(rsC.Fields(intCount).Type)
                    If DataFormat = "200" Or DataFormat = "130" Or DataFormat = "202"then
                        'The Dataformat is not varChar
                        DataType  = "varChar"            
                    End If
                    Exit For
                End If
            Next

            If boolNegative Then
                strConditionNm = "Not " & strConditionNm
            End If
            If DataType = "varChar" then
                If intLoop = 0 Then
                    strCondition = strConditionNm & "='" & arrConditionValue(intLoop) & "'"
                Else
                    strCondition = strCondition & " AND " & strConditionNm & "='" & arrConditionValue(intLoop) & "'"
                End If
            Else
                If intLoop = 0 Then
                    strCondition = strConditionNm & "=" & arrConditionValue(intLoop)
                Else
                    strCondition = strCondition & " AND " & strConditionNm & "=" & arrConditionValue(intLoop)
                End If
            End If
            DataType  = ""
            intLoop = intLoop + 1
        Next

        'close the first recordset
        rsC.Close
        
        rs.Open "SELECT " & OutputValues & " FROM [" & Table & strClose & " WHERE " & strCondition, cnn
    Else
        rs.Open "SELECT " & OutputValues & " FROM [" & Table & strClose, cnn
    End If

    firstCol = true
    Do While Not rs.EOF
        For Each fld In rs.Fields
            If firstCol Then
                fldCat = fldCat & fld
                firstCol = false
            Else
                fldCat = fldCat & ";" & fld
            End If
        Next
        DataReader = fldCat
        Exit Do    '    Exit after first record
    Loop
    
    'clean up variables
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set rsC = Nothing
    Set cnn = Nothing
End Function

Function Valid(arrayVar,arrayIndex)
    If ubound(arrayVar)<arrayIndex or lbound(arrayVar)>arrayIndex Then
        Valid=""
    Else
        Valid=arrayVar(arrayIndex)
    End If
End Function

'Create an excel file with name as "Test" and name the first sheet as "Table1". Then add below columns
1) UserName
2) Environment
3) PWD
4) ActiveUser

Now fill the dummy data in the file and make sure that you have "Sridhar" as one of the username in the data.
And now try to execute the below LOC.


Function Call:

Code:

myoutput = DataReader(Array("C:\Test","Table1","PWD,ActiveUser,Environment","UserName","Sridhar"))
msgbox myoutput

Please let me know if any body have issues using this function or have any suggestions.