ptrex 89 Posted December 7, 2007 (edited) Reading Excel data using SQLSomeone was asking what is the fastest way to determine the number of lines in an Excel sheet.Fast reading the number of records in ExcelWell the best way for speed is SQL of course, opposed to the EXCEL COM fucntions.Here's is how to get stared :expandcollapse popupConst $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 EndfuncThis way you can access an EXCEL files as a database.Regards,ptrex Edited September 14, 2012 by ptrex Hide ptrex's signature Hide all signatures Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM New Share this post Link to post Share on other sites
zfisherdrums 5 Posted December 8, 2007 Nice example. I've been using excel as a data store for application maps. ADO makes working with them so easy. : ) Hide zfisherdrums's signature Hide all signatures Identify .NET controls by their design time namesLazyReader© could have read all this for you. Unit Testing for AutoItFolder WatcherWord Doc ComparisonThis here blog... Share this post Link to post Share on other sites
ptrex 89 Posted December 8, 2007 @zfisherdrums Thanks The way ADO handles Excel gives in some occations more flexibilty to select data. Which is not possible using standard Excel functions. That's why I sometimes move over to this approach as well. Regards, ptrex Hide ptrex's signature Hide all signatures Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM New Share this post Link to post Share on other sites
MatteoGuallini 0 Posted November 27, 2008 I'm a newbye Where I can find documentation for implementing this way of using ado? Hide MatteoGuallini's signature Hide all signatures http://www.vigevano-prabis.it/ Share this post Link to post Share on other sites
ptrex 89 Posted November 28, 2008 @lapasucA good way to get started withe ADO is here :Learn ADOregards,ptrex Hide ptrex's signature Hide all signatures Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM New Share this post Link to post Share on other sites
MatteoGuallini 0 Posted November 28, 2008 Thanks a lot. Hide MatteoGuallini's signature Hide all signatures http://www.vigevano-prabis.it/ Share this post Link to post Share on other sites
Vishal85 0 Posted October 13, 2011 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. Share this post Link to post Share on other sites
Vishal85 0 Posted October 13, 2011 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() Share this post Link to post Share on other sites
maniootek 5 Posted January 13, 2016 I want to use this script but without variable $s_TablenameI want script read the first sheet name and read data from it.Any idea? Share this post Link to post Share on other sites
ptrex 89 Posted January 24, 2016 @maniootek The variable in the first script is pointing to sheet1 which is the first sheet. Global $s_Tablename = "[Sheet1$]" Rgds, ptrex Hide ptrex's signature Hide all signatures Contributions :Firewall Log Analyzer for XP - Creating COM objects without a need of DLL's - UPnP support in AU3Crystal Reports Viewer - PDFCreator in AutoIT - Duplicate File FinderSQLite3 Database functionality - USB Monitoring - Reading Excel using SQLRun Au3 as a Windows Service - File Monitor - Embedded Flash PlayerDynamic Functions - Control Panel Applets - Digital Signing Code - Excel Grid In AutoIT - Constants for Special Folders in WindowsRead data from Any Windows Edit Control - SOAP and Web Services in AutoIT - Barcode Printing Using PS - AU3 on LightTD WebserverMS LogParser SQL Engine in AutoIT - ImageMagick Image Processing - Converter @ Dec - Hex - Bin -Email Address Encoder - MSI Editor - SNMP - MIB ProtocolFinancial Functions UDF - Set ACL Permissions - Syntax HighLighter for AU3ADOR.RecordSet approach - Real OCR - HTTP Disk - PDF Reader Personal Worldclock - MS Indexing Engine - Printing ControlsGuiListView - Navigation (break the 4000 Limit barrier) - Registration Free COM DLL Distribution - WinRM SMART Analysis - COM Object Browser - Excel PivotTable Object - VLC Media Player - Windows LogOnOff Gui -Extract Data from Outlook to Word & Excel - Analyze Event ID 4226 - DotNet Compiler Wrapper - Powershell_COM New Share this post Link to post Share on other sites
water 2,164 Posted January 24, 2016 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. Hide water's signature Hide all signatures My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2019-10-24 - Version 1.4.14.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX (NEW 2019-11-30 - Version 1.4.0.0) - Download - General Help & Support - Example Scripts - WikiOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsPowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & SupportExcel - Example Scripts - WikiWord - WikiTask Scheduler (NEW 2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - WikiTutorials:ADO - Wiki Share this post Link to post Share on other sites