Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

Reading Excel data using SQL


  • Please log in to reply
7 replies to this topic

#1 ptrex

ptrex

    Universalist

  • MVPs
  • 2,420 posts

Posted 07 December 2007 - 08:28 AM

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 :

AutoIt         
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, 14 September 2012 - 09:13 AM.








#2 zfisherdrums

zfisherdrums

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 393 posts

Posted 08 December 2007 - 06:54 AM

Nice example. I've been using excel as a data store for application maps. ADO makes working with them so easy. : )

#3 ptrex

ptrex

    Universalist

  • MVPs
  • 2,420 posts

Posted 08 December 2007 - 04:47 PM

@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

#4 MatteoGuallini

MatteoGuallini

    Wayfarer

  • Active Members
  • Pip
  • 55 posts

Posted 27 November 2008 - 10:51 PM

I'm a newbye
Where I can find documentation for implementing this way of using ado?

#5 ptrex

ptrex

    Universalist

  • MVPs
  • 2,420 posts

Posted 28 November 2008 - 10:19 AM

@lapasuc

A good way to get started withe ADO is here :

Learn ADO

regards,

ptrex

#6 MatteoGuallini

MatteoGuallini

    Wayfarer

  • Active Members
  • Pip
  • 55 posts

Posted 28 November 2008 - 10:52 AM

Thanks a lot.

#7 Vishal85

Vishal85

    Seeker

  • Active Members
  • 41 posts

Posted 13 October 2011 - 01:59 AM

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.

#8 Vishal85

Vishal85

    Seeker

  • Active Members
  • 41 posts

Posted 13 October 2011 - 03:09 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users