Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

ADOSQL with Excel


  • Please log in to reply
4 replies to this topic
flyingDman
  • Spam Officer
  • 2186 posts
  • Last active: Nov 07 2015 08:15 AM
  • Joined: 27 Feb 2009

I have used ADOSQL (see here) with CSV files and MS Access files but have failed with Excel files. I understand that the connection string should be something like:

con_string := "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = c:\myFolder\myExcel2007file.xlsx"

,but this has not worked.

 

Could someone share a working example?


Marine Corps Gen. Joseph Dunford told senators at his Joint Chiefs of Staff confirmation hearing : “If you want to talk about a nation that could pose an existential threat to the United States, I'd have to point to Russia. And if you look at their behavior, it's nothing short of alarming.”


Jackie Sztuk _Blackholyman
  • Spam Officer
  • 3757 posts
  • Last active: Apr 03 2016 08:47 PM
  • Joined: 28 Feb 2012

This seems to work for excel 2010 but i did not test it much

adOpenStatic = 3
adLockOptimistic = 3
adCmdText = 1
dataSource := A_ScriptDir . "\SampleData.xlsx"
 
objConnection := ComObjCreate("ADODB.Connection")
objRecordSet := ComObjCreate("ADODB.Recordset")
 
objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" dataSource "';Extended Properties='Excel 12.0;HDR=NO;IMEX=1';")
 
objRecordset.Open("Select * FROM [Sheet1$]", objConnection, adOpenStatic, adLockOptimistic, adCmdText)
 
pFields := objRecordset.Fields
for key, val in pFields
    Names .= key.Name . "|"
 
msgbox % Names

Hope it helps


Helping%20you%20learn%20autohotkey.jpg?d

[AHK] Version. 1.1+ [CLOUD] DropBox ; Copy [WEBSITE] Blog ; About

flyingDman
  • Spam Officer
  • 2186 posts
  • Last active: Nov 07 2015 08:15 AM
  • Joined: 27 Feb 2009

Thanks Blackholyman.

Using:

pFields := objRecordset.Fields
for key, val in pFields
    Names .= key.value " "
msgbox % Names

It got me the name of the fields (first row of my sheet1).  So it is communicating. But I was not able to get anything else. The intent is to query the excel database using SQL. Using ADOSQL, that is fairly easily for CSV and .mdb or .accdb files. Extremely powerful. 

 

Any further help is much appreciated!


Marine Corps Gen. Joseph Dunford told senators at his Joint Chiefs of Staff confirmation hearing : “If you want to talk about a nation that could pose an existential threat to the United States, I'd have to point to Russia. And if you look at their behavior, it's nothing short of alarming.”


Jackie Sztuk _Blackholyman
  • Spam Officer
  • 3757 posts
  • Last active: Apr 03 2016 08:47 PM
  • Joined: 28 Feb 2012

You can use the MoveNext method something like this

adOpenStatic = 3
adLockOptimistic = 3
adCmdText = 1
dataSource := A_ScriptDir . "\SampleData.xlsx"
 
objConnection := ComObjCreate("ADODB.Connection")
objRecordSet := ComObjCreate("ADODB.Recordset")
 
objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" dataSource "';Extended Properties='Excel 12.0;HDR=NO;IMEX=1';")
 
objRecordset.Open("Select * FROM [Sheet1$]", objConnection, adOpenStatic, adLockOptimistic, adCmdText)
 
while !(objRecordset.EOF)
{
    pFields := objRecordset.Fields
    for key, val in pFields
        msgbox % key.value
    objRecordset.MoveNext
}
 
return

at least that works for me... :)


Helping%20you%20learn%20autohotkey.jpg?d

[AHK] Version. 1.1+ [CLOUD] DropBox ; Copy [WEBSITE] Blog ; About

flyingDman
  • Spam Officer
  • 2186 posts
  • Last active: Nov 07 2015 08:15 AM
  • Joined: 27 Feb 2009

Thanks again. Whole lot more work than ADOSQL. Was looking for and hoped for a uniform approach between CSV, MDB and XLSX files.


Marine Corps Gen. Joseph Dunford told senators at his Joint Chiefs of Staff confirmation hearing : “If you want to talk about a nation that could pose an existential threat to the United States, I'd have to point to Russia. And if you look at their behavior, it's nothing short of alarming.”