Sign in to follow this  
Followers 0
munsen70

Help with csv file to multi-dimensional array

16 posts in this topic

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.

;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

Share this post


Link to post
Share on other sites



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.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

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.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by czardas

Share this post


Link to post
Share on other sites

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


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

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Here my function:

 

#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 by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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 by czardas

Share this post


Link to post
Share on other sites

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.

;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

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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 by czardas

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Thank you very much again.

Share this post


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
Sign in to follow this  
Followers 0