Sign in to follow this  
Followers 0
Ace08

Excel Query using SQL

4 posts in this topic

#1 ·  Posted (edited)

Hi again i found a script that ptrex made, it was originaly made to read an excel file as a database and counting all the records. what i did was query all the records however i can only get only one column record.

this one works.

Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001
Global $s_Filename=FileGetShortName("D:\My Documents\PROGRAMS\DataBase Update\8082009-042011.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 CustomerID FROM "& $s_Tablename , $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText)


Do
    ConsoleWrite ($objRecordSet.Fields(0).value & $objRecordSet.Fields(1).value & @CR)
    $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

but if i query 2 fields at the same time like and setting $objRecordSet.Fields(test).value i am getting errors

$objRecordSet.Open ("Select CustomerID,CustFName as Test FROM "& $s_Tablename , $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText)
$objRecordSet.Fields(test).value

any help will be appreciated Thanks.

Edited by Ace08

Work smarter not harder.My First Posted Script: DataBase

Share this post


Link to post
Share on other sites



try this:
$objRecordSet.Open ("Select * FROM"& $s_Tablename & "Order by 1 Asc" , $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText)

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

@ChrisL Thanks will look into this in the morning :>

@Juvigy i've tried your code and i'm still getting errors i can only query 1 row at a time :unsure:

Edited by Ace08

Work smarter not harder.My First Posted Script: DataBase

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