Read .XLSX with ADO anomaly

[Dana]

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

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

 

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

[dmob]

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

In the end I used this lil gem by AspirinJunkie:

expandcollapse popup

; #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

 

[Dana]

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.

[Dana]

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?

[Danp2]

Check for errors following this line --

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

[mLipok]

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 ?

[Dana]

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.

[water]

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 January 6, 2023 by water

[Dana]

 

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.

[Danp2]

@DanaThat's normal for an object to appear empty when output to console. You could do something like this instead --

ConsoleWrite("ObjCreate returned " & isObj($oConnection) & @crlf)

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

[mLipok]

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 *

[Dana]

Same results on home PC.

ConsoleWrite("ObjCreate returned " & isObj($oConnection) & @crlf)

returns 1

[Dana]

1 minute ago, mLipok said:

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

Thanks, I will look into it.

I tried _xlsx_2Array again, I could make it work with but it's way too slow for what I need.

[mLipok]

test this:

expandcollapse popup

;~ 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

 

[water]

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

[Dana]

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 January 9, 2023 by Dana

[Skysnake]

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 January 11, 2023 by Skysnake

[robertocm]

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

 

[Dana]

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:

At work:

 

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.

 

 

 

[robertocm]

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 January 13, 2023 by robertocm