Trong Posted April 24, 2016 Posted April 24, 2016 (edited) 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 Enjoy my work? Buy me a 🍻 or tip via ❤️ PayPal
czardas Posted April 24, 2016 Posted 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) Trong 1 operator64 ArrayWorkshop
Malkey Posted April 24, 2016 Posted 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. expandcollapse popup;#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 Trong 1
czardas Posted April 24, 2016 Posted April 24, 2016 (edited) @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 operator64 ArrayWorkshop
Trong Posted April 24, 2016 Author Posted April 24, 2016 Enjoy my work? Buy me a 🍻 or tip via ❤️ PayPal
czardas Posted April 24, 2016 Posted April 24, 2016 Please post the csv for the last example. operator64 ArrayWorkshop
Trong Posted April 24, 2016 Author Posted 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 Enjoy my work? Buy me a 🍻 or tip via ❤️ PayPal
czardas Posted April 24, 2016 Posted April 24, 2016 (edited) 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#Standardizationhttps://tools.ietf.org/html/rfc4180#section-2 Edited April 24, 2016 by czardas operator64 ArrayWorkshop
Trong Posted April 24, 2016 Author Posted April 24, 2016 With "StringRegExp" of Malkey: expandcollapse popupGlobal $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 Enjoy my work? Buy me a 🍻 or tip via ❤️ PayPal
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now