_FileReadToArray will read a CSV test file but not the CSV file I want to read

[thegoodguy]

Hi,

I am trying to read a CSV file to array using _FileReadToArray. I kept getting an error when I tried to view the array using _ArrayDisplay. (no array variable passed to function).  The CSV file is saved from Excel. I created a test CSV with "simple" data and the same number of elements. This file is read to array and displayed with no problems. I concluded that there is something the the naughty CSV that _FileReadToArray doesn't like. I have tried replacing all of the  "\"s ,";", ";" and ".", but no joy. Can one of you knowledgeable and kind folk please enlighten me?

I have attached the two CSV files.

Many thanks in advance
 

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

Local $aRetArray[40][7], $sFilePath = "E:\++ Work\work\Dad\## Afterlife\Shop\Silver charms\Done\listing pages\items1.csv"
_FileReadToArray($sFilePath, $aRetArray, Default, ",")
_ArrayDisplay($aRetArray, "2D array - count", Default, 8)

 

items1.csv items2.csv

[ioa747]

the items 1.csv file has some issues like the blank line

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

Local $aRetArray, $sFilePath = @ScriptDir & "\items2.csv"

_FileReadToArray($sFilePath, $aRetArray, Default, ",")
_ArrayDisplay($aRetArray, "$aRetArray")

 

Edit:
the file items 1.csv has some issues ...  nor does it have a fixed number of columns

#include <Array.au3>

Local Const $sTempCSVFile = @ScriptDir & "\items1.csv"
Local $aDataArray = FileReadToArray($sTempCSVFile)

;~ _ArrayDisplay($aDataArray, "Autorunsc Data Loaded into Array")

For $i = 0 To UBound($aDataArray) - 1
    Local $aPart = StringSplit($aDataArray[$i], ",")
    ConsoleWrite($i & ") $aParts=" & $aPart[0] & @CRLF)
Next

 

Edit:
possible solution

#include <Array.au3>

Local Const $sTempCSVFile = @ScriptDir & "\items1.csv"
Local $aDataArray = FileReadToArray($sTempCSVFile)
Local $aNewArray[UBound($aDataArray)][10]

For $i = 0 To UBound($aDataArray) - 1
    Local $aPart = StringSplit($aDataArray[$i], ",")
    For $j = 1 To $aPart[0]
        $aNewArray[$i][$j - 1] = $aPart[$j]
    Next
Next

_ArrayDisplay($aNewArray, "$aNewArray")

 

Edited 13 hours ago by ioa747

[Nine]

Take also a look at _FileReadToArray using the $FRTA_INTARRAYS flag...

[pixelsearch]

 You can also try the function _StringSplit2D_EX() based on @AspirinJunkie function (link in the script below) . It splits nicely "items1.csv" which has a non-fixed number of columns.

expandcollapse popup

#include <Array.au3>
#include <MsgBoxConstants.au3>
#include <StringConstants.au3>

Local $sFilePath = @ScriptDir & "\items1.csv"

Local $sFileRead = FileRead($sFilePath)
If @error Then Exit MsgBox($MB_TOPMOST, "FileRead", "@error = " & @error)

Local $aRetArray = _StringSplit2D_EX($sFileRead, ",", @CRLF, True) ; True to allow a variable number of columns.
If @error Then Exit MsgBox($MB_TOPMOST, "_StringSplit2D_EX", _
    "@error = " & @error & @CRLF & _
    "All rows don't have the same number of fields (line " & (@extended + 1) & ")")

_ArrayDisplay($aRetArray, "2D array from delimited file")

;==============================================
Func _StringSplit2D_EX(ByRef $sString, $sDelim_Col = "|", $sDelim_Row = @CRLF, $bExpand = False, $iAdd_EmptyCol = 0)
    ; based on AspirinJunkie's function _StringSplit2D found at https://autoit.de/thread/85380-1d-array-in-2d-array-splitten/
    ; Thanks Nine for suggesting the 4th parameter $bExpand, to allow or not the same number of fields per row (as in _FileReadToArray)

    Local $a_FirstDim = StringSplit($sString, $sDelim_Row, $STR_ENTIRESPLIT + $STR_NOCOUNT)
    Local $iKeep_NbCol = Ubound(StringSplit($a_FirstDim[0], $sDelim_Col, $STR_ENTIRESPLIT + $STR_NOCOUNT)) ; keep nb cols row 0
    Local $a_Out[UBound($a_FirstDim)][1 + $iAdd_EmptyCol], $a_Line, $i_2DMax = 1

    For $i = 0 To UBound($a_FirstDim) - 1
        $a_Line = StringSplit($a_FirstDim[$i], $sDelim_Col, $STR_ENTIRESPLIT + $STR_NOCOUNT)
        If (Not $bExpand) And (Ubound($a_Line) <> $iKeep_NbCol) Then Return SetError(3, $i, 0) ; same error # as _FileReadToArray
        If UBound($a_Line) > $i_2DMax Then ; when $bExpand = False, this test will be True maximum 1 time, never more.
            $i_2DMax = UBound($a_Line)
            ReDim $a_Out[UBound($a_Out)][$i_2DMax + $iAdd_EmptyCol]
        EndIf
        For $j = 0 To UBound($a_Line) - 1
            $a_Out[$i][$j] = $a_Line[$j]
        Next
    Next

    Return $a_Out
EndFunc   ;==>_StringSplit2D_EX

Edit: @ioa747 & @Musashi thanks for your appreciation.
I already used several times this interesting function _StringSplit2D_EX . In case you'll use it one day, I just made a minor change in it, based on the "@extended idea" described in my other post later in this thread.

For example, let's run the amended script above, with :
1) items1.csv is the one from OP's 1st post (the bad csv with a non-fixed number of columns)
2) Now this line :

Local $aRetArray = _StringSplit2D_EX($sFileRead, ",", @CRLF, True) ; True to expand, it allows a variable number of columns in the rows.

Please change its 4th param. True to False , which means that a variable number of columns is not allowed :

Local $aRetArray = _StringSplit2D_EX($sFileRead, ",", @CRLF, False) ; False doesn't expand, it means the number of columns must be the same in all rows.

Now a normal message error is displayed :

Please notice "line 17" in the message : this is what @extended returned, apart from @error, it helps the user to quickly point at the 1st line where the error appears. This can be interesting in case of a big file in input.

For the record, the 5th optional parameter allows to add 1 or more empty columns at the right of the array, during its creation, because sometimes we need them. Well... if I added this 5th optional parameter, then you can be sure I needed it one day

If you want to try the 5th parameter, just use this line in the script, it will add 5 empty columns on the right, from Col6 to Col10

Local $aRetArray = _StringSplit2D_EX($sFileRead, ",", @CRLF, True, 5)

Thanks for reading

Edited 5 hours ago by pixelsearch
return the 1st culprit line number in @extended

[thegoodguy]

3 hours ago, ioa747 said:

the items 1.csv file has some issues like the blank line

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

Local $aRetArray, $sFilePath = @ScriptDir & "\items2.csv"

_FileReadToArray($sFilePath, $aRetArray, Default, ",")
_ArrayDisplay($aRetArray, "$aRetArray")

 

Edit:
the file items 1.csv has some issues ...  nor does it have a fixed number of columns

#include <Array.au3>

Local Const $sTempCSVFile = @ScriptDir & "\items1.csv"
Local $aDataArray = FileReadToArray($sTempCSVFile)

;~ _ArrayDisplay($aDataArray, "Autorunsc Data Loaded into Array")

For $i = 0 To UBound($aDataArray) - 1
    Local $aPart = StringSplit($aDataArray[$i], ",")
    ConsoleWrite($i & ") $aParts=" & $aPart[0] & @CRLF)
Next

 

 

Oddly, If I introduce a blank line to items2.csv, it too fails, However, if I remove the blank line from items1.csv, it continues to fail.

The items1.csv does sort of have fixed columns. The consecutive commas are separating null values.

[thegoodguy]

1 hour ago, Nine said:

Take also a look at _FileReadToArray using the $FRTA_INTARRAYS flag...

Setting this flag filps the axes of the array from 6x36 to 36x6 of item1.csv. Oddly, the script ends without error or output with items2.csv.

[thegoodguy]

Thanks for the work-arounds.

It would just be nice to know why items1.csv fails. Which contents upsets Autoit.

 

Thanks again.

[ioa747]

24 minutes ago, thegoodguy said:

The items1.csv does sort of have fixed columns. The consecutive commas are separating null values.

This is not true, and the second example that counts "," proves it.

and this is the reason for failure

[pixelsearch]

44 minutes ago, thegoodguy said:

t would just be nice to know why items1.csv fails. Which contents upsets Autoit.

items1.csv :
16 first lines got 5 commas each
Line 17 ("Large house/mansion") got only 4
Plenty of other lines got only 4 commas, one line got only 3 commas etc...

Edit: the 1st thing to do in your script is to check @error so you'll know what is happening :

_FileReadToArray($sFilePath, $aRetArray, Default, ",")
If @error Then Exit MsgBox(0, "_FileReadToArray", "@error = " & @error)

It will show an error = 3, which means (help file) :

3 - File lines have different numbers of fields (only if $FRTA_INTARRAYS flag not set)

To retrieve the 1st line where this error occured, I modified temporarily a line in _FileReadToArray, to return also the line number in @extended :

; Return error
; Return SetError(3, 0, 0)

; Return error AND extended
Return SetError(3, $i, 0)

It would be great in this function (and in a few others too !) to return not only @error but also @extended when possible. It would help the user to quickly point the line where the issue appears etc...

Edited 9 hours ago by pixelsearch
Comments about a possible @extended in _FileReadToArray

[thegoodguy]

After a lot of experimenting with removing different characters, I figured it out 

 

Excel allows commas within data elements (cells) of CSV file by putting quotes around the data.  _FileReadToArray sees quotes as part of the data and commas as separators, full-stop (punning).

 

Removing the commas within the data solves the problem. Replacing the delimiter commas with something else (e.g semi-colon) and specify it within  would work too. You just have to deal with a lot of quotes that Excel bungs into the CSV file. I've attached the corrected items1.csv file if anyone is interested.

 

Maybe this will help someone that's trying to get Excel output into an array.

 

Thanks all.

items1.csv