Help with csv file to multi-dimensional array

[munsen70]

Hello, I am working on a project for a schedule checking application.  The original scheduling program stores all of it's data in a SQL CE database.  I am able to get the info and put it into a .csv file successfully.  What I need to do next is get the data to display in a ComboBox.  I've been all over the forums and it looks like the best way to do so is to put the data in an array first.  I have no experience with array's, but I found the code below submitted by a user named Chris on stackoverflow.com and am attempting to use it.  In the csv that is created, the number of lines can vary so I was looking for something that would allow me to read it without having to set the number of lines.  The number of column's is 4 and I do not send the headers to the csv file.  I am looking to populate the combo box with the items from each line separately.  When I run my script, I get the error below at the second part of the code that reads the file to an array.  

Subscript used with non-Array variable.:
$array[$inwhich][$i] = $row_array[$i]
$array^ ERROR

I have tried using the @error and the Graphical AutoIt Debugger by Stumpii which show me the same error, but I don't know where to start to correct it.

expandcollapse popup

;I call the functions in my main while loop with the following:

        Case $btnUpdate
            _DBtoCSV()
            _ParseCSV($csvFile, "'", "Not Working", False)
            _SetComboData()


;====Code from Chris on stackoverflow.com Read CSV file to array part 1======================================================

Func _ParseCSV($f, $Dchar, $error, $skip)


    Local $line = ""

    $i = 0
    $file = FileOpen($f, 0)
    If $file = -1 Then
        MsgBox(0, "Error", $error)
        Return False
    EndIf

    ;skip 1st line
    If $skip Then $line = FileReadLine($file)

    While 1
        $i = $i + 1
        Local $line = FileReadLine($file)
        If @error = -1 Then ExitLoop
        $row_array = StringSplit($line, $Dchar)
        If $i == 1 Then $row_size = UBound($row_array)
        If $row_size <> UBound($row_array) Then MsgBox(0, "Error", "Row: " & $i & " has different size ")
        $row_size = UBound($row_array)
        $array = _arrayAdd_2d($array, $i, $row_array, $row_size)

    WEnd
    FileClose($file)
    $array[0][0] = $i - 1 ;stores number of lines
    $array[0][1] = $row_size - 1 ; stores number of data in a row (data corresponding to index 0 is the number of data in a row actually that's way the -1)
    Return $array

EndFunc   ;==>_ParseCSV

;=============Code from Chris on stackoverflow.com Read CSV file to array part 2=========================================

Func _arrayAdd_2d($array, $inwhich, $row_array, $row_size)
    For $i = 1 To $row_size - 1 Step 1
        $array[$inwhich][$i] = $row_array[$i]
    Next
    Return $array

EndFunc   ;==>_arrayAdd_2d


;=======================Set ComboBox data function=========================================================================

Func _SetComboData()
    GUICtrlSetData($cboOrderInfoDD, "|" & _ArrayToString($array))

EndFunc   ;==>_SetComboData

[BrewManNH]

You need to define the incoming array you send to _arrayadd_2d AS an array before you can add to it. Your code sends the function a simple variable and not an array. That function requires the first parameter to be a 2D array.

[czardas]

You could try using _CSVSplit(). The code was written a while ago however it should still be working with the latest AutoIt release.

'?do=embed' frameborder='0' data-embedContent>>

You need to read the CSV file using FileRead(). The start of the example in ?p=1132253'>post No 8 (on the same page) shows you how to do this.

[BrewManNH]

If it's a simple CSV file, nothing fancy such as commas inside quotes that shouldn't be split, the _fileReadToArray function in the current release will do a character delimited string split to create a 2D array.

[munsen70]

Thank you for the replies.  BrewMan, it is a simple CSV file.  How do I specify the delimiter in the _fileReadToArray?  It does read the CSV to a file,  but keeps the commas and returns a full string with the commas included.  czardas, I will take a look at that as well.  It will take me a bit to test it with my script though just because I'm slow at this stuff.

[czardas]

By default my script does not wrap fields in double quotes and behaves as a standard split to 2D Array (or 1D array if there are no commas in the string). Only when fields are found to contain commas, breaks or double quotes does the more complicated syntax kick in when writing from array to CSV. _CSVSplit() will also parse corrupt formats to some degree - with inconsistent numbers of entries in each row - and fix these errors when converting back to CSV.

Edited June 6, 2014 by czardas

[Melba23]

munsen70,

 

How do I specify the delimiter in the _fileReadToArray?

You need the latest AutoIt release - then you will see the new version of the function:

_FileReadToArray ( $sFilePath, ByRef $aArray [, $iFlags = $FRTA_COUNT [, $sDelimiter = ""]] )

and your question should be answered.

M23

[munsen70]

I will definitely update and try the _FileReadToArray function.  czardas, I am trying to use your script as it is giving me a really good learning opportunity.  As I said I know nothing about arrays.  When I first tried your script I got a return of error 5 for the sort column number being out of range.  I think that was because I didn't specify anything in the paramaters.  I've been reading up to understand arrays and want to make sure I have the parameters correct.  With using the ComboBox, before I read the database to a csv file I add another column with the '|' character to separate the lines when they go into the ComboBox, so I now have 5 columns.  If I'm understanding how the parameters are set correctly, when I set this - $aParam[5][10] = [[1,Default,False,1,False]].  I am saying that the array will have 5 columns, 10 rows, $iCol is set to 1, $sDelim is a comma, $bHeaders is False, $iSortCol is set to 1 and $bAlphaSort is set to False so it should sort taking number sequence into account.  Is that correct?

With the parameters set like this, I am getting the error "3".  I have added ConsoleWrites to try and figure out where the error is occuring, but I can't seem to figure it out.  Here is the output from the ConsoleWrites I put in to see where it stops.  It looks to me like it goes through all of the parts of the script fine and gets caught after it goes through the last 3 steps 4 times

Step1
Step2
Step3
Step4
Step5
Step6
Step7
Step5
Step6
Step7
Step5
Step6
Step7
Step5
Step6
Step7
Error = 3

expandcollapse popup

Local $sFilePath = $csvFile ; Change this to your own csv file (in the same directory as the script)

Local $hFile = FileOpen($sFilePath)
If $hFile = -1 Then
    MsgBox(0, "", "Unable to open file 1")
    Exit
EndIf

ConsoleWrite("Step1" & @CRLF)

Local $sCSV = FileRead($hFile)
If @error Then
    MsgBox(0, "", "Unable to read file")
    FileClose($hFile)
    Exit
EndIf
FileClose($hFile)

ConsoleWrite("Step2" & @CRLF)

Local $aCSV = _CSVSplit($sCSV) ; Create the main array
If @error Then
    ConsoleWrite("Error = " & @error & @LF)
    Exit
EndIf

ConsoleWrite("Step3" & @CRLF)

; Examples 0f _ArrayToSubItemCSV()
Local $aParam[5][10] = [[1,Default,False,1,False]] ; Test parameters
;[1,Default,True,2,Default], _ ; Test 1 = Numeric sort - with headers
;[1,Default,True,2,True], _ ; Test 2 = Alpha Sort - with headers
;[1,Default,Default,Default,Default], _ ; Test 3 = Any order - no headers
;[1,Default,True,1,Default]] ; Test 4 = Original sequence - with headers

Local $aSubItemCSV, $sFolderName, $sNewPath, $iSuccess

For $i = 0 To 3
    ; Create an array of csv strings
    $aSubItemCSV = _ArrayToSubItemCSV($aCSV, $aParam[$i][0], $aParam[$i][1], $aParam[$i][2], $aParam[$i][3], $aParam[$i][4])
    If @error Then
        ConsoleWrite("Error = " & @error & @LF)
        Exit
    EndIf

ConsoleWrite("Step4" & @CRLF)

    ; Now let's write each csv to file
    $sFolderName = StringTrimRight(StringReplace($sFilePath, $csvLocation, $csvLocation), 4) & " " & $i +1

    For $j = 0 To UBound($aSubItemCSV) -1
        If StringRegExp($aSubItemCSV[$j][0], '[\/\?<>\\\:\|\*"]') Then
            MsgBox(0, "Invalid file name", "You can not use the following characters in a file name" & @CRLF & '/ ? < > \ : | * "')
            Exit
        EndIf

ConsoleWrite("Step5" & @CRLF)

        $sNewPath = $sFolderName & $aSubItemCSV[$j][0] & ".csv"
        $hFile = FileOpen($sNewPath, BitOr(8,1))
        If $hFile = -1 Then
            MsgBox(0, "", "Unable to open file 2")
            Exit
        EndIf

ConsoleWrite("Step6" & @CRLF)

        $iSuccess = FileWrite($hFile, $aSubItemCSV[$j][1])
        FileClose($hFile)
        If $iSuccess = 0 Then
            MsgBox(0, "", "Unable to write to file")
            Exit
        EndIf

ConsoleWrite("Step7" & @CRLF)

    Next
Next

[mikell]

An other alternative is this timeless _CSV2Array func

http://www.thorsten-willert.de/Themen/AutoIt/_CSV2Array.au3?a

But the _FileReadToArray in the last release is the best way though

[UEZ]

Here my function:

 

expandcollapse popup

#include <Array.au3>

Global $sTextCSV = '10,20,"30,40",50,"60,70,80,90"' & @CRLF & _
                    '30,1,2,3,4,5,6,7,8' & @CRLF & _
                    '40,1,2,3,4,5,6,7,8'

$2DArray = StringSplitW($sTextCSV, ",")
_ArrayDisplay($2DArray)


; #FUNCTION# ========================================================================================================================================
; Name .................:   StringSplitW()
; Description ..........:   Splits  a string into columns instead of rows as it is done by SplitString(), like a csv file to a 2d array ;-)
; Syntax ...............:   StringSplitW($sString, $sDelimiter, $iWidthLen)
; Parameters ...........:   $sString - string to split
;                           $sDelimiter - [optional] the delimter how to split the string
;                           $iWidthLen - [optional] length of the row (amount of columns - default is 256)
; Return values .......:    Success - 2d array
;                           Error 1 - either $sString or $delimter is not set
;                           Error 2 - array width exceeded
;                           Error 3 - error splitting string
;
; Version .............:    v0.96 build 2015-01-20 beta
; Author ..............:    UEZ
; Modified ............:
; Remarks .............:    RegEx take from http://stackoverflow.com/questions/4476812/regular-expressions-how-to-replace-a-character-within-quotes
; Related .............:    StringSplit, StringReplace, StringRegExpReplace, StringLen, StringStripCR
; ===================================================================================================================================================
Func StringSplitW($sString, $sDelimiter = ";", $sQuotationMark = '"', $sDummy = "¦", $iWidthLen = 256)
    If $sString = "" Or $sDelimiter = "" Then Return SetError(1, 0, 0)
    Local $chk, $iWidth, $i, $j, $k, $iLen, $iMax = 1, $iMaxWidth
    Local $aPos[1], $l = 0
    Local $aSplit =  StringSplit(StringStripCR($sString), @LF)
    If @error Then Return SetError(3, 0, 0)
    Local $aVertical[$aSplit[0]][$iWidthLen], $iDelimiterLen = StringLen($sDelimiter) - 1, $sLine
    For $k = 1 To $aSplit[0]
        $iLen = StringLen($aSplit[$k])
        If $iLen > 1 Then
            $sLine = StringRegExpReplace($aSplit[$k], '(?m)\' & $sDelimiter & '(?=[^' & $sQuotationMark & ']*' & $sQuotationMark & '(?:[^' & $sQuotationMark & '\r\n]*' & $sQuotationMark & '[^' & $sQuotationMark & ']*' & $sQuotationMark & ')*[^' & $sQuotationMark & '\r\n]*$)', $sDummy)
            $chk = StringReplace($sLine, $sDelimiter, $sDelimiter)
            $iWidth = @extended
            If $iWidth > $iWidthLen Then Return SetError(2, 0, 0)
            If $iWidth >= $iMax Then $iMax = $iWidth + 1
            Switch $iWidth
                Case 0
                    $aVertical[$l][0] = $sLine
                Case Else
                    Dim $aPos[$iWidth * 2 + 2]
                    $j = 1
                    $aPos[0] = 1
                    For $i = 0 To $iWidth - 1
                        $aPos[$j] = StringInStr($sLine, $sDelimiter, 0, $i + 1) - 1
                        $aPos[$j + 1] = $aPos[$j] + 2 + $iDelimiterLen
                        $j += 2
                    Next
                    $aPos[UBound($aPos) - 1] = StringLen($sLine)
                    $j = 0
                    For $i = 0 To UBound($aPos) - 1 Step 2
                        $aVertical[$l][$j] = StringMid(StringReplace($sLine, $sDummy, $sDelimiter), $aPos[$i], $aPos[$i + 1] - $aPos[$i] + 1)
                        $j += 1
                    Next
                EndSwitch
                $l += 1
        EndIf
    Next
    ReDim $aVertical[$l][$iMax]
    Return $aVertical
EndFunc

Br,

UEZ

Edited January 19, 2015 by UEZ

[czardas]

czardas, I am trying to use your script as it is giving me a really good learning opportunity.  As I said I know nothing about arrays.  When I first tried your script I got a return of error 5 for the sort column number being out of range.  I think that was because I didn't specify anything in the paramaters.

 

I see you tried to run the whole script, instead of extracting the first part which is all you needed. I apologize because it's really my fault for not making a simpler example. You had already created the array using _CSVSplit(). The rest of the code is an example to test an entirely different function.

Try this:

include <Array.au3>
include <CSVSplit.au3>

Local $sFilePath = @ScriptDir & "\test.csv" ; Path to the CSV file. <<<<< CHANGE AS NECESSARY

Local $hFile = FileOpen($sFilePath)
If $hFile = -1 Then
    MsgBox(0, "", "Unable to open file")
    Exit
EndIf

Local $sCSV = FileRead($hFile)
If @error Then
    MsgBox(0, "", "Unable to read file")
    FileClose($hFile)
    Exit
EndIf
FileClose($hFile)

Local $aCSV = _CSVSplit($sCSV) ; Create the main array. <<<<< THIS FUNCTION
If @error Then
    ConsoleWrite("Error = " & @error & @LF)
    Exit
EndIf

_ArrayDisplay($aCSV) ; Show the array.

Edited June 7, 2014 by czardas

[munsen70]

Oh.  Sorry about that.  Works well now that it's correct.  Both solutions work to create the array just fine.  Thank you all for your help.  After modifying the database to add the '|' character as a separate column, I can get the data to a combobox on separate lines, but I can only get it to work if I write it to a text file first, which then puts all of the commas, etc back in lumping everything for each line together.  Is this normal or is there any way I can load the data the way it appears in the ArrayDisplay?  I've been reading around and found a few examples, but each one I try gives the same result of the combobox being blank.

expandcollapse popup

;Here is where I included the CVSSplit which I write to a txt file at the end

;========================Function to use CSVSplit by czardas on AutoIt forums==============================================

Func _callCSVSplit()
    Local $sFilePath = $csvFile ; Change this to your own csv file (in the same directory as the script)
    Local $lines

    Local $hFile = FileOpen($sFilePath)
    If $hFile = -1 Then
        MsgBox(0, "", "Unable to open file")
        Exit
    EndIf

    Local $sCSV = FileRead($hFile)
    If @error Then
        MsgBox(0, "", "Unable to read file")
        FileClose($hFile)
        Exit
    EndIf
    FileClose($hFile)

    $aCSV = _CSVSplit($sCSV) ; Create the main array. <<<<< THIS FUNCTION
    If @error Then
        ConsoleWrite("Error = " & @error & @LF)
        Exit
    EndIf

    _ArrayDisplay($aCSV) ; Show the array.

    _FileWriteFromArray($arrayoutput, $aCSV, 0, Default, ",")

    

EndFunc   ;==>_callCSVSplit


;And here is the function where I populate the combobox with the data from the text file.
;=======================Set ComboBox data function=========================================================================

Func _SetComboData()

    ;Local $orderlist = FileRead($arrayoutput)
    ;GUICtrlSetData($cboOrderInfoDD, Fileread($orderlist))
    GUICtrlSetData($cboOrderInfoDD, FileRead($arrayoutput));testing using csvsplit

EndFunc   ;==>_SetComboData

[czardas]

You can create a ListView control to view the two dimensional array. You don't need to write the array to file again. Look at examples in the help file.

To get data from the array you will need to loop through both dimensions of the array using two For loops - one inside the other. Something like this:

For $i = 0 To Ubound($aCSV, 1) -1 ; Loop through all rows
    For $j = 0 To Ubound($aCSV, 2) -1 ; Loop through all columns
        $data = $aCSV[$i][$j] ; Read each element of the array
        ConsoleWrite($data & @LF) ; Write to SciTE console as a test
    Next
Next

Edited June 10, 2014 by czardas

[munsen70]

I worked with that yesterday and was able to get the listview to populate with data, but I think to get that to fit my needs is going to take quite a bit more work than the time I have.  Even with the commas displaying, the tool works the way it needs.  You can select the item in the drop down which actually generates and sends an email with that information, but I was stuck with getting the info into the combobox and you have definitely resolved the issue I was having.  Thank you all very much for all of the help.

[czardas]

Good luck with it.

[munsen70]

Thank you very much again.