Reading Excel data using SQL

[exodius]

Ptrex offered the following code in the examples forum some time ago and I'd really like to use it, but I can't seem to figure out how to word the query to return the actual contents of the cell instead of just counting them.

Would I even use the Recordset object for returning the values of the columns and rows?

expandcollapse popup

Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001 
Global $s_Filename=FileGetShortName("C:\Tmp\Test.xls") 
Global $s_Tablename = "[Sheet1$]" 

; Initialize COM error handler 
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

; Source XLS data
$objConnection = ObjCreate("ADODB.Connection")
$objRecordSet = ObjCreate("ADODB.Recordset")
$objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&$s_Filename&";Extended Properties=""Excel 8.0;HDR=Yes;"";")
$objRecordSet.Open ("Select Count(*) FROM"& $s_Tablename & "Order by 1 Asc", $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText) 

Do 
    ConsoleWrite ($objRecordSet.Fields(0).value+1 &@CR) ; + 1 because it is 0 based
    $objRecordSet.MoveNext()
Until $objRecordSet.EOF()

$objConnection.Close
$objConnection = ""
$objRecordSet = ""

    
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

[weaponx]

The query should be something like "Select * FROM "& $s_Tablename

See this post for some tips:

#477896

Edited March 24, 2008 by weaponx

[exodius]

The query should be something like "Select * FROM "& $s_Tablename

See this post for some tips:

#477896

Thanks for the advice weaponx, yeah, the answer wasn't too tough. My question now, is does anybody know what the invocation to get a listing of the sheet names is? I've looked on the net and I'm not finding a good example of this...

expandcollapse popup

Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001 
Global $s_Filename=FileGetShortName("Testy.xls") 
Global $s_Tablename = "[Sheet1$]" 

; Initialize COM error handler 
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

; Source XLS data
$objConnection = ObjCreate("ADODB.Connection")
$objRecordSet = ObjCreate("ADODB.Recordset")
$objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&$s_Filename&";Extended Properties=""Excel 8.0;HDR=Yes;"";")
$objRecordSet.Open ("Select * FROM " & $s_Tablename, $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText) 

Do
    For $x In $objRecordSet.Fields
       MsgBox (0, "", "Column: " & @CRLF & $x.name & @CRLF & "Value: " & $x.value)
    Next
    
    $objRecordSet.MoveNext
Until $objRecordSet.EOF

$objConnection.Close
$objConnection = ""
$objRecordSet = ""

    
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

[exodius]

Okay, so I found the following code out at http://www.thescarms.com/dotnet/Schema.aspx and don't understand how to convert the part that actually gets that Table (Worksheets) information. Could any of you AutoIt COM gurus take a peek and give me your input?

expandcollapse popup

Dim i As Integer
    Dim dtXlsSchema As DataTable
    Dim myConn As New OleDbConnection
    Dim XlsConn As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data " & _
        "Source=C:\temp\myWorksheet.xls;" & _
        "Extended Properties=Excel 8.0"
    '
    ' Open an ADO connection to the Excel file.
    '
    myConn.ConnectionString = XlsConn
    myConn.Open()
    '
    ' Get a list of tables (worksheets) in the XLS file.
    '
    dtXlsSchema = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                      New Object() {Nothing, Nothing, Nothing, "TABLE"})

    For i = 0 To dtXlsSchema.Rows.Count - 1
        Debug.WriteLine(dtXlsSchema.Rows(i).Item("Table_Name").ToString)
    Next
    '
    ' Get the schema for the specified table.
    ' Change "MyTableName" to the actual worksheet name.
    '
    dtXlsSchema = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
                      New Object() {Nothing, Nothing, "MyTableName$", "TABLE"})

    For i = 0 To dtXlsSchema.Columns.Count - 1
        Debug.WriteLine(dtXlsSchema.Columns(i).ToString)
    Next
    '
    ' List the columns for the specified table.
    '
    dtXlsSchema = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
                      New Object() {Nothing, Nothing, "MyTableName$", Nothing})

    For i = 0 To dtXlsSchema.Rows.Count - 1
        Debug.WriteLine(dtXlsSchema.Rows(i).Item("Column_Name").ToString)
    Next

    myConn.Close()

[exodius]

Just to follow-up, ptrex hooked me up with this awesome code below, thanks ptrex!

expandcollapse popup

Const $adSchemaTables = 20
Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001 ;  =&H0001
Global $s_Filename=FileGetShortName("C:\Test.xls") 

; Initialize COM error handler 
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

$objConnection = ObjCreate("ADODB.Connection")
$objRecordSet = ObjCreate("ADODB.Recordset")

$objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source="&$s_Filename&";" & _
                "Extended Properties=""Excel 8.0;HDR=Yes;"";")

ConsoleWrite(_adoListTables() & @LF)
ConsoleWrite( @LF)

Func _adoListTables()
    Local $oList = ''
    If IsObj($objConnection) = 0 Then Return SetError(1)
    $oRec = $objConnection.OpenSchema($adSchemaTables)
    
    While NOT $oRec.EOF
        If StringLen( $oRec("TABLE_TYPE").value) > 5 Then;; Skip the hidden internal tables
            $oRec.movenext
            ContinueLoop
        EndIf
        $oList = $oList & $oRec("TABLE_NAME").value & ' | '
        $oRec.movenext
    Wend
    
    If $oList <> '' Then
        Return '|' & StringTrimRight($oList,1)
    Else
        SetError(3, 0, 0)
        Return $oList
    EndIf
EndFunc    ;<===> _adoListTables()


$objConnection.Close
$objConnection = ""
$objRecordSet = ""

Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

[weaponx]

Ptrex is the man.

[ptrex]

@weaponx

What can I say ?!

ptrex