Jump to content

Reading Excel data using SQL


ptrex
 Share

Recommended Posts

Reading Excel data using SQL

Someone was asking what is the fastest way to determine the number of lines in an Excel sheet.

Fast reading the number of records in Excel

Well the best way for speed is SQL of course, opposed to the EXCEL COM fucntions.

Here's is how to get stared :

Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001
Global $s_Filename=FileGetShortName("C:TmpTest.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

This way you can access an EXCEL files as a database.

Regards,

ptrex

Edited by ptrex
Link to comment
Share on other sites

Link to comment
Share on other sites

  • 11 months later...
  • 2 years later...

Thanks for sharing this info....It helped me a lot...I believe its a faster way to read data from excel.

I am looking for a way to insert data into excel using ADO, SQL queries. I googled and also searched on this forum but its not working for me. Can you please provide me some direction or give some sample code to insert data into excel using ADO and SQL queries. I am able to connect to excel and read data from it but i am not able to insert data in it. Please help. I dont want to use the standard excel udf functions as i think ADO is a faster way and i need to insert data in excel very heavily.

Link to comment
Share on other sites

I got this working....The issue was with IMEX value in the connection object for excel file...IMEX accepts values 0 (use native type) and 1 (considering everything as string) only....I changed IMEX to 0 and all worked fine.....Found this after doing some reading and some digging....Thanks everybody!!

Sample code here...Hope it helps!!

$s_DataFileName = "C:\test.xls"

Global $s_Filename = FileGetShortName($s_DataFileName)

Dim $oConn

$oConn = ObjCreate("ADODB.Connection")

$oConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & $s_Filename & ";" & _

"Extended Properties=""Excel 8.0;HDR=YES;IMEX=0;"";")

$oConn.Execute("INSERT INTO [sheet1$](TEstCol1, TestCol2, TestCol3) VALUES ('Nice one', 'Testttt', 'Hi there')")

$oConn.Close()

Link to comment
Share on other sites

  • 4 years later...
  • 2 weeks later...
Link to comment
Share on other sites

NB: The names of the sheets vary depending on the language (or if manually changed by a user). A script would run more reliable if the sheet number could be specified.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...