Jump to content

_FileReadToArray - CSV to Array


 Share

Recommended Posts

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 by You
csv3

Regards,
 

Link to comment
Share on other sites

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)

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

@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 by czardas
Link to comment
Share on other sites

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

 

Regards,
 

Link to comment
Share on other sites

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 by czardas
Link to comment
Share on other sites

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

 

Regards,
 

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...