Sign in to follow this  
Followers 0
exodius

Reading Excel data using SQL

7 posts in this topic

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?

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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

See this post for some tips:

#477896

Edited by weaponx

Share this post


Link to post
Share on other sites

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...

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

Share this post


Link to post
Share on other sites

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?

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()

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

Ptrex is the man.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0