_FileReadToArray - CSV to Array

Trong · April 24, 2016

CSV1:

"Index","Name","Home Address","Last Love"
"1","Rola Takizawa","Nữ hoàng, Nga Nhật","24/04/2016 22:37:00 PM"
"2","Saori Hara","Hình Ảnh Nóng, Đức Nhật","21/03/2016 21:07:00 PM"
"3","Ozawa","phổ biến nhất","23/04/2016 23:31:00 PM"
"3","Akiho Ameri Ichinose","Kanagawa, Ameri, Ichinose","22/04/2016 21:50:00 PM"

CSV2:

Index,Name,Home Address,Last Love
1,Takizawa,"Nữ hoàng, Nga Nhật","24/04/2016 22:37:00 PM"
2,"Saori Hara","Hình Ảnh Nóng, Đức Nhật","21/03/2016 21:07:00 PM"
3,Ozawa,"phổ biến nhất","23/04/2016 23:31:00 PM"
3,"Akiho Ameri Ichinose","Kanagawa, Ameri, Ichinose","22/04/2016 21:50:00 PM"

CSV3:

Index,Name,Home Address,Last Love
1,Takizawa,"Nữ hoàng, Nga Nhật",24/04/2016 22:37:00 PM
2,Saori Hara,"Hình Ảnh Nóng, Đức Nhật",21/03/2016 21:07:00 PM
3,Ozawa,phổ biến nhất,23/04/2016 23:31:00 PM
3,Akiho Ameri Ichinose,"Kanagawa, Ameri, Ichinose",22/04/2016 21:50:00 PM

----------------------------------------------------------------------------------
Global $CSV3 = 'Index,Name,Home Address,Last Love' & @CRLF
$CSV2 &= '1,Takizawa,"Nữ hoàng, Nga Nhật",24/04/2016 22:37:00 PM' & @CRLF
$CSV2 &= '2,Saori Hara,"Hình Ảnh Nóng, Đức Nhật",21/03/2016 21:07:00 PM' & @CRLF
$CSV2 &= '3,Ozawa,phổ biến nhất,23/04/2016 23:31:00 PM' & @CRLF
$CSV2 &= '3,Akiho Ameri Ichinose,"Kanagawa, Ameri, Ichinose",22/04/2016 21:50:00 PM' & @CRLF

Does not work when have more ","

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <GUIListView.au3>
#include <Array.au3>
#include <File.au3>

Global $CSV1 = '"Index","Name","Home Address","Last Love"' & @CRLF
$CSV1 &= '"1","Rola Takizawa","Nữ hoàng, Nga Nhật","24/04/2016 22:37:00 PM"' & @CRLF
$CSV1 &= '"2","Saori Hara","Hình Ảnh Nóng, Đức Nhật","21/03/2016 21:07:00 PM"' & @CRLF
$CSV1 &= '"3","Ozawa","phổ biến nhất","23/04/2016 23:31:00 PM"' & @CRLF
$CSV1 &= '"3","Akiho Ameri Ichinose","Kanagawa, Ameri, Ichinose","22/04/2016 21:50:00 PM"' & @CRLF

Global $CSV2 = "Index,Name,Home Address,Last Love" & @CRLF
$CSV2 &= "1,Takizawa,'Nữ hoàng, Nga Nhật','24/04/2016 22:37:00 PM'" & @CRLF
$CSV2 &= "2,'Saori Hara','Hình Ảnh Nóng, Đức Nhật','21/03/2016 21:07:00 PM'" & @CRLF
$CSV2 &= "3,Ozawa,'phổ biến nhất','23/04/2016 23:31:00 PM'" & @CRLF
$CSV2 &= "3,'Akiho Ameri Ichinose','Kanagawa, Ameri, Ichinose','22/04/2016 21:50:00 PM'" & @CRLF

Local $hOpen=FileOpen("CSV1.csv",128)
FileWrite($hOpen,$CSV1)
FileClose($hOpen)

Local $hOpen=FileOpen("CSV2.csv",128)
FileWrite($hOpen,$CSV2)
FileClose($hOpen)

Global $rArrayCSV1,$rArrayCSV2

Local $ArrayCSV1 = _FileReadToArray("CSV2.csv", $rArrayCSV1, Default,',')
ConsoleWrite( $ArrayCSV1 & ' - ' & 'error = ' & @error  & @CRLF)
_ArrayDisplay($rArrayCSV1)

Local $ArrayCSV2 = _FileReadToArray("CSV2.csv", $rArrayCSV2, Default,',')
ConsoleWrite( $ArrayCSV2 & ' - ' & 'error = ' & @error  & @CRLF)
_ArrayDisplay($rArrayCSV2)

Edited April 24, 2016 by You
csv3

czardas · April 24, 2016

Have you tried this? https://www.autoitscript.com/forum/topic/155748-csvsplit/ You need to read the CSV file using FileRead() as shown in post #8.

#include <Array.au3>
#include 'CSVSplit.au3'

Local $sCSV1 = '"Index","Name","Home Address","Last Love"' & @CRLF & _
'"1","Rola Takizawa","Nữ hoàng, Nga Nhật","24/04/2016 22:37:00 PM"' & @CRLF & _
'"2","Saori Hara","Hình Ảnh Nóng, Đức Nhật","21/03/2016 21:07:00 PM"' & @CRLF & _
'"3","Ozawa","phổ biến nhất","23/04/2016 23:31:00 PM"' & @CRLF & _
'"3","Akiho Ameri Ichinose","Kanagawa, Ameri, Ichinose","22/04/2016 21:50:00 PM"'

Local $sCSV2 = 'Index,Name,Home Address,Last Love' & @CRLF & _
'1,Takizawa,"Nữ hoàng, Nga Nhật","24/04/2016 22:37:00 PM"' & @CRLF & _
'2,"Saori Hara","Hình Ảnh Nóng, Đức Nhật","21/03/2016 21:07:00 PM"' & @CRLF & _
'3,Ozawa,"phổ biến nhất","23/04/2016 23:31:00 PM"' & @CRLF & _
'3,"Akiho Ameri Ichinose","Kanagawa, Ameri, Ichinose","22/04/2016 21:50:00 PM"'

Local $aArray1 = _CSVSplit($sCSV1)
_ArrayDisplay($aArray1)

Local $aArray2 = _CSVSplit($sCSV2)
_ArrayDisplay($aArray2)

Malkey · April 24, 2016

The UDF "_FileReadToArray" splits each line on all delimiters (,), including comas within quotes.
This example uses a modified "_FileReadToArray" function copied from the include file, "..\AutoIt3\Include\File.au3".
Within this "_FileReadToArrayModified" function the two "StringSplit" functions have be replaced with two "StringRegExp" functions to allow comas ($sDelimiter) within quotes not to be recognized as delimiters.

;#include <GUIConstantsEx.au3>
;#include <MsgBoxConstants.au3>
;#include <GUIListView.au3>
#include <Array.au3>
#include <File.au3>

Global $CSV1 = '"Index","Name","Home Address","Last Love"' & @CRLF
$CSV1 &= '"1","Rola Takizawa","N? hoàng, Nga Nh?t","24/04/2016 22:37:00 PM"' & @CRLF
$CSV1 &= '"2","Saori Hara","Hình ?nh Nóng, Ð?c Nh?t","21/03/2016 21:07:00 PM"' & @CRLF
$CSV1 &= '"3","Ozawa","ph? bi?n nh?t","23/04/2016 23:31:00 PM"' & @CRLF
$CSV1 &= '"3","Akiho Ameri Ichinose","Kanagawa, Ameri, Ichinose","22/04/2016 21:50:00 PM"' & @CRLF

Global $CSV2 = "Index,Name,Home Address,Last Love" & @CRLF
$CSV2 &= "1,Takizawa,'N? hoàng, Nga Nh?t','24/04/2016 22:37:00 PM'" & @CRLF
$CSV2 &= "2,'Saori Hara','Hình ?nh Nóng, Ð?c Nh?t','21/03/2016 21:07:00 PM'" & @CRLF
$CSV2 &= "3,Ozawa,'ph? bi?n nh?t','23/04/2016 23:31:00 PM'" & @CRLF
$CSV2 &= "3,'Akiho Ameri Ichinose','Kanagawa, Ameri, Ichinose','22/04/2016 21:50:00 PM'" & @CRLF

;Local $hOpen=FileOpen("CSV1.csv",128)
If FileExists("CSV1.csv") = 0 Then FileWrite("CSV1.csv", $CSV1)
;FileClose($hOpen)

;Local $hOpen=FileOpen("CSV2.csv",128)
If FileExists("CSV2.csv") = 0 Then FileWrite("CSV2.csv", $CSV2)
;FileClose($hOpen)

Global $rArrayCSV1, $rArrayCSV2

$Array1 = _FileReadToArrayModified("CSV1.csv", $rArrayCSV1, 0, ",")
ConsoleWrite($Array1 & ' - ' & 'error = ' & @error & @CRLF)
_ArrayDisplay($rArrayCSV1)

Local $Array2 = _FileReadToArrayModified("CSV2.csv", $rArrayCSV2, 0, ",")
ConsoleWrite($Array2 & ' - ' & 'error = ' & @error & @CRLF)
_ArrayDisplay($rArrayCSV2)


; Copied from #include <File.au3> and modified in 2 places.
Func _FileReadToArrayModified($sFilePath, ByRef $vReturn, $iFlags = $FRTA_COUNT, $sDelimiter = "")
    ; Clear the previous contents
    $vReturn = 0

    If $iFlags = Default Then $iFlags = $FRTA_COUNT
    If $sDelimiter = Default Then $sDelimiter = ""

    ; Set "array of arrays" flag
    Local $bExpand = True
    If BitAND($iFlags, $FRTA_INTARRAYS) Then
        $bExpand = False
        $iFlags -= $FRTA_INTARRAYS
    EndIf
    ; Set delimiter flag
    Local $iEntire = $STR_CHRSPLIT
    If BitAND($iFlags, $FRTA_ENTIRESPLIT) Then
        $iEntire = $STR_ENTIRESPLIT
        $iFlags -= $FRTA_ENTIRESPLIT
    EndIf
    ; Set row count and split count flags
    Local $iNoCount = 0
    If $iFlags <> $FRTA_COUNT Then
        $iFlags = $FRTA_NOCOUNT
        $iNoCount = $STR_NOCOUNT
    EndIf

    ; Check delimiter
    If $sDelimiter Then
        ; Read file into an array
        Local $aLines = FileReadToArray($sFilePath)
        If @error Then Return SetError(@error, 0, 0)

        ; Get first dimension and add count if required
        Local $iDim_1 = UBound($aLines) + $iFlags
        ; Check type of return array
        If $bExpand Then ; All lines have same number of fields
            ; Count fields in first line
            Local $iDim_2 = UBound(StringSplit($aLines[0], $sDelimiter, $iEntire + $STR_NOCOUNT))
            ; Size array
            Local $aTemp_Array[$iDim_1][$iDim_2]
            ; Declare the variables
            Local $iFields, _
                    $aSplit
            ; Loop through the lines
            For $i = 0 To $iDim_1 - $iFlags - 1
                ; Split each line as required
                ;$aSplit = StringSplit($aLines[$i], $sDelimiter, $iEntire + $STR_NOCOUNT)
                ;########## Replaced previous line with next line ############# - Malkey
                $aSplit = StringRegExp($aLines[$i], '(["''][^"'']+["'']|[^' & $sDelimiter & ']+)' & $sDelimiter & '?\s*', 3)
                ; Count the items
                $iFields = UBound($aSplit)
                If $iFields <> $iDim_2 Then
                    ; Return error
                    Return SetError(3, 0, 0)
                EndIf
                ; Fill this line of the array
                For $j = 0 To $iFields - 1
                    $aTemp_Array[$i + $iFlags][$j] = $aSplit[$j]
                Next
            Next
            ; Check at least 2 columns
            If $iDim_2 < 2 Then Return SetError(4, 0, 0)
            ; Set dimension count
            If $iFlags Then
                $aTemp_Array[0][0] = $iDim_1 - $iFlags
                $aTemp_Array[0][1] = $iDim_2
            EndIf
        Else ; Create "array of arrays"
            ; Size array
            Local $aTemp_Array[$iDim_1]
            ; Loop through the lines
            For $i = 0 To $iDim_1 - $iFlags - 1
                ; Split each line as required
                ;$aTemp_Array[$i + $iFlags] = StringSplit($aLines[$i], $sDelimiter, $iEntire + $iNoCount)
                ;########## Replaced previous line with next line ############# - Malkey
                $aTemp_Array[$i + $iFlags] = StringRegExp($aLines[$i], '(["''][^"'']+["'']|[^' & $sDelimiter & ']+)' & $sDelimiter & '?\s*', 3)
            Next
            ; Set dimension count
            If $iFlags Then
                $aTemp_Array[0] = $iDim_1 - $iFlags
            EndIf
        EndIf
        ; Return the array
        $vReturn = $aTemp_Array
    Else ; 1D
        If $iFlags Then
            Local $hFileOpen = FileOpen($sFilePath, $FO_READ)
            If $hFileOpen = -1 Then Return SetError(1, 0, 0)
            Local $sFileRead = FileRead($hFileOpen)
            FileClose($hFileOpen)

            If StringLen($sFileRead) Then
                $vReturn = StringRegExp(@LF & $sFileRead, "(?|(\N+)\z|(\N*)(?:\R))", 3)
                ConsoleWrite("reg" & @LF)
                $vReturn[0] = UBound($vReturn) - 1
            Else
                Return SetError(2, 0, 0)
            EndIf
        Else
            $vReturn = FileReadToArray($sFilePath)
            If @error Then
                $vReturn = 0
                Return SetError(@error, 0, 0)
            EndIf
        EndIf

    EndIf
    Return 1
EndFunc   ;==>_FileReadToArrayModified

czardas · April 24, 2016

@Malkey Nice regexp, but I would say it needs more work. Your code doesn't work for typically accepted CSV syntax.

Index,Name,Home Address,Last Love
1,Takizawa,"Nữ hoàng, Nga Nhật","24/04/2016 22:37:00 PM"
2,"Saori Hara","Hình Ảnh Nóng, Đức Nhật","21/03/2016 21:07:00 PM"
3,Ozawa,"phổ biến nhất","23/04/2016 
23:31:00 PM"
3,"Akiho Ameri Ichinose","Kanagawa, ""Ameri"", Ichinose","22/04/2016 21:50:00 PM"

Edited April 24, 2016 by czardas

Trong · April 24, 2016

czardas · April 24, 2016

Please post the csv for the last example.

Trong · April 24, 2016

2 minutes ago, czardas said:

Please post the csv for the last example.

Global $CSV2 = "Index,Name,Home Address,Last Love" & @CRLF
$CSV2 &= "1,Takizawa,'Nữ hoàng, Nga Nhật','24/04/2016 22:37:00 PM'" & @CRLF
$CSV2 &= "2,'Saori Hara','Hình Ảnh Nóng, Đức Nhật','21/03/2016 21:07:00 PM'" & @CRLF
$CSV2 &= "3,Ozawa,'phổ biến nhất','23/04/2016 23:31:00 PM'" & @CRLF
$CSV2 &= "3,'Akiho Ameri Ichinose','Kanagawa, Ameri, Ichinose','22/04/2016 21:50:00 PM'" & @CRLF

czardas · April 24, 2016

Okay thank you. The CSV above is one of the many variations that exist, which I didn't include. Perhaps I will modify the function in the future, but I have a lot of other projects going on right now. I used the following standardized rules from Wikipedia when I wrote my function. Perhaps the Wikipedia description could be clearer. The text seems to suggest that using single quotes to enclose fields is not part of the standard format.

Quote

MS-DOS-style lines that end with (CR/LF) characters (optional for the last line)

An optional header record (there is no sure way to detect whether it is present, so care is required when importing).

Each record "should" contain the same number of comma-separated fields.

Any field may be quoted (with double quotes).

Fields containing a line-break, double-quote, and/or commas should be quoted. (If they are not, the file will likely be impossible to process correctly).

A (double) quote character in a field must be represented by two (double) quote characters.

https://en.wikipedia.org/wiki/Comma-separated_values#Standardization
https://tools.ietf.org/html/rfc4180#section-2

Edited April 24, 2016 by czardas

Trong · April 24, 2016

With "StringRegExp" of Malkey:

Global $CSV1 = '"Index","Name","Home Address","Last Love"' & @CRLF
$CSV1 &= '"1","Rola Takizawa","Nữ hoàng, Nga Nhật","24/04/2016 22:37:00 PM"' & @CRLF
$CSV1 &= '"2","Saori Hara","Hình Ảnh Nóng, Đức Nhật","21/03/2016 21:07:00 PM"' & @CRLF
$CSV1 &= '"3","Ozawa","phổ biến nhất","23/04/2016 23:31:00 PM"' & @CRLF
$CSV1 &= '"3","Akiho Ameri Ichinose","Kanagawa, Ameri, Ichinose","22/04/2016 21:50:00 PM"' & @CRLF

Global $CSV2 = "Index,Name,Home Address,Last Love" & @CRLF
$CSV2 &= "1,Takizawa,'Nữ hoàng, Nga Nhật','24/04/2016 22:37:00 PM'" & @CRLF
$CSV2 &= "2,'Saori Hara','Hình Ảnh Nóng, Đức Nhật','21/03/2016 21:07:00 PM'" & @CRLF
$CSV2 &= "3,Ozawa,'phổ biến nhất','23/04/2016 23:31:00 PM'" & @CRLF
$CSV2 &= "3,'Akiho Ameri Ichinose','Kanagawa, Ameri, Ichinose','22/04/2016 21:50:00 PM'" & @CRLF

Global $CSV3 = 'Index,Name,Home Address,Last Love' & @CRLF
$CSV3 &= '1,Takizawa,"Nữ hoàng, Nga Nhật",24/04/2016 22:37:00 PM' & @CRLF
$CSV3 &= '2,Saori Hara,"Hình Ảnh Nóng, Đức Nhật",21/03/2016 21:07:00 PM' & @CRLF
$CSV3 &= '3,Ozawa,phổ biến nhất,23/04/2016 23:31:00 PM' & @CRLF
$CSV3 &= '3,Akiho Ameri Ichinose,"Kanagawa, Ameri, Ichinose",22/04/2016 21:50:00 PM' & @CRLF

;~ FileWrite("CSV1.csv", $CSV1)
Local $hOpen = FileOpen("CSV1.csv", 2 + 8 + 128)
FileWrite($hOpen, $CSV1)
FileClose($hOpen)

;~ FileWrite("CSV2.csv", $CSV2)
Local $hOpen = FileOpen("CSV2.csv", 2 + 8 + 128)
FileWrite($hOpen, $CSV2)
FileClose($hOpen)

;~ FileWrite("CSV3.csv", $CSV3)
Local $hOpen = FileOpen("CSV3.csv", 2 + 8 + 128)
FileWrite($hOpen, $CSV3)
FileClose($hOpen)

#include <Array.au3>

_ArrayDisplay(_SplitCSV("CSV1.csv"))
_ArrayDisplay(_SplitCSV("CSV2.csv"))
_ArrayDisplay(_SplitCSV("CSV3.csv"))

Func _SplitCSV($sFilePath, $sDelimiter = ",")
    If $sDelimiter = "" Then Return SetError(1, 0, 0)
    Local $aLines = FileReadToArray($sFilePath)
    If @error Then Return SetError(@error > 0, 0, 0)
    Local $sPattern = '(["''][^"'']+["'']|[^' & $sDelimiter & ']+)' & $sDelimiter & '?\s*'
    Local $bLines = StringRegExp($aLines[0], $sPattern, 3)
    Local $iDim_1 = UBound($aLines) + 0
    Local $iDim_2 = UBound($bLines)
    Local $aTemp_Array[$iDim_1][$iDim_2]
    Local $iFields, $aSplit
    For $i = 0 To $iDim_1 - 1
        $aSplit = StringRegExp($aLines[$i], $sPattern, 3)
        $iFields = UBound($aSplit)
        If $iFields <> $iDim_2 Then
            Return SetError(2, 0, 0)
        EndIf
        For $j = 0 To $iFields - 1
            $aTemp_Array[$i + 0][$j] = $aSplit[$j]
        Next
    Next
    If $iDim_2 < 2 Then Return SetError(3, 0, 0)
    Return $aTemp_Array
EndFunc   ;==>_SplitCSV

_FileReadToArray - CSV to Array

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Similar Content