Open any excel file as an array

[Jewtus]

I've had issues in the past dealing with excel so I decided to cut out the middle man and build a script that would take any file that opens in excel (csv, xml, xls, etc) and convert it into an array so I can handle the raw data in a cleaner way. I used czardas' CSV parser to do this and added a simple save in excel to save it as a csv to parse.

 

expandcollapse popup

Func _CreateCSV($fnImportFile)
    $oExcel = ObjCreate("Excel.Application")
    $oExcel.Visible=False
    $oBook= $oExcel.Workbooks.Open($fnImportFile)
    $oSheet=$oBook.ActiveSheet
    $fnMaster=@TempDir&"\"&Chr(Round(Random(0,24),0)+64)&Random(0,24)&Chr(Round(Random(0,24),0)+64)&Random(0,24)&Chr(Round(Random(0,24),0)+64)&".csv"
    ConsoleWrite($fnMaster&@CRLF)
    $oSheet.SaveAs($fnMaster, 6)
    $oBook.Close(False)
    $oExcel.Quit
    $aReturnArray=_CSVSplit(FileRead($fnMaster))
    FileDelete($fnMaster)
    If not @error Then
        Return $aReturnArray
    Else
        Return -1
    EndIf
EndFunc

; #FUNCTION# ====================================================================================================================
; Name...........: _CSVSplit
; Description ...: Converts a string in CSV format to a two dimensional array (see comments)
; Syntax.........: CSVSplit ( $aArray [, $sDelim ] )
; Parameters ....: $aArray  - The array to convert
;                  $sDelim  - Optional - Delimiter set to comma by default (see 2nd comment)
; Return values .: Success  - Returns a two dimensional array or a one dimensional array (see 1st comment)
;                  Failure  - Sets @error to:
;                 |@error = 1 - First parameter is not a valid string
;                 |@error = 2 - Second parameter is not a valid string
;                 |@error = 3 - Could not find suitable delimiter replacements
; Author ........: czardas
; Comments ......; Returns a one dimensional array if the input string does not contain the delimiter string
;                ; Some CSV formats use semicolon as a delimiter instead of a comma
;                ; Set the second parameter to @TAB To convert to TSV
; ===============================================================================================================================

Func _CSVSplit($string, $sDelim = ",") ; Parses csv string input and returns a one or two dimensional array
    If Not IsString($string) Or $string = "" Then Return SetError(1, 0, 0) ; Invalid string
    If Not IsString($sDelim) Or $sDelim = "" Then Return SetError(2, 0, 0) ; Invalid string

    $string = StringRegExpReplace($string, "[\r\n]+\z", "") ; [Line Added] Remove training breaks
    Local $iOverride = 63743, $asDelim[3] ; $asDelim => replacements for comma, new line and double quote
    For $i = 0 To 2
        $asDelim[$i] = __GetSubstitute($string, $iOverride) ; Choose a suitable substitution character
        If @error Then Return SetError(3, 0, 0) ; String contains too many unsuitable characters
    Next
    $iOverride = 0

    Local $aArray = StringRegExp($string, '\A[^"]+|("+[^"]+)|"+\z', 3) ; Split string using double quotes delim - largest match
    $string = ""

    Local $iBound = UBound($aArray)
    For $i = 0 To $iBound -1
        $iOverride += StringInStr($aArray[$i], '"', 0, -1) ; Increment by the number of adjacent double quotes per element
        If Mod ($iOverride +2, 2) = 0 Then ; Acts as an on/off switch
            $aArray[$i] = StringReplace($aArray[$i], $sDelim, $asDelim[0]) ; Replace comma delimeters
            $aArray[$i] = StringRegExpReplace($aArray[$i], "(\r\n)|[\r\n]", $asDelim[1]) ; Replace new line delimeters
        EndIf
        $aArray[$i] = StringReplace($aArray[$i], '""', $asDelim[2]) ; Replace double quote pairs
        $aArray[$i] = StringReplace($aArray[$i], '"', '') ; Delete enclosing double quotes - not paired
        $aArray[$i] = StringReplace($aArray[$i], $asDelim[2], '"') ; Reintroduce double quote pairs as single characters
        $string &= $aArray[$i] ; Rebuild the string, which includes two different delimiters
    Next
    $iOverride = 0

    $aArray = StringSplit($string, $asDelim[1], 2) ; Split to get rows
    $iBound = UBound($aArray)
    Local $aCSV[$iBound][2], $aTemp
    For $i = 0 To $iBound -1
        $aTemp = StringSplit($aArray[$i], $asDelim[0]) ; Split to get row items
        If Not @error Then
            If $aTemp[0] > $iOverride Then
                $iOverride = $aTemp[0]
                ReDim $aCSV[$iBound][$iOverride] ; Add columns to accomodate more items
            EndIf
        EndIf
        For $j = 1 To $aTemp[0]
            If StringLen($aTemp[$j]) Then
                If Not StringRegExp($aTemp[$j], '[^"]') Then ; Field only contains double quotes
                    $aTemp[$j] = StringTrimLeft($aTemp[$j], 1) ; Delete enclosing double quote single char
                EndIf
                $aCSV[$i][$j -1] = $aTemp[$j] ; Populate each row
            EndIf
        Next
    Next

    If $iOverride > 1 Then
        Return $aCSV ; Multiple Columns
    Else
        For $i = 0 To $iBound -1
            If StringLen($aArray[$i]) And (Not StringRegExp($aArray[$i], '[^"]')) Then ; Only contains double quotes
                $aArray[$i] = StringTrimLeft($aArray[$i], 1) ; Delete enclosing double quote single char
            EndIf
        Next
        Return $aArray ; Single column
    EndIf

EndFunc ;==> _CSVSplit

; #INTERNAL_USE_ONLY# ===========================================================================================================
; Name...........: __GetSubstitute
; Description ...: Searches for a character to be used for substitution, ie one not contained within the input string
; Syntax.........: __GetSubstitute($string, ByRef $iCountdown)
; Parameters ....: $string   - The string of characters to avoid
;                  $iCountdown - The first code point to begin checking
; Return values .: Success   - Returns a suitable substitution character not found within the first parameter
;                  Failure   - Sets @error to 1 => No substitution character available
; Author ........: czardas
; Comments ......; This function is connected to the function _CSVSplit and was not intended for general use
;                  $iCountdown is returned ByRef to avoid selecting the same character on subsequent calls to this function
;                  Initially $iCountown should be passed with a value = 63743
; ===============================================================================================================================

Func __GetSubstitute($string, ByRef $iCountdown)
    If $iCountdown < 57344 Then Return SetError(1, 0, "") ; Out of options
    Local $sTestChar
    For $i = $iCountdown To 57344 Step -1
        $sTestChar = ChrW($i)
        $iCountdown -= 1
        If Not StringInStr($string, $sTestChar) Then
            Return $sTestChar
        EndIf
    Next
    Return SetError(1, 0, "") ; Out of options
EndFunc ;==> __GetSubstitute

 

 

Edit:

The code above is pretty junk (my part at least) so I wanted to improve it... This will return an array of arrays based on the excel file

#include <Array.au3>

Func _GetExcelArrays($fnImportFile)
    $oExcel = ObjCreate("Excel.Application")
    $oExcel.Visible=False
    $oBook= $oExcel.Workbooks.Open($fnImportFile)
    $sheetCount=$oBook.Worksheets.Count
    Local $aReturnArray[$sheetCount]
    For $x=1 to $sheetCount
        $oSheet=$oBook.Worksheets($x)
        $oSheet.Activate
        $fnMaster=@TempDir&"\"&Chr(Round(Random(0,24),0)+64)&Random(0,24)&Chr(Round(Random(0,24),0)+64)&Random(0,24)&Chr(Round(Random(0,24),0)+64)&".csv"
        $oSheet.SaveAs($fnMaster, 6)
        $aReturnArray[$x-1]=_CSVSplit(FileRead($fnMaster))
        FileDelete($fnMaster)
    Next
    $oBook.Close(False)
    $oExcel.Quit
    Return $aReturnArray
EndFunc

 

Edited October 11, 2016 by Jewtus

[antonioj84]

hi,  i am looking for that same code ... dump excel into an array however when i ran your code nothing happened, can you provide more details.  

[water]

Why not use _Excel_RangeRead?

[Jewtus]

Toss a message box in before the file delete and see if the file name that gets dumped to the console exists... You can also change the visible to true to see if its a problem with opening the excel file or something. That will isolate where the issue is. However, water is correct... If you know the range, rangeread is so much easier... I didn't always have the range... or the worksheet name...

Edited October 11, 2016 by Jewtus

[mLipok]

Maybe ADO.au3 UDF ?
 

 

Func _Example_MSExcel()

    Local $sFileFullPath = Default ; Here put FileFullPath to your Excel File or use Default to open FileOpenDialog
    Local $sProvider = Default
    Local $sExtProperties = Default
    Local $HDR = Default
    Local $IMEX = Default

    Local $sConnectionString = _ADO_ConnectionString_Excel($sFileFullPath, $sProvider, $sExtProperties, $HDR, $IMEX)

    _Example_1_RecordsetToConsole($sConnectionString, "select * from [Sheet1$]")
    _Example_2_RecordsetDisplay($sConnectionString, "select * from [Sheet1$]")
    _Example_3_ConnectionProperties($sConnectionString)

EndFunc   ;==>_Example_MSExcel

 

[antonioj84]

thanks, water it did the job, simple and elegant. range_read does the exact same 

#include <Excel.au3>
#include <File.au3>
#include <Array.au3>



Local $oExcel =_Excel_Open()


$datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\test.xls")
$datawb.worksheets("info").select
$LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count
$mydata = _Excel_RangeRead($datawb, Default, "A1:AI" & $LastRow)
If IsArray($mydata) Then _ArrayDisplay($mydata)


_Excel_BookClose($datawb)

 

[water]

Are there any columns beyond "AI" which you do not want to read?

[antonioj84]

hey water, I selected the range where the data reside that I wanted, the other data beyond I do not need. are you suggesting something ?

[water]

If you want to "ignore" some columns then your solution is fine. Else you simply could set the range to "Default" and the function would return all used cells.

[antonioj84]

can i have someone assistance here, ?  all i am trying to do is to read the input (numerical between 3 or 4 digits )  then match in column G or  "location name" then if found select the number to the left column F or "location code"

 

example if number read match  at " location name" or  G = 5321, therefore  my location code or F   should be 344742463 and if  my script was correct     my $ssstore should contain 344742463

expandcollapse popup

#include <Excel.au3>
                                 #include <File.au3>
                                 #include <Array.au3>

                                 $Dealer = IniRead(@ScriptDir & "\config.ini", "data", "Dealer", "")
                                 $checknum = GUICtrlRead($Input3)
                                

                                Local $oExcel =_Excel_Open()
                                $datawb1 = _Excel_BookOpen($oExcel, $Dealer, True)
                                $datawb1.worksheets("Locations").select
                                $LastRow = $datawb1.ActiveSheet.UsedRange.Rows.Count
                                $mydealer = _Excel_RangeRead($datawb1, Default, "j1:H" & $LastRow)
                                _Excel_BookClose($datawb1, False)


                              ;_ArrayDisplay($mydealer, $checknum)
                
                    for $i = 1 to UBound($mydata)-1
                    $check = False
                    if $mydata[$i][0] = $checknum Then   ; look for value

                        $ssstore = ""
                        ;MsgBox(0, "", UBound($mydealer)-1)
                        for $j = 1 to UBound($mydealer)-1
                            $tmp = StringSplit($mydealer[$j][1], " ")
                            if $j = 99 then
                            ;   _ArrayDisplay($tmp)
                            ;    MsgBox(0, Int($tmp[$ji]) , Int($checknum) )
                            EndIf

                            for $ji = 1 to UBound($tmp)-1
                                if Int($tmp[$ji]) = Int($checknum) Then
                                    $ssstore = $mydealer[$j][0]     ;found value
                                     
                                     MsgBox(0, "", $ssstore)                                                                                                                                              $j = UBound($mydealer)+1
                                    $ji = UBound($tmp)+1
                                EndIf
                            Next
                        Next

 

[water]

Something like this should do the trick.

For $iCounter = 1 To UBound($mydealer, 1) - 1
    If StringInStr(" " & CheckNum & " ", " " & $mydealer[$iCounter][1] & " ") > 0 Then
        MsgBox("0, " Found", " Result = " & $mydealer[$iCounter][1])
        ExitLoop
    EndIf
Next

This adds spaces at start/end of the array element and the search string so that you only find blank delimited numbers.

[antonioj84]

hey water, it did the trick. you got me some leisure time

[water]

[antonioj84]

I need some help I just can not figure out I am for the exact same issue that i thought that was fixed. I am getting this error

any ideas what am I doing wrong or this just a generic error when excel does not open.  some error checking tips is welcome. please improve in my code thanks

this is the error. 

Local $bVisible

            ElseIf $checknum <> "" Then


                                $oExcel = _Excel_Open(False)
                                $datawb = _Excel_BookOpen($oExcel, $STORENUM, True, False)
                                $datawb.worksheets("Black").Select
                                 $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count
                                 $mydata = _Excel_RangeRead($datawb, Default, "A1:AI" & $LastRow)
                                 _Excel_BookClose($datawb,$bVisible =True)

C:\Program Files (x86)\AutoIt3\Include\Excel.au3" (227) : ==> Variable must be of type "Object".:
$oExcel.Windows($oWorkbook.Name).Visible = $bVisible
$oExcel.Windows($oWorkbook.Name)^ ERROR

Edited October 19, 2016 by antonioj84
made an awfull mistake

[water]

Do you mean the problem has been solved by you?

[antonioj84]

yes it was solved I did 2 things

I added   #RequireAdmin and a sleep( 2000) )_Excel_open

$oExcel = _Excel_Open(False)
                Sleep(2000)  ; I added this sleep pause
                $datawb = _Excel_BookOpen($oExcel, $STORENUM)
                $datawb.worksheets("Black").Select
                $LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count
                $mydata = _Excel_RangeRead($datawb, Default, "A1:AI" & $LastRow)
                _Excel_BookClose($datawb,$bVisible =True)

 

[antonioj84]

can anyone else help.

I am using widows 7, with the latest autoit,  and excel 2013 quite often excel will be visible on the screen  is there is a way I can hide it for good

Local $oExcel =_Excel_Open(False)
$datawb = _Excel_BookOpen($oExcel,$Retail &"\"& $aFileList[$i], 0)
 ;$datawb = _Excel_BookOpen($oExcel,$sFolderPathSource & $aFileList[$i], 0)
 $datawb.worksheets("info").Select
$LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count
$mydata = _Excel_RangeRead($datawb, Default, "B1:K" & $LastRow)
_Excel_BookClose($datawb,True)

 

Edited November 9, 2016 by antonioj84

[water]

Try:

Local $oExcel =_Excel_Open(False)
$datawb = _Excel_BookOpen($oExcel,$Retail &"\"& $aFileList[$i], 0)
$datawb.worksheets("info").Activate
$LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count
$mydata = _Excel_RangeRead($datawb, Default, "B1:K" & $LastRow)
_Excel_BookClose($datawb,True)

BTW: "Quite often" means always, one out of ten ...? Can you reproduce?

[antonioj84]

here's the picture at the bottom excel always  come up now even I am running in the hidden mode

Edited November 12, 2016 by antonioj84

[antonioj84]

that snippet of code from water,  have fixed the crashing and the visibility issue opening excel. add that code and  use the _Excel_BookOpenEX instead of _Excel_BookOpen

Func _Excel_BookOpenEX($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default, $bUpdateLinks = Default)
    ; Error handler, automatic cleanup at end of function
    Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")
    #forceref $oError
    If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0)
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $bReadOnly = Default Then $bReadOnly = False
    If $bVisible = Default Then $bVisible = True
    Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, $bUpdateLinks, $bReadOnly, Default, $sPassword, $sWritePassword)
    If @error Then Return SetError(3, @error, 0)
    Local $oWindow = $oExcel.Windows($oWorkbook.Name) ; <== Modified
    If IsObj($oWindow) Then $oWindow.Visible = $bVisible ; <== Modified
    ; If a read-write workbook was opened read-only then set @extended = 1
    If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(0, 1, $oWorkbook)
    Return $oWorkbook
EndFunc   ;==>_Excel_BookOpen