Jump to content

Read .XLSX with ADO anomaly


Recommended Posts

I'm working on retrieving data from a xlsx file using a sql query and ADO, this is a standalone snippet:

#include <_Array.au3>
Global Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ; adLockReadOnly
Global Const $iOptions = 512 ; adCmdTableDirect - Return all rows from the specified table

Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object

Global $sFilename = @ScriptDir & "\all-airport-data-abridged.xlsx" ; debug file
;Global $sFilename = @ScriptDir & "\all-airport-data.xlsx"

Global $sConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & $sFilename & ';Extended Properties="Excel 8.0;HDR=No"'
Global $sConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' & $sFilename & ';Extended Properties="Excel 12.0 Xml;HDR=NO"'

$oConnection.Open($sConnectionString) ; Open the connection
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sSQL_Query = "Select * FROM [Airports$A:zz3]" ; Select all records and all fields of the selected range
$oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query

Global $array = $oRecordset.GetRows(-1)
_ArrayDisplay($array)
exit

There seems to be a problem reading the first line with the field names in some columns when the remaining data in the column is numeric, unless I only read one or two rows.  But not all, e.g. columns 104-105 where it's numeric but the field name does come through.  In the line where I create the query:

Global $sSQL_Query = "Select * FROM [Airports$A:zz3]"

As written above, I get no data for the first line in columns 3 and 27 (and maybe more, I didn't check them all):

image.png.68241867583900ef3876a1a8b572ce38.png

But if I change it to zz2 or zz1, I do get data.

image.png.12e15c00c07ca595ae3eb1fc139d9dc1.png

 

Any ideas?

I didn't create the xlsx file so I have no control over how it's formatted (the abridged file attached is chopped out of the original, which has ~20K records).  Nor can I count on the user of the script having MS Office installed so I can't use the Excel UDF.

The workaround is simple, just read the first line to get the names, then read the rest of the data.  But why is it happening?

all-airport-data-abridged.xlsx

Link to post
Share on other sites

I had similar problem, searched the forum and found the reasons behind it.

In the end I used this lil gem by AspirinJunkie:

; #FUNCTION# ======================================================================================
; Name ..........: _xlsx_2Array
; Description ...: reads single worksheets of an Excel xlsx-file into an array
; Syntax ........: _xlsx_2Array(Const $sFile [, Const $sSheetNr = 1 [, $dRowFrom = 1 [, $dRowTo = Default]]])
; Parameters ....: $sFile      - path-string of the xlsx-file
;                  $sSheetNr   - number (1-based) of the target worksheet
;                  $dRowFrom   - row number (1-based) where to start the extraction
;                  $dRowTo   - row number (1-based) where to stop the extraction
; Return values .: Success - Return 2D-Array with the worksheet content
;                  Failure - Return False and set @error to:
;                       @error = 1 - error importing shared-string list (@extended = @error of __xlsx_readSharedStrings)
;                              = 2 - error reading cell-values (@extended = @error of __xlsx_readCells)
;                              = 3 - error unpacking the xlsx-file (@extended = @error of __unzip)
; Author ........: AspirinJunkie
; Last changed ..: 2020-07-27
; =================================================================================================
Func _xlsx_2Array(Const $sFile, Const $sSheetNr = 1, $dRowFrom = 1, $dRowTo = Default)
    Local $pthWorkDir = @TempDir & "\xlsxWork\"
    Local $pthStrings = $pthWorkDir & "xl\sharedStrings.xml"

    ; correct wrong values for  $dRowFrom and $dRowTo
    If $dRowFrom < 1 Or Not IsInt($dRowFrom) Then $dRowFrom = 1
    If $dRowFrom > $dRowTo Then
        $dRowFrom = 1
        $dRowTo = Default
    EndIf

    ; how many sheets
;~  Local $sRead = FileRead($sFile)
;~  cw("$sRead: " & $sRead)
;~  If @error Then cw("FileRead.Err: " & @error & " Ext: " & @extended)
;~  Local $aTotSheets = StringRegExp($sRead, "sheet.xml sheet" & $sSheetNr & ".xml", 1)
;~  If @error Then cw("StringRegExp.Err: " & @error & " Ext: " & @extended)
;~  If IsArray($aTotSheets) Then a2c($aTotSheets, "$aTotSheets")

    ; unpack xlsx-file
    __unzip($sFile, $pthWorkDir, "shared*.xml sheet.xml sheet" & $sSheetNr & ".xml")
    If @error Then Return SetError(3, @error, False)

    Local $pthSheet = FileExists($pthWorkDir & "xl\worksheets\sheet.xml") ? $pthWorkDir & "xl\worksheets\sheet.xml" : $pthWorkDir & "xl\worksheets\sheet" & $sSheetNr & ".xml"

    ; read strings into an 1D-array
    Local $aStrings = __xlsx_readSharedStrings($pthStrings)
    If @error Then Return SetError(1, @error, False)

    ; read all cells into an 2D-array
    Local $aCells = __xlsx_readCells($pthSheet, $aStrings, $dRowFrom, $dRowTo)
    If @error Then Return SetError(2, @error, False)

    ; remove temporary data
    DirRemove($pthWorkDir, 1)

    Return $aCells
EndFunc   ;==>_xlsx_2Array


#Region xlsx specific helper functions

; #FUNCTION# ======================================================================================
; Name ..........: __xlsx_readCells
; Description ...: import xlsx worksheet values
; Syntax ........: __xlsx_readCells(Const $sFile, ByRef $aStrings [, $dRowFrom = 1 [, $dRowTo = Default]])
; Parameters ....: $sFile      - path-string of the worksheet-xml
;                  $aStrings   - array with shared strings (return value of __xlsx_readSharedStrings)
;                  $dRowFrom   - row number (1-based) where to start the extraction
;                  $dRowTo   - row number (1-based) where to stop the extraction
; Return values .: Success - Return 2D-Array with the worksheet content
;                  Failure - Return False and set @error to:
;                       @error = 1 - cannot create XMLDOM-Object
;                              = 2 - cannot open worksheet file
;                              = 3 - cannot extract cell objects out of the xml-structure
;                              = 4 - cannot determine worksheet dimensions
;                              = 5 - wrong string id in shared-string value
; Author ........: AspirinJunkie
; Last changed ..: 2020-07-27
; =================================================================================================
Func __xlsx_readCells(Const $sFile, ByRef $aStrings, Const $dRowFrom = 1, $dRowTo = Default)
    Local $oXML = __xlsx_getXMLObject()

    If Not $oXML.load($sFile) Then Return SetError(2, 0, False)

    ; determine the namespace prefix:
    Local $sPre = $oXML.documentElement.prefix
    If $sPre <> "" Then $sPre &= ":"

    ; select the cell-nodes (but only if they have a value-child)
    Local $oCells = $oXML.selectNodes('/' & $sPre & 'worksheet/' & $sPre & 'sheetData/' & $sPre & 'row/' & $sPre & 'c[' & $sPre & 'v]')
    If Not IsObj($oCells) Then Return SetError(3, 0, False)

    ; determine dimensions:
    Local $dColumnMax = 1, $dRowMax = 1, $sR, $aCoords
    For $oCell In $oCells
        $sR = $oCell.GetAttribute("r")
        $aCoords = __xlsx_CellstringToRowColumn($sR)
        $oCell.SetAttribute("zeile", $aCoords[1])
        $oCell.SetAttribute("spalte", $aCoords[0])
        If $aCoords[0] > $dColumnMax Then $dColumnMax = $aCoords[0]
        If $aCoords[1] > $dRowMax Then $dRowMax = $aCoords[1]
    Next

    ; create output array
    If $dRowTo <> Default Then $dRowMax = $dRowTo > $dRowMax ? $dRowMax : $dRowTo
    Local $aRet[$dRowMax - $dRowFrom + 1][$dColumnMax]

    ; read cell values
    Local $i = 0, $sTmp, $sValue
    For $oCell In $oCells
        $i += 1

        Local $dRow = $oCell.GetAttribute("zeile")
        If $dRow < $dRowFrom Or $dRow > $dRowMax Then ContinueLoop

        If $oCell.GetAttribute("t") = "s" Then    ; value = shared string-id
            $sTmp = Int(__xmlSingleText($oCell, $sPre & 'v'))
            If $sTmp > UBound($aStrings) Then Return SetError(5, $sTmp, False)
            $sValue = $aStrings[$sTmp]
        Else ; normal value
            $sValue = __xmlSingleText($oCell, $sPre & 'v')
            If StringRegExp($sValue, '(?i)\A(?|0x\d+|[-+]?(?>\d+)(?>\.\d+)?(?:e[-+]?\d+)?)\Z') Then $sValue = Number($sValue) ; if number then convert to number type
        EndIf
        $aRet[$oCell.GetAttribute("zeile") - $dRowFrom][$oCell.GetAttribute("spalte") - 1] = $sValue
    Next

    Return $aRet
EndFunc   ;==>__xlsx_readCells


; #FUNCTION# ======================================================================================
; Name ..........: __xlsx_readSharedStrings
; Description ...: import the shared string list from an xml file inside an xlsx-file
; Syntax ........: __xlsx_readSharedStrings(Const $sFile)
; Parameters ....: $sFile      - path-string of the shared-string-xml
; Return values .: Success - Return 2D-Array with the worksheet content
;                  Failure - Return False and set @error to:
;                       @error = 1 - cannot create XMLDOM-Object
;                              = 2 - cannot open worksheet file
;                              = 3 - cannot extract shared string objects out of the xml-structure
; Author ........: AspirinJunkie
; Last changed ..: 2020-07-27
; =================================================================================================
Func __xlsx_readSharedStrings(Const $sFile)
    Local $oXML = __xlsx_getXMLObject()

    If Not $oXML.load($sFile) Then Return SetError(2, 0, False)

    Local $sPre = $oXML.documentElement.prefix
    If $sPre <> "" Then $sPre &= ":"

    Local $oStrings = $oXML.selectNodes('/' & $sPre & 'sst/' & $sPre & 'si')
    If Not IsObj($oStrings) Then Return SetError(3, 0, False)

    Local $aRet[$oStrings.length], $i = 0

    For $oText In $oStrings
        $aRet[$i] = $oText.text
        $i += 1
    Next

    Return $aRet
EndFunc   ;==>__xlsx_readSharedStrings

; converts excel formatted cell coordinate to array [column, row]
Func __xlsx_CellstringToRowColumn($sID)
    Local $aSplit = StringRegExp($sID, '^([A-Z]+)(\d+)$', 1)
    If @error Then Return SetError(1, @error, False)

    Local $aChars = StringSplit($aSplit[0], '', 3)
    Local $j, $dV, $aRet[2] = [0, Int($aSplit[1])]

    For $i = 0 To UBound($aChars) - 1
        $j = UBound($aChars) - $i - 1
        $aRet[0] += (Asc($aChars[$i]) - 64) * (26 ^ $j)
    Next
    Return $aRet
EndFunc   ;==>__xlsx_CellstringToRowColumn


; #FUNCTION# ======================================================================================
; Name ..........: __xlsxExcel2Date
; Description ...: convert a excel date-value into an editable form (array of components or formatted string)
;                  with standard parameters the local date and time format is used
; Syntax ........: __xlsxExcel2Date($dExcelDate[, Const $sType = Default[, Const $iFlags = 0x01[, Const $sFormat = ""[, Const $iFlagsTime = 0[, Const $sFormatTime = ""]]]]])
; Parameters ....: $dExcelDate     - excel date value as number or string
;                  $sType          - Default: an array[6]: [year, month, day, hour, minute, second]
;                                    "date": string with formatted date
;                                    "time": string with formatted time
;                                    "datetime": string with formatted date and time
;                  $iFlags         - parameter $iflags of _WinAPI_GetDateFormat()
;                  $sFormat        - parameter $sFormat of _WinAPI_GetDateFormat()
;                  $iFlagsTime     - parameter $iflags of _WinAPI_GetTimeFormat()
;                  $sFormatTime    - parameter $sFormat of _WinAPI_GetTimeFormat()
; Return values .: Success - Return array or string (depending on the selected $sType)
;                  Failure - Return False and set @error to:
;                       @error = 1 - invalid value for $sType
; Author ........: AspirinJunkie
; Last changed ..: 2020-08-07
; =================================================================================================
Func __xlsxExcel2Date($dExcelDate, Const $sType = Default, Const $iFlags = 0x01, Const $sFormat = "", Const $iFlagsTime = 0, Const $sFormatTime = "")
    Switch $sType
        Case Default
            Local $aRet[6], $fTimeRaw = $dExcelDate - Int($dExcelDate)

            _DayValueToDate(2415018.5 + Int($dExcelDate), $aRet[0], $aRet[1], $aRet[2])

            ; process the time
            $aRet[3] = Floor($fTimeRaw * 24)
            $fTimeRaw -= $aRet[3] / 24 ; = Mod($fTimeRaw, 1/24)
            $aRet[4] = Floor($fTimeRaw * 1440)
            $fTimeRaw -= $aRet[4] / 1440
            $aRet[5] = Floor($fTimeRaw * 86400)

            Return $aRet
        Case "date"
            Local $y, $m, $d
            _DayValueToDate(2415018.5 + Int($dExcelDate), $y, $m, $d)
            Local $tDateTime = _Date_Time_EncodeSystemTime($m, $d, $y)
            Return _WinAPI_GetDateFormat(0x0400, $tDateTime, $iFlags, $sFormat)
        Case "time"
            Local $h, $min, $s, $fTimeRaw = $dExcelDate - Int($dExcelDate)
            ; process the time
            $h = Floor($fTimeRaw * 24)
            $fTimeRaw -= $h / 24 ; = Mod($fTimeRaw, 1/24)
            $min = Floor($fTimeRaw * 1440)
            $fTimeRaw -= $min / 1440
            $s = Floor($fTimeRaw * 86400)
            Return StringFormat("%02d:%02d:%02d", $h, $min, $s)
        Case "datetime"
            Local $y, $m, $d, $h, $min, $s, $fTimeRaw = $dExcelDate - Int($dExcelDate)
            ; process the time
            $h = Floor($fTimeRaw * 24)
            $fTimeRaw -= $h / 24 ; = Mod($fTimeRaw, 1/24)
            $min = Floor($fTimeRaw * 1440)
            $fTimeRaw -= $min / 1440
            $s = Floor($fTimeRaw * 86400)

            _DayValueToDate(2415018.5 + Int($dExcelDate), $y, $m, $d)
            Local $tDateTime = _Date_Time_EncodeSystemTime($m, $d, $y, $h, $min, $s)
            Return _WinAPI_GetDateFormat(0x0400, $tDateTime, $iFlags, $sFormat) & " " & _WinAPI_GetTimeFormat(0, $tDateTime, $iFlagsTime, $sFormatTime)
        Case Else
            Return SetError(1,0, False)
    EndSwitch
EndFunc   ;==>__xlsxExcel2Date

; function to share one single xmldom-object over the functions but without beeing a global variable
Func __xlsx_getXMLObject()
    Local Static $c = 0
    Local Static $oX = ObjCreate("Microsoft.XMLDOM")

    If $c = 0 Then
        With $oX
            .Async = True
            .resolveExternals = False
            .validateOnParse = False
            .setProperty("ForcedResync", False)
        EndWith
        $c = 1
    EndIf
    Return $oX
EndFunc   ;==>__xlsx_getXMLObject

#EndRegion xlsx specific helper functions



#Region general helper functions

Func __unzip($sInput, $sOutput, Const $sPattern = "")
    If Not FileExists($sInput) Then Return SetError(1, @error, False)
    $sOutput = StringRegExpReplace($sOutput, '(\\*)$', '')
    If Not StringInStr(FileGetAttrib($sOutput), 'D', 1) Then
        If Not DirCreate($sOutput) Then Return SetError(1, @error, False)
    EndIf

    If FileExists("7za.exe") Then
        Local $dRet = RunWait(StringFormat('7za.exe x "%s" -o"%s" %s -r -tzip -bd -bb0 -aoa', $sInput, $sOutput, $sPattern), "", @SW_HIDE)
        Return $dRet = 0 ? True : SetError(2, $dRet, False)

    Else ; much slower
        FileCopy($sInput, @TempDir & "\tmp.zip")

        Local Static $oShell = ObjCreate("Shell.Application")
        $oShell.Namespace($sOutput).CopyHere($oShell.Namespace(@TempDir & "\tmp.zip").Items, 4 + 16)
        FileDelete(@TempDir & "\tmp.zip")
    EndIf
    Return 1
EndFunc   ;==>__unzip

; returns value of a single xml-dom-node and handles errors
Func __xmlSingleText(ByRef $oXML, Const $sXPath)
    Local $oTmp = $oXML.selectSingleNode($sXPath)
    Return IsObj($oTmp) ? $oTmp.text : SetError(1, 0, "")
EndFunc   ;==>__xmlSingleText

#EndRegion general helper functions

 

Link to post
Share on other sites
  • 2 weeks later...

I fixed it with IMEX=1:

Global $sConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' & $sFilename & ';Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1"'

I tried _xlsx2Array but it didn't work, I forget why.

Link to post
Share on other sites

Well, now that that's working, another problem.  I have code that works on my work computer, but when I try to run it on my home PC, it won't run.  U have:

#include <_Array.au3>
Global Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ; adLockReadOnly
Global Const $iOptions = 512 ; adCmdTableDirect - Return all rows from the specified table

Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object

Global $sFilename = @ScriptDir & "\all-airport-data-abridged.xlsx" ; debug file
Global $sFilename = @ScriptDir & "\all-airport-data.xlsx"

Global $sConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' & $sFilename & ';Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1"'

$oConnection.Open($sConnectionString) ; Open the connection
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sSQL_Query = "Select * FROM [Airports$A:zz1]" ; Select all records and all fields of the selected range

$oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query

Global $array = $oRecordset.GetRows(1)

$oRecordset.Close ; Close the recordset
;$oRecordset = 0 ; Release the recordset object

$numfields = UBound($array, 2)

_ArrayDisplay($array)

But it fails to open the connection:

"......\sandbox\ado_test.au3" (13) : ==> The requested action with this object has failed.:
$oConnection.Open($sConnectionString)
$oConnection^ ERROR

Obviously it requires some component not installed on my computer (MS Office, perhaps?  I have that at work but use OpenOffice at home).  I need this to be portable, not relying on any other software on the user's computer.  Any ideas?

Link to post
Share on other sites
9 hours ago, Dana said:

But it fails to open the connection:

"......\sandbox\ado_test.au3" (13) : ==> The requested action with this object has failed.:
$oConnection.Open($sConnectionString)
$oConnection^ ERROR

Where is your COM ERROR HANDLER defined ?

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 for other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2022-03-07

Link to post
Share on other sites
3 hours ago, mLipok said:

Where is your COM ERROR HANDLER defined ?

 

12 hours ago, Danp2 said:

Check for errors following this line --

Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object

That line's OK; it fails on the next line and returns no error or return

$ret = $oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
if @error then ConsoleWrite("returned error " & @error & @crlf)
ConsoleWrite("returned " & $ret &@crlf)

$ret = $oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query
if @error then ConsoleWrite("returned error " & @error & @crlf)
ConsoleWrite("returned " & $ret &@crlf)

I'm guessing it's a provider issue, but I'm not really familiar with using COM objects.  On the work computer the Microsoft.ACE.OLEDB provider works but others such as the default MSDASQL don't work and error the same as the ACE does at home.

Link to post
Share on other sites
2 hours ago, Dana said:

That line's OK; it fails on the next line and returns no error or return

How do you know without checking for errors?
If ObjCreate fails variable $oConnection is undefined and hence $oConnection.Open crashes.

Thats why Danp2 suggests:

Quote

Check for errors following this line --

Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object

I would like to extend this suggestion: Check for errors after EACH ADO command.

Edited by water

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 post
Share on other sites

 

Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object
if @error Then ConsoleWrite("ObjCreate error " & @error & @crlf)
ConsoleWrite("ObjCreate returned " & $oConnection & @crlf)

gives no @error, but $oConnection appears empty; still, the subsequent $oConnection.Open line works (on the work computer with the ACE provider, but not with the MSDASQL provider).

I haven't had a chance to try it on the other computer yet.

Link to post
Share on other sites
2 hours ago, Danp2 said:

I believe that @mLipokhas pointed you into the right direction with his question about COM error handlers.

Thanks
But IMHO the best way is to use ADO.au3 UDF *

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 for other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2022-03-07

Link to post
Share on other sites

test this:

;~ https://www.autoitscript.com/forum/topic/209361-read-xlsx-with-ado-anomaly

#include <Array.au3>

#mLipok ; here you have COM ERROR HANDLER initialization
Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

Global Const $iCursorType = 0 ; adOpenForwardOnly
Global Const $iLockType = 1 ; adLockReadOnly
Global Const $iOptions = 512 ; adCmdTableDirect - Return all rows from the specified table

Global $oConnection = ObjCreate("ADODB.Connection") ; Create a connection object

Global $sFilename = @ScriptDir & "\all-airport-data-abridged.xlsx" ; debug file
;Global $sFilename = @ScriptDir & "\all-airport-data.xlsx"

;~ Global $sConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & $sFilename & ';Extended Properties="Excel 8.0;HDR=No"'
Global $sConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=' & $sFilename & ';Extended Properties="Excel 12.0 Xml;HDR=NO"'

$oConnection.Open($sConnectionString) ; Open the connection
Global $oRecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sSQL_Query = "Select * FROM [Airports$A:zz3]" ; Select all records and all fields of the selected range
$oRecordset.Open($sSQL_Query, $oConnection, $iCursorType, $iLockType, $iOptions) ; Issue the SQL query

Global $array = $oRecordset.GetRows(-1)
_ArrayDisplay($array)
exit


#mLipok ; here you have COM ERROR HANDLER function definition
; User's COM error function. Will be called if COM error occurs
Func _ErrFunc($oError)
        ; Do anything here.
        ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
                        @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
                        @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
                        @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
                        @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
                        @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
                        @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
                        @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
                        @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
                        @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

 

Signature beginning:
Please remember: "AutoIt"..... *  Wondering who uses AutoIt and what it can be used for ? * Forum Rules *
ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 for other useful stuff click the following button:

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST APIErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * IE in TaskSchedulerIE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related:How to get reference to PDF object embeded in IE * IE on Windows 11

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

I also encourage you to check awesome @trancexx code:  * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuffOnHungApp handlerAvoid "AutoIt Error" message box in unknown errors  * HTML editor

winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2022-03-07

Link to post
Share on other sites

Can you please tell us which version of AutoIt you run?

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 post
Share on other sites
Posted (edited)
On 1/6/2023 at 4:45 PM, mLipok said:

test this:

 

Tested with three different versions of the connection string.  The version that works on my work computer does this at home, and the ADO UDF gives the same error:


ObjCreate returned
Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\Users\d-m-h\Documents\Danaware\sandbox\all-airport-data-abridged.xlsx;Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1"
ado_test.au3 (26) : ==> COM Error intercepted !
     err.number is:         0x80020009
     err.windescription:    Exception occurred.

     err.description is:     Provider cannot be found. It may not be properly installed.
     err.source is:         ADODB.Connection
     err.helpfile is:     C:\windows\HELP\ADO270.CHM
     err.helpcontext is:     1240655
     err.lastdllerror is:     0
     err.scriptline is:     26
     err.retcode is:     0x800A0E7A

oConnection.Open error -2147352567
oConnection.Open returned

 

The other two versions do the same both at work and at home:

Provider=MSDASQL;FileDSN=C:\Users\d-m-h\Documents\Danaware\sandbox\all-airport-data-abridged.xlsx;Extended Properties="Excel 8.0 Xml;HDR=NO;IMEX=1"
ado_test.au3 (26) : ==> COM Error intercepted !
     err.number is:         0x80020009
     err.windescription:    Exception occurred.

     err.description is:     General error: Invalid file dsn ''
     err.source is:         Microsoft OLE DB Provider for ODBC Drivers
     err.helpfile is:
     err.helpcontext is:     0
     err.lastdllerror is:     0
     err.scriptline is:     26
     err.retcode is:     0x80004005

oConnection.Open error -2147352567
oConnection.Open returned

 

 


ObjCreate returned
Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Users\d-m-h\Documents\Danaware\sandbox\all-airport-data-abridged.xlsx;Extended Properties="Excel 8.0 Xml;HDR=NO;IMEX=1"
ado_test.au3 (26) : ==> COM Error intercepted !
     err.number is:         0x80020009
     err.windescription:    Exception occurred.

     err.description is:     Could not find installable ISAM.
     err.source is:         Microsoft JET Database Engine
     err.helpfile is:
     err.helpcontext is:     5003170
     err.lastdllerror is:     0
     err.scriptline is:     26
     err.retcode is:     0x80004005

oConnection.Open error -2147352567
oConnection.Open returned

 

 

On 1/6/2023 at 6:19 PM, water said:

Can you please tell us which version of AutoIt you run?

3.3.14.2

Edited by Dana
Link to post
Share on other sites

ODBC data driver?

Eg, At the command prompt type odbcad32 to run the ODBC Data Source Administrator

Provider=Microsoft.ACE.OLEDB.12.0

Where is this located? How does the script access the driver?

In order to use ADO you need

  • data file
  • SQL script
  • Data Driver
  • credentials

Also, this concerning your error

ISAM

This indicates that the XLSX is linked to an Access DB

Get the Access DB also?

Skysnake

Edited by Skysnake

Skysnake

Why is the snake in the sky?

Link to post
Share on other sites

I remember similar issue in computers with not whole Office installed (missing Access then not data driver installed)

Try to download and install this in your home computer:

AccessDatabaseEngine.exe

 

some references:

https://www.autoitscript.com/wiki/ADO_Tools

https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255

https://social.msdn.microsoft.com/Forums/en-US/600c7fb6-e7af-4a72-9323-c80c379703d2/the-microsoftaceoledb120-provider-is-not-registered-on-the-local-machine?forum=adodotnetdataproviders

 

Link to post
Share on other sites
On 1/11/2023 at 9:25 AM, Skysnake said:

ODBC data driver?

Eg, At the command prompt type odbcad32 to run the ODBC Data Source Administrator

At home:

image.png.e525467fa6b0a68ded41182234e77953.png

At work:

image.png.e1cf2edc03f8c2a28e6c6d9f05ab1061.png

 

3 hours ago, robertocm said:

Try to download and install this in your home computer:

AccessDatabaseEngine.exe

I looked at that and would have tried it if it was just my own system, but:

"Note: Before installing this download, you must first remove any previous versions of the Access Database Engine from your computer by using Add/Remove Programs in the Control Panel. "

I don't want to get into that on a user's system.

 

Or maybe I should just try a different approach using sqlite or something.

 

 

 

Link to post
Share on other sites
12 hours ago, Dana said:

At home:

... (64-bit)

Perhaps your script have to match with driver: if running in 32-bit mode, then you need 32-bit version of data driver (but your image is about 64-bit). Both script and data driver have to match.

12 hours ago, Dana said:

you must first remove any previous versions of the Access Database Engine

Seems you don't have the 32-bit data driver, not problem i think

 

Some ideas (sorry copy-paste):

Jet for Access, Excel and Txt on 64 bit systems

https://www.connectionstrings.com/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/

"Summary

It is possible to open Excel, Access and text files on 64 bit systems. You can either configure your app to go 32-bit or you can change your application to use the new 64 bit drivers."

---

https://stackoverflow.com/questions/13811179/where-how-can-i-download-and-install-the-microsoft-jet-oledb-4-0-for-windows-8

...

On modern Windows this driver isn't available by default anymore, but you can download as Microsoft Access Database Engine 2010 Redistributable on the MS site. If your app is 32 bits be sure to download and install the 32 bits variant because to my knowledge the 32 and 64 bit variant cannot coexist.

---

From:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

MTL2014, March 8, 2017

https://social.msdn.microsoft.com/Forums/en-US/home?forum=adodotnetdataproviders

"The ACE provider is not installed on Windows operating systems by default. You have to install it. The ACE provider is available in two forms: Microsoft Office Access database engine 2007 and  the Microsoft Access Database Engine 2010 Redistributable. The 2007 version is only available as a 32-bit component whereas the 2010 version offers both 32 and 64-bit options. You need to be careful which option you choose, because a wrong choice here is the most frequent cause for the error message."

...

"Do you install 32-bit component and restart your computer?"

---

"You can't use 32-bit SSMS with the 64-bit ACE OLEDB Provider. You might want to try installing the 2007 version which is 32-bit only:

https://www.microsoft.com/en-us/download/details.aspx?id=23734

Paul ~~~~ Microsoft MVP (Visual Basic)"

---

"I think you need to install the 32 bit version of the database engine. 

Indeed, VS works in 32 bit."

---

How do I setup the DSN for Microsoft access ODBC driver?

https://www.yellowfinbi.com/resources/forum/yfforum-how-do-i-setup-the-dsn-for-microsoft-access-odbc-driver-thread-103711?_ga=2.120226248.1943523695.1606556100-1586943709.1602924953

...

" It's likely the shortcut for setting ODBC data sources is pointing to the 32bit data sources instead of 64bit."

---

Accessing Excel files on a x64 machine

06/04/2010

https://docs.microsoft.com/en-us/archive/blogs/farukcelik/accessing-excel-files-on-a-x64-machine

---

How to import data from an Excel file to a SQL Server database

November 10, 2017 by Marko Zivkovic

https://www.sqlshack.com/import-data-excel-file-sql-server-database/

install Microsoft Access Database Engine 2016 Redistributable. The latest version of Microsoft Access Database Engine can open the earlier version of Excel, so make sure that you have the latest one.

The Microsoft Access Database Engine 2016 Redistributable comes with two versions:

- AccessDatabaseEngine.exe is 32-bit version

- AccessDatabaseEngine_X64.exe is 64-bit version

Edited by robertocm
Link to post
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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...