Jump to content

Reading CSV with mixed delimiters?


Recommended Posts

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.

The most powerful number in the Universe.  Zero.

Link to comment
Share on other sites

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.

The most powerful number in the Universe.  Zero.

Link to comment
Share on other sites

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.

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Link to comment
Share on other sites

#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)

 

Best Regards BugFix  

Link to comment
Share on other sites

  • Moderators

Threads merged.

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see 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

 

Link to comment
Share on other sites

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.

Best Regards BugFix  

Link to comment
Share on other sites

  • Moderators

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

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see 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

 

Link to comment
Share on other sites

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.

"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to build bigger and better idiots. So far, the universe is winning."- Rick Cook

Link to comment
Share on other sites

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

ascidata_mod2_binary.jpg

The most powerful number in the Universe.  Zero.

Link to comment
Share on other sites

  • Moderators

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:

#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

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see 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

 

Link to comment
Share on other sites

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])

 

The most powerful number in the Universe.  Zero.

Link to comment
Share on other sites

  • Moderators

fopetesl,

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

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see 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

 

Link to comment
Share on other sites

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 by fopetesl
MsgBox fixed size

The most powerful number in the Universe.  Zero.

Link to comment
Share on other sites

  • Moderators

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

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see 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

 

Link to comment
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
 Share

×
×
  • Create New...