Reading CSV with mixed delimiters?

[fopetesl]

I have a csv file with delimiters "," and @CRLF

After trying several different examples (simple ones!) I still haven't resolved an answer

#include <Array.au3>
#include <File.au3>
;#include "ArrayMultiColSort.au3"

Global $BatchDir = "C:\ncat\"
FileChangeDir($BatchDir)
$sFile = "mod1.csv"

; Read file into a 2D array
Global $aArray

_FileReadToArray($sFile, $aArray, $FRTA_NOCOUNT, ",")

; And here it is
_ArrayDisplay($aArray, "Original", Default, 8)

Firstly it throws a MsgBox error "No array variable passed to function" _ArrayDisplay(), so no displayed data

Second and probably more important how do I get _FileReadToArray() to split the imported array[][] into rows and columns?

I tried "," & @CRLF without success.

[fopetesl]

I have a csv file with delimiters "," and @CRLF

After trying several different examples (simple ones!) I still haven't resolved an answer

#include <Array.au3>
#include <File.au3>
;#include "ArrayMultiColSort.au3"

Global $BatchDir = "C:\ncat\"
FileChangeDir($BatchDir)
$sFile = "mod1.csv"

; Read file into a 2D array
Global $aArray

_FileReadToArray($sFile, $aArray, $FRTA_NOCOUNT, ",")

; And here it is
_ArrayDisplay($aArray, "Original", Default, 8)

Firstly it throws a MsgBox error "No array variable passed to function" _ArrayDisplay(), so no displayed data

Second and probably more important how do I get _FileReadToArray() to split the imported array[][] into rows and columns?

I tried "," & @CRLF without success.

[Jfish]

The regex guys probably have a more elegant solution but for me I would just read it into a string first.  Then I would string replace the @crlf with the comma and the do a string split on the new string using a comma as the delimiter.  That function also returns an array.

[BugFix]

#include <File.au3>

Global $BatchDir = "C:\ncat\"
FileChangeDir($BatchDir)
$sFile = "mod1.csv"

; Read file into a array
Global $aArray

_FileReadToArray($sFile, $aArray, $FRTA_NOCOUNT)  ; param: "," was wrong, linebreak is: @CRLF, the default delimiter

; now create an 2D-Array to hold row/column
; 1st count the fields in line
StringReplace($aArray[0], ',', ',')
Local $iCountField = @extended +1
Local $aArrayRowCol[UBound($aArray)][$iCountField]

Local $aSplit
; iterate through the line array and split every line in field-values
For $i = 0 To UBound($aArray) -1
    $aSplit = StringSplit($aArray[$i], ',')
    For $j = 1 To $aSplit[0]
        $aArrayRowCol[$i][$j] = $aSplit[$j]
    Next
Next

 ; The result
_ArrayDisplay($aArrayRowCol)

 

[Melba23]

Threads merged.

M23

[Skysnake]

Would it be possible to provide some sample code? How can "@CRLF" be used as a CSV delimiter?

[BugFix]

7 minutes ago, Skysnake said:

Would it be possible to provide some sample code? How can "@CRLF" be used as a CSV delimiter?

I think, fopetesl means:  line has fields with delimiter "," and ends with @CRLF.

[Skysnake]

Try this

1. Open with Notepad++ and change line endings.  If ending with @CRLF really should not be a problem...

2. Try this CSVSplit

Skysnake

[Subz]

If you have Excel you can read it directly into a 2d Array by using the Excel UDF.

[Melba23]

fopetesl,

I can read a "comma and @CRLF" delimited file with no problem:

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

$sFilePath = "Test.csv"

If not FileExists($sFilePath) Then

    $sData = "A1,A2,A3" & @CRLF & "B1,B2,B3" & @CRLF & "C1,C2,C3"

    $hFile = FileOpen($sFilePath, $FO_OVERWRITE)

    FileWrite($hFile, $sData)

    FileClose($hFile)

EndIf

Local $aRetArray

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

_ArrayDisplay($aRetArray, "", Default, 8)

But I wonder if your file has the same number of comma-separated elements on each row. Because if it does not then _FileReadToArray will fail with @error 3 (as explained in the Help file) and you do not get an array returned - which would explain why you get the MsgBox telling you just that.

As always, it helps to do some debugging yourself before posting - or at the very least post an example of the file you are using so that we can test with the same data.

M23

[Bowmore]

One issue that can often cause a failure when attempting to read a delimited file, is a blank line in the file, especially the last line.

[fopetesl]

18 hours ago, Melba23 said:

fopetesl,

I can read a "comma and @CRLF" delimited file with no problem:

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

$sFilePath = "Test.csv"

If not FileExists($sFilePath) Then

    $sData = "A1,A2,A3" & @CRLF & "B1,B2,B3" & @CRLF & "C1,C2,C3"

    $hFile = FileOpen($sFilePath, $FO_OVERWRITE)

    FileWrite($hFile, $sData)

    FileClose($hFile)

EndIf

Local $aRetArray

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

_ArrayDisplay($aRetArray, "", Default, 8)

But I wonder if your file has the same number of comma-separated elements on each row. Because if it does not then _FileReadToArray will fail with @error 3 (as explained in the Help file) and you do not get an array returned - which would explain why you get the MsgBox telling you just that.

As always, it helps to do some debugging yourself before posting - or at the very least post an example of the file you are using so that we can test with the same data.

M23

This is a sample CSV presented in three ways 1) as a CSV file ascidata_mod2.csv;

2) as text, (just the relevant part)

,P1,CvP1,N10,N15,N20,N25,N30,N35,N40,N45,N50,P95,P90,P75,P50,P25,P10,P5,P2.5,P1
Pev1,59,48.4,0.5,5.4,10.3,15.1,20,24.9,29.8,34.7,39.6,14.5,19.5,35,60.5,86,99.2,102.2,104,106
,P2,CvP2,N10,N15,N20,N25,N30,N35,N40,N45,N50,P95,P90,P75,P50,P25,P10,P5,P2.5,P1
Pev2,74,34.5,0.1,1.1,2.5,4.3,6.5,9.2,12.2,15.7,19.5,26.5,36.2,56.2,79,96,101.8,103.8,105.5,107.2
P1 FREQ,P1 HIST,P2 FREQ,P2 HIST,,Pev1,,,,,,Pev2,,,,,,,,,
100,0,100,0,P1,59,,,,P2,74,,,,,,,,,,
100,0,100,0,CvP1,48.4,,,,CvP2,34.5,,,,,,,,,,
100,0,100,0,N10,0.5,P95,14.5,,N10,0.1,P95,26.5,,,,,,,,
100,0,100,0,N15,5.4,P90,19.5,,N15,1.1,P90,36.2,,,,,,,,
100,0,100,0,N20,10.3,P75,35,,N20,2.5,P75,56.2,,,,,,,,
100,0,100,0,N25,15.1,P50,60.5,,N25,4.3,P50,79,,,,,,,,
100,0,100,0,N30,20,P25,86,,N30,6.5,P25,96,,,,,,,,
100,0,100,0,N35,24.9,P10,99.2,,N35,9.2,P10,101.8,,,,,,,,
100,0,100,0,N40,29.8,P5,102.2,,N40,12.2,P5,103.8,,,,,,,,
100,0,100,0,N45,34.7,P2.5,104,,N45,15.7,P2.5,105.5,,,,,,,,
100,0,100,0,N50,39.6,P1,106,,N50,19.5,P1,107.2,,,,,,,,
100,0,100,0
100,0,100,0

3) as binary ascidata_mod2_binary.jpg

The cells I am particularly interested in extracting F6-F16, H6-H16, K6-K16 & M6-16

Editing the CSV file manually is not an option. The CSV is created from interpreted raw data and the actual layout can be adjusted somewhat but the CSV data must be accessible.

 

ascidata_mod2.csv

[Melba23]

fopetesl,

As I suspected, the lines do not contain the same number of elements, so _FileReadToArray will not create a "square" 2D array and will return @error 3. See what I mean about doing some errorchecking on the code?

So we have to do some more manipulation to get the data into a 2D array:

expandcollapse popup

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

$sFilePath = "Test.csv"

; Create file if required
If Not FileExists($sFilePath) Then

    $sData = ",P1,CvP1,N10,N15,N20,N25,N30,N35,N40,N45,N50,P95,P90,P75,P50,P25,P10,P5,P2.5,P1" & @CRLF & _
                    "Pev1,59,48.4,0.5,5.4,10.3,15.1,20,24.9,29.8,34.7,39.6,14.5,19.5,35,60.5,86,99.2,102.2,104,106" & @CRLF & _
                    ",P2,CvP2,N10,N15,N20,N25,N30,N35,N40,N45,N50,P95,P90,P75,P50,P25,P10,P5,P2.5,P1" & @CRLF & _
                    "Pev2,74,34.5,0.1,1.1,2.5,4.3,6.5,9.2,12.2,15.7,19.5,26.5,36.2,56.2,79,96,101.8,103.8,105.5,107.2" & @CRLF & _
                    "P1 FREQ,P1 HIST,P2 FREQ,P2 HIST,,Pev1,,,,,,Pev2,,,,,,,,," & @CRLF & _
                    "100,0,100,0,P1,59,,,,P2,74,,,,,,,,,," & @CRLF & _
                    "100,0,100,0,CvP1,48.4,,,,CvP2,34.5,,,,,,,,,," & @CRLF & _
                    "100,0,100,0,N10,0.5,P95,14.5,,N10,0.1,P95,26.5,,,,,,,," & @CRLF & _
                    "100,0,100,0,N15,5.4,P90,19.5,,N15,1.1,P90,36.2,,,,,,,," & @CRLF & _
                    "100,0,100,0,N20,10.3,P75,35,,N20,2.5,P75,56.2,,,,,,,," & @CRLF & _
                    "100,0,100,0,N25,15.1,P50,60.5,,N25,4.3,P50,79,,,,,,,," & @CRLF & _
                    "100,0,100,0,N30,20,P25,86,,N30,6.5,P25,96,,,,,,,," & @CRLF & _
                    "100,0,100,0,N35,24.9,P10,99.2,,N35,9.2,P10,101.8,,,,,,,," & @CRLF & _
                    "100,0,100,0,N40,29.8,P5,102.2,,N40,12.2,P5,103.8,,,,,,,," & @CRLF & _
                    "100,0,100,0,N45,34.7,P2.5,104,,N45,15.7,P2.5,105.5,,,,,,,," & @CRLF & _
                    "100,0,100,0,N50,39.6,P1,106,,N50,19.5,P1,107.2,,,,,,,," & @CRLF & _
                    "100,0,100,0" & @CRLF & _
                    "100,0,100,0"
    $hFile = FileOpen($sFilePath, $FO_OVERWRITE)
    FileWrite($hFile, $sData)
    FileClose($hFile)
EndIf

; Read file to 1D array
Global $aLines
_FileReadToArray($sFilePath, $aLines)
;_ArrayDisplay($aLines, "", Default, 8)

;Global $aComma[UBound($aLines)] ; Use this if you want to see how many commas were found on each line
$iMaxComma = 0
For $i = 0 To UBound($aLines) - 1
    StringReplace($aLines[$i], ",", "")
    If @extended > $iMaxComma Then
        $iMaxComma = @extended
        ;$aComma[$i] = @extended
    EndIf
Next
; _ArrayDisplay($aComma, "", Default, 8)

; Now create  asuitably sized 2D array
Global $aElements[UBound($aLines)][$iMaxComma + 1]
; And split each line into it
For $i = 0 To UBound($aLines) - 1
    $aSplit = StringSplit($aLines[$i], ",")
    For $j = 1 To $aSplit[0]
        $aElements[$i][$j - 1] = $aSplit[$j]
    Next
Next
; And here is the result
_ArrayDisplay($aElements, "", Default, 8)

Now you get a 2D array and can access the elements you require.

M23

[fopetesl]

Works just as described, M23. Thank you.

But, when I attempt to extract 'cells' I get the same result as _ArrayDisplay()

; And here is the result
;_ArrayDisplay($aElements, "", Default, 8); <<< works fine
 MsgBox($MB_OK, "Data Results", $aElements[6][4] & @CRLF & "  " & $aElements[6][5])

 

[Melba23]

fopetesl,

I get a MsgBox with "P1" & "59", which is what I would expect.

M23

[fopetesl]

35 minutes ago, Melba23 said:

fopetesl,

I get a MsgBox with "P1" & "59", which is what I would expect.

M23

Ooops! More finger trouble! Excess of variables.

Problem solved

[fopetesl]

Yes, I now can access the elements I want.

However I would like to display in a pop up GUI not a message box unless I can make a MsgBox look like a GUI with options to print and/or exit _and_ make it larger.

I've looked at ComboBox, GUICtrlCreateEdit/GUICtrlSetData, TreeView plus other examples of displaying text in a GUI.  None fit tidily and/or don't have the user options I'd like.

Edited March 30, 2017 by fopetesl
MsgBox fixed size

[Melba23]

fopetesl,

You will have to create your own custom dialog to do that. A simple GUI with a ListView and a few buttons should suffice.

M23