AutoHotkey Community
It is currently May 27th, 2012, 12:01 pm

View unanswered posts | View active topics


Board index » AutoHotkey » Scripts » Custom

All times are UTC [ DST ]



Post new topic Reply to topic  Page 1 of 1
  [ 9 posts ] 
  Print view | E-mail friend Previous topic | Next topic 
Author Message
shajul
PostPosted: December 9th, 2010, 6:48 pm 
Offline

Joined: September 15th, 2006, 10:25 am
Posts: 567
Using ADO to Query an Excel Spreadsheet

Example on how to use ADODB to query an Excel file instead of Excel.Application class.
Hence Excel sheets can function as a flat-file database and you send SQL queries to Excel and get back information.

ScreenShot:
Image

Code:
adOpenStatic = 3
adLockOptimistic = 3
adCmdText = 1
dataSource := A_ScriptDir . "\SampleData.xls"

objConnection := ComObjCreate("ADODB.Connection")
objRecordSet := ComObjCreate("ADODB.Recordset")

objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" . dataSource . ";Extended Properties=""Excel 8.0;HDR=Yes;"";")

objRecordset.Open("Select * FROM [Sheet1$]", objConnection, adOpenStatic, adLockOptimistic, adCmdText)
Gui, Font, S10 CDefault, Verdana
Gui, Add, Text, x12 y10 h20 , SELECT * from [Sheet1$] WHERE
Gui, Add, DropDownList, R10 x+5 y8 w100 h25 vselMain gUpdateGo,
Gui, Add, Text, x+5 y10 h20 , =
Gui, Add, DropDownList, R10 x+5 y8 w100 h25 vselItem gGo,
pFields := objRecordset.Fields
Loop, % pFields.Count
    tNames .= pFields.Item(A_Index-1).Name . "|"
Gui, Add, ListView, x2 y40 w480 h340 vMyLV, % SubStr(tNames,1,-1)
GuiControl,,selMain,% "|" . SubStr(RegExReplace(tNames,"^.+?\|",""),1,-1) ;RegExReplace removes the first field, DATETIME not supported yet
strObj := Object()
Loop
{
    pFields := objRecordset.Fields
    Loop, % pFields.Count
        strObj[A_Index] := pFields.Item(A_Index-1).Value
    LV_Add("",strObj.1,strObj.2,strObj.3,strObj.4,strObj.5,strObj.6,strObj.7,strObj.8,strObj.9,strObj.10)
    objRecordset.MoveNext
} Until objRecordset.EOF
strObj.Remove(strObj.MinIndex(),strObj.MaxIndex())
LV_ModifyCol()
Gui, Show, w487 h384, ADODB Test
return

UpdateGo:
Gui,Submit,NoHide
objRecordset := objConnection.Execute("Select ``" . selMain . "`` FROM [Sheet1$]")
Loop
{
    pFields := objRecordset.Fields
    tvar := pFields.Item(0).Value
    if tvar not in %str%
        str .= pFields.Item(0).Value . "`,"   
    objRecordset.MoveNext
} Until objRecordset.EOF
StringReplace,str,str,`,,|,All
str := "|" . SubStr(str,1,-1)
GuiControl,,selItem,%str%
str=
GuiControl,Choose,selItem,1

Go:
Gui,Submit,NoHide
LV_Delete()
if selItem is not number
   selItem := "'" . selItem . "'"
IfInString, selItem,/
    StringReplace,selItem,selItem,/,//,All

objRecordset := objConnection.Execute("Select * FROM [Sheet1$] Where ``" . selMain . "`` = " . selItem)
Loop
{
    pFields := objRecordset.Fields
    Loop, % pFields.Count
        strObj[A_Index] := pFields.Item(A_Index-1).Value
    LV_Add("",strObj.1,strObj.2,strObj.3,strObj.4,strObj.5,strObj.6,strObj.7,strObj.8,strObj.9,strObj.10)
    strObj.Remove(strObj.MinIndex(),strObj.MaxIndex())
    objRecordset.MoveNext
} Until objRecordset.EOF
Return

GuiClose:
objRecordSet.Close()
objConnection.Close()
ExitApp


Download -> SampleData.xls

Image

Sorry the code is not well commented :(


Last edited by shajul on December 23rd, 2010, 12:07 pm, edited 3 times in total.

Report this post
Top
 Profile  
Reply with quote  
Learning one
 Post subject:
PostPosted: December 9th, 2010, 8:39 pm 
Offline
User avatar

Joined: April 4th, 2009, 8:19 pm
Posts: 1143
Location: Croatia
Very nice example shajul. The effect is very similar to [object]Table on which I'm working on...


Report this post
Top
 Profile  
Reply with quote  
shajul
 Post subject:
PostPosted: December 10th, 2010, 6:43 am 
Offline

Joined: September 15th, 2006, 10:25 am
Posts: 567
Learning one wrote:
Very nice example shajul. The effect is very similar to [object]Table on which I'm working on...


Thank you. I have updated the example to include selecting which column to query also..
Eagerly waiting to see your approach with object Table :)


Report this post
Top
 Profile  
Reply with quote  
Andi
 Post subject:
PostPosted: December 23rd, 2010, 4:52 am 
Offline

Joined: February 11th, 2005, 6:31 am
Posts: 174
Location: Germany
very interesting :D

...but the script crashes with the colums OrderDate, Units, Unit Cost and Total. Whats wrong here?

Quote:
---------------------------
B0582697.ahk
---------------------------
Error: 0x80040E07 -
Source: Microsoft JET Database Engine
Description: Datentypen in Kriterienausdruck unverträglich.
HelpFile: (null)
HelpContext: 5003071

Specifically: Execute

Line#
050: str := "|" . SubStr(str,1,-1)
051: GuiControl,,selItem,%str%
052: str =
053: GuiControl,Choose,selItem,1
056: Gui,Submit,NoHide
057: LV_Delete()
058: selItem := """" . selItem . """"
---> 059: objRecordSet := objConnection.Execute("Select * FROM [Sheet1$] Where " . selMain . "=" . selItem)
060: Loop
061: {
062: pFields := objRecordset.Fields
063: Loop,pFields.Count
064: strObj[A_Index] := pFields.Item(A_Index-1).Value
065: LV_Add("",strObj.1,strObj.2,strObj.3,strObj.4,strObj.5,strObj.6,strObj.7,strObj.8,strObj.9,strObj.10)
066: strObj.Remove(strObj.MinIndex(),strObj.MaxIndex())

Continue running the script?
---------------------------
Ja Nein
---------------------------


Report this post
Top
 Profile  
Reply with quote  
shajul
 Post subject:
PostPosted: December 23rd, 2010, 9:29 am 
Offline

Joined: September 15th, 2006, 10:25 am
Posts: 567
Yes, thanks for testing and reporting the bug.
It was related to how strings and numbers are handled and columns with spaces are handled.

I have corrected them in the original post. Still, you can see that column "OrderDate" throws up an error as SQL DATETIME is not implemented in my code and needs more research. Going for Christmas vacation - will look it up later :)
Merry Christmas!


Report this post
Top
 Profile  
Reply with quote  
Myself
 Post subject:
PostPosted: January 25th, 2011, 7:02 pm 
Hi shajul,

It's a nood question : can you explain why you use "``"" and ""``" in :
Quote:
Where ``" . selMain . "`` = " . selItem
, please ?

I seen that other things dont work but I want to know when I must use this notation, and what is the reason to use it.

Thanks in advance.


Report this post
Top
  
Reply with quote  
Andi
 Post subject:
PostPosted: January 26th, 2011, 4:29 am 
Offline

Joined: February 11th, 2005, 6:31 am
Posts: 174
Location: Germany
The ahk-help says:
Quote:
` (literal accent; i.e. two consecutive escape characters result in a single literal character)


I tried shajul's first version, and for me this code does the job in the same way. So there seems to be no difference in behavior between

Code:
objRecordset := objConnection.Execute("Select * FROM [Sheet1$] Where " . selMain . "=" . selItem)

and
Code:
objRecordset := objConnection.Execute("Select * FROM [Sheet1$] Where ``" . selMain . "`` = " . selItem)


although the var selmain is wrapped. See the difference here:
Code:
selMain = Region
selItem = Quebeck
obj := "Select * FROM [Sheet1$] Where ``" . selMain . "`` = " . selItem
obj_1 := "Select * FROM [Sheet1$] Where " . selMain . "=" . selItem
msgbox %obj%`n%obj_1%



@shajul: The reason for my crash has somthing to do with the country settings
Quote:
Error: 0x80040E07 -
Source: Microsoft JET Database Engine
Description: Datentypen in Kriterienausdruck unverträglich.
HelpFile: (null)
HelpContext: 5003071

The german setting uses a "," instead of a "." as decimal point.


Report this post
Top
 Profile  
Reply with quote  
shajul
 Post subject:
PostPosted: January 26th, 2011, 6:18 am 
Offline

Joined: September 15th, 2006, 10:25 am
Posts: 567
Andi wrote:
So there seems to be no difference in behavior..


There is a difference only if the value of SelMain contains a space, eg
Code:
SelMain := "Unit Cost"
then the SQL query has to be
Code:
Where `Unit Cost`= %SelItem%


This has more to do with SQL rather than AHK, so you must understand the basics of SQL.

It is best to use ` whenever SelMain contains a string.

You are correct in that
Autohotkey Help wrote:
` (literal accent; i.e. two consecutive escape characters result in a single literal character)

_________________
If i've seen further it is by standing on the shoulders of giants

my site | ~shajul | WYSIWYG BBCode Editor


Report this post
Top
 Profile  
Reply with quote  
Myself
 Post subject:
PostPosted: January 26th, 2011, 8:03 am 
Thanks Andi and shajul,
I understand the reason of the usage of `. So it's better to use it when you don't know if there is a space or not.
Thanks a lot.


Report this post
Top
  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  Page 1 of 1
  [ 9 posts ] 

Board index » AutoHotkey » Scripts » Custom

All times are UTC [ DST ]

Who is online

Users browsing this forum: No registered users and 4 guests

You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Powered by phpBB® Forum Software © phpBB Group