funkey Posted February 21, 2010 Posted February 21, 2010 (edited) Hello friends! My new script is used to edit CSV-files. Just doubleclick the entries you want to edit! Add CRLF to itemtext with Ctrl+{Enter} Now you can create new files. Have fun! Version-Updates: Spoiler ;Version 0.3: February 21, 2010 ;Edit header ;Open file direct with the program ;Filename is in Window title ;Inifile to save the options ;Version 0.4: February 25, 2010 ;Changed the LV-Color ;Optional WinSetOnTop ;Drop files in listview to load ;Resizing ;HTML-Export ;Version 0.5: March 04, 2010 ;Added optional index in first column ;Updated color in listview with $GUI_BKCOLOR_LV_ALTERNATE ;Version 0.6: April 03, 2010 ;Improved saving speed ;Added saving the window position and size ;Added progressbar during saving and opening ;Added 'Delete rows' for selected rows in context menu ;Added 'Delete column' for selected column in context menu ;Added inserting of new columns and rows ;Added making backup files when saving ;Added toolbar ;Fixed some bugs ;Changed index showing ;Version 0.7: April 08, 2010 ;Fixed bugs ;Added: Icon ;Added: Save file when exiting ;Added asterisk (star) to see if file has changed ;Added in context menu: row to clip ;Added in context menu: column to clip ;Added: Support full CSV format <-- thanks a lot to progandy!! ;Added: Enter @CRLF to item with Ctrl+{Enter} ;Version 0.8: April 23, 2010 ;Fixed bugs ;Added: Cut, copy and paste rows ;Added: Renew index after changing row(s) ;Added: Accelerators ;Added: Search and replace strings ;Added: Hide toolbar if you want ;Added: Buttons for row up / row down ;Added: Create new file ;Version 0.8a: December 15, 2021 ;Changed few things to run with actial version of AutoIt CSV-Editor.ico CSV-Editor V0.8a.au3 Edited December 15, 2021 by funkey Changed to make it run with actual version of AutoIt Xandy and mLipok 2 Programming today is a race between software engineers striving tobuild bigger and better idiot-proof programs, and the Universetrying to produce bigger and better idiots.So far, the Universe is winning.
ReFran Posted February 21, 2010 Posted February 21, 2010 Looks good so far. Some hints: - @desktopwidth/... or at least a smaler width/hight as 800/800 (e.g. 600/600) would avoid as first to have to edit. - I would await, drag and drop for files, row up/row down, row delete, row or col to clip and sorting. Excel produced csv files use @lf as cell seperator and @cr as line end seperator. So it doesn't work very well for that. However, thanks for your work on that. Best regards, Reinhard
funkey Posted February 25, 2010 Author Posted February 25, 2010 Update to Version 0.4: February 25, 2010;Changed the Colour of the listview;WinSetOnTop;Drop files in listview to load;Resizing;HTML-Export Programming today is a race between software engineers striving tobuild bigger and better idiot-proof programs, and the Universetrying to produce bigger and better idiots.So far, the Universe is winning.
funkey Posted March 7, 2010 Author Posted March 7, 2010 Version 0.5: March 04, 2010 ;Show the index if you want ;Listview with $GUI_BKCOLOR_LV_ALTERNATE Programming today is a race between software engineers striving tobuild bigger and better idiot-proof programs, and the Universetrying to produce bigger and better idiots.So far, the Universe is winning.
funkey Posted April 3, 2010 Author Posted April 3, 2010 There are lots of news in new version 0.6 ;Improved saving speed ;Added saving the window position and size ;Added progressbar during saving and opening ;Added 'Delete rows' for selected rows in context menu ;Added 'Delete column' for selected column in context menu ;Added inserting of new columns and rows ;Added making backup files when saving ;Added toolbar ;Fixed some bugs ;Changed index showing Have fun!! Programming today is a race between software engineers striving tobuild bigger and better idiot-proof programs, and the Universetrying to produce bigger and better idiots.So far, the Universe is winning.
funkey Posted April 5, 2010 Author Posted April 5, 2010 Added icon.No more comments? Programming today is a race between software engineers striving tobuild bigger and better idiot-proof programs, and the Universetrying to produce bigger and better idiots.So far, the Universe is winning.
wraithdu Posted April 5, 2010 Posted April 5, 2010 (edited) Looks pretty good. It messes up a bit on Google's contacts CSV format, specifically a multiline address or fields that contain the delimiter (these fields are enclosed in quotes but contain commas, for example), but that may be a bit non-standard. I noticed one bug, after saving a new column heading, the numbered index column is shown regardless of the setting. Edited April 5, 2010 by wraithdu
funkey Posted April 6, 2010 Author Posted April 6, 2010 Thanks wraithdu for your comment! Give me a sample of a Google's contacts CSV file so I can find a solution for this.TIA Programming today is a race between software engineers striving tobuild bigger and better idiot-proof programs, and the Universetrying to produce bigger and better idiots.So far, the Universe is winning.
wraithdu Posted April 6, 2010 Posted April 6, 2010 (edited) Here's an example. It's easy to make up one on your own as well. Just create a field that has the delimiter in it and quote it. Also create a field that has a line break and quote it... this is gonna be tougher cause it actually splits the line. This example has the delims and line break in the same quoted field - this is a single record! A possible solution would be a custom parser that looks for quotes and treats anything within quotes as a single field. Name,Given Name,Additional Name,Family Name,Yomi Name,Given Name Yomi,Additional Name Yomi,Family Name Yomi,Name Prefix,Name Suffix,Initials,Nickname,Short Name,Maiden Name,Birthday,Gender,Location,Billing Information,Directory Server,Mileage,Occupation,Hobby,Sensitivity,Priority,Subject,Notes,Group Membership,E-mail 1 - Type,E-mail 1 - Value,E-mail 2 - Type,E-mail 2 - Value,Phone 1 - Type,Phone 1 - Value,Phone 2 - Type,Phone 2 - Value,Phone 3 - Type,Phone 3 - Value,Address 1 - Type,Address 1 - Formatted,Address 1 - Street,Address 1 - City,Address 1 - PO Box,Address 1 - Region,Address 1 - Postal Code,Address 1 - Country,Address 1 - Extended Address,Organization 1 - Type,Organization 1 - Name,Organization 1 - Yomi Name,Organization 1 - Title,Organization 1 - Department,Organization 1 - Symbol,Organization 1 - Location,Organization 1 - Job Description,Website 1 - Type,Website 1 - Value Marley Bob,Bob,,Marley,,,,,,,,,,,,,,,,,,,,,,,* My Contacts,Other,bobmarley@hotmail.com ::: bob.marley.b@company.com,* Other,bobmarley@gmail.com,Mobile,(123) 456-7890,Work,(987) 654-3210,,,Home,"123 Main Street Drive SomeTown, CA 12345",,,,,,,,,,,,,,,,,fb://profile/999999999 Edited April 6, 2010 by wraithdu
ProgAndy Posted April 6, 2010 Posted April 6, 2010 (edited) I tried to create some CSV functions. All files I checked were parsed wihtout errors: expandcollapse popup; #FUNCTION# ==================================================================================================================== ; Name...........: _ParseCSV ; Description ...: Reads a CSV-file ; Syntax.........: _ParseCSV($sFile, $sDelimiters=',', $sQuote='"', $iFormat=0) ; Parameters ....: $sFile - File to read or string to parse ; $sDelimiters - [optional] Fieldseparators of CSV, mulitple are allowed (default: ,;) ; $sQuote - [optional] Character to quote strings (default: ") ; $iFormat - [optional] Encoding of the file (default: 0): ; |-1 - No file, plain data given ; |0 or 1 - automatic (ASCII) ; |2 - Unicode UTF16 Little Endian reading ; |3 - Unicode UTF16 Big Endian reading ; |4 or 5 - Unicode UTF8 reading ; Return values .: Success - 2D-Array with CSV data (0-based) ; Failure - 0, sets @error to: ; |1 - could not open file ; |2 - error on parsing data ; |3 - wrong format chosen ; Author ........: ProgAndy ; Modified.......: ; Remarks .......: ; Related .......: _WriteCSV ; Link ..........: ; Example .......: ; =============================================================================================================================== Func _ParseCSV($sFile, $sDelimiters=',;', $sQuote='"', $iFormat=0) Local Static $aEncoding[6] = [0, 0, 32, 64, 128, 256] If $iFormat < -1 Or $iFormat > 6 Then Return SetError(3,0,0) ElseIf $iFormat > -1 Then Local $hFile = FileOpen($sFile, $aEncoding[$iFormat]), $sLine, $aTemp, $aCSV[1], $iReserved, $iCount If @error Then Return SetError(1,@error,0) $sFile = FileRead($hFile) FileClose($hFile) EndIf If $sDelimiters = "" Or IsKeyword($sDelimiters) Then $sDelimiters = ',;' If $sQuote = "" Or IsKeyword($sQuote) Then $sQuote = '"' $sQuote = StringLeft($sQuote, 1) Local $srDelimiters = StringRegExpReplace($sDelimiters, '[\\\^\-\[\]]', '\\\0') Local $srQuote = StringRegExpReplace($sQuote, '[\\\^\-\[\]]', '\\\0') Local $sPattern = StringReplace(StringReplace('(?m)(?:^|[,])\h*(["](?:[^"]|["]{2})*["]|[^,\r\n]*)(\v+)?',',', $srDelimiters, 0, 1),'"', $srQuote, 0, 1) Local $aREgex = StringRegExp($sFile, $sPattern, 3) If @error Then Return SetError(2,@error,0) $sFile = '' ; save memory Local $iBound = UBound($aREgex), $iIndex=0, $iSubBound = 1, $iSub = 0 Local $aResult[$iBound][$iSubBound] For $i = 0 To $iBound-1 If $iSub = $iSubBound Then $iSubBound += 1 ReDim $aResult[$iBound][$iSubBound] EndIf Select Case StringLen($aREgex[$i])<3 And StringInStr(@CRLF, $aREgex[$i]) $iIndex += 1 $iSub = 0 ContinueLoop Case StringLeft(StringStripWS($aREgex[$i], 1),1)=$sQuote $aREgex[$i] = StringStripWS($aREgex[$i], 3) $aResult[$iIndex][$iSub] = StringReplace(StringMid($aREgex[$i], 2, StringLen($aREgex[$i])-2), $sQuote&$sQuote, $sQuote, 0, 1) Case Else $aResult[$iIndex][$iSub] = $aREgex[$i] EndSelect $aREgex[$i]=0 ; save memory $iSub += 1 Next If $iIndex = 0 Then $iIndex=1 ReDim $aResult[$iIndex][$iSubBound] Return $aResult EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _WriteCSV ; Description ...: Writes a CSV-file ; Syntax.........: _WriteCSV($sFile, Const ByRef $aData, $sDelimiter, $sQuote, $iFormat=0) ; Parameters ....: $sFile - Destination file ; $aData - [Const ByRef] 0-based 2D-Array with data ; $sDelimiter - [optional] Fieldseparator (default: ,) ; $sQuote - [optional] Quote character (default: ") ; $iFormat - [optional] character encoding of file (default: 0) ; |0 or 1 - ASCII writing ; |2 - Unicode UTF16 Little Endian writing (with BOM) ; |3 - Unicode UTF16 Big Endian writing (with BOM) ; |4 - Unicode UTF8 writing (with BOM) ; |5 - Unicode UTF8 writing (without BOM) ; Return values .: Success - True ; Failure - 0, sets @error to: ; |1 - No valid 2D-Array ; |2 - Could not open file ; Author ........: ProgAndy ; Modified.......: ; Remarks .......: ; Related .......: _ParseCSV ; Link ..........: ; Example .......: ; =============================================================================================================================== Func _WriteCSV($sFile, Const ByRef $aData, $sDelimiter=',', $sQuote='"', $iFormat=0) Local Static $aEncoding[6] = [2, 2, 34, 66, 130, 258] If $sDelimiter = "" Or IsKeyword($sDelimiter) Then $sDelimiter = ',' If $sQuote = "" Or IsKeyword($sQuote) Then $sQuote = '"' Local $iBound = UBound($aData, 1), $iSubBound = UBound($aData, 2) If Not $iSubBound Then Return SetError(2,0,0) Local $hFile = FileOpen($sFile, $aEncoding[$iFormat]) If @error Then Return SetError(2,@error,0) For $i = 0 To $iBound-1 For $j = 0 To $iSubBound-1 FileWrite($hFile, $sQuote & StringReplace($aData[$i][$j], $sQuote, $sQuote&$sQuote, 0, 1) & $sQuote) If $j < $iSubBound-1 Then FileWrite($hFile, $sDelimiter) Next FileWrite($hFile, @CRLF) Next FileClose($hFile) Return True EndFunc ; === EXAMPLE =================================================== ;~ #include<Array.au3> ;~ $aResult = _ParseCSV(@ScriptDir & '\test.csv', "\", '$', 4) ;~ _ArrayDisplay($aResult) ;~ _WriteCSV(@ScriptDir & '\written.csv', $aResult, ',', '"', 5) ; =============================================================== Edit: @wraithdu: Your snippet works, too (I had copied it to the clipboard): #include<Array.au3> $aResult = _ParseCSV(ClipGet(), ',', '"', -1) _ArrayDisplay($aResult) Edit: fixed a few errors in _ParseCSV Edited April 7, 2010 by ProgAndy *GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes
ajag Posted April 6, 2010 Posted April 6, 2010 (edited) Here's an example. It's easy to make up one on your own as well. Just create a field that has the delimiter in it and quote it. Also create a field that has a line break and quote it... this is gonna be tougher cause it actually splits the line. This example has the delims and line break in the same quoted field - this is a single record! A possible solution would be a custom parser that looks for quotes and treats anything within quotes as a single field. Name,Given Name,Additional Name,Family Name,Yomi Name,Given Name Yomi,Additional Name Yomi,Family Name Yomi,Name Prefix,Name Suffix,Initials,Nickname,Short Name,Maiden Name,Birthday,Gender,Location,Billing Information,Directory Server,Mileage,Occupation,Hobby,Sensitivity,Priority,Subject,Notes,Group Membership,E-mail 1 - Type,E-mail 1 - Value,E-mail 2 - Type,E-mail 2 - Value,Phone 1 - Type,Phone 1 - Value,Phone 2 - Type,Phone 2 - Value,Phone 3 - Type,Phone 3 - Value,Address 1 - Type,Address 1 - Formatted,Address 1 - Street,Address 1 - City,Address 1 - PO Box,Address 1 - Region,Address 1 - Postal Code,Address 1 - Country,Address 1 - Extended Address,Organization 1 - Type,Organization 1 - Name,Organization 1 - Yomi Name,Organization 1 - Title,Organization 1 - Department,Organization 1 - Symbol,Organization 1 - Location,Organization 1 - Job Description,Website 1 - Type,Website 1 - Value Marley Bob,Bob,,Marley,,,,,,,,,,,,,,,,,,,,,,,* My Contacts,Other,bobmarley@hotmail.com ::: bob.marley.b@company.com,* Other,bobmarley@gmail.com,Mobile,(123) 456-7890,Work,(987) 654-3210,,,Home,"123 Main Street Drive SomeTown, CA 12345",,,,,,,,,,,,,,,,,fb://profile/999999999 Hi funkey, Hint: This issue is well known at the SQL command "LOAD DATA INFILE". There you can define (example): LOAD DATA INFILE [...] FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' [...] This means that if a field is enclosed by double quotes all characters between the double quotes will be recognized as the content of the field regardless if a cahracter is a field (or line) delimiter. Maybe you can implement this in your CSV Editor. A-Jay [EDIT: Damn good, ProgAndy... ;-)] Edited April 6, 2010 by ajag Rule #1: Always do a backup Rule #2: Always do a backup (backup of rule #1)
wraithdu Posted April 6, 2010 Posted April 6, 2010 @ProgAndy Nice indeed. @Funkey You'll also have to rework your listview to handle the possibility of multiline fields.
funkey Posted April 7, 2010 Author Posted April 7, 2010 (edited) Thanks wraithdu for the sample of the gmail contact file. And special thanks to progandy for his great functions! The function does not work correctly, for example wraithdu's string returns only one row for me. Also it is quite slow parsing big files and I have troubles embedding this to my CSV-Editor, maybe you can have a look at the actual beta file? Thx. I found nothing to create a multiline-listview. Everywhere there is the same answer to the question: Try the ownerdrawn style, but nowhere is an example how to do this. So I decided to show the item-text like this: "line1�line2�line3" Edited April 8, 2010 by funkey Programming today is a race between software engineers striving tobuild bigger and better idiot-proof programs, and the Universetrying to produce bigger and better idiots.So far, the Universe is winning.
ProgAndy Posted April 7, 2010 Posted April 7, 2010 (edited) If you take the current parser-functions, it works: Func _CSVReadToArray($sFile, $sSeparator = ";", $sQuote = '"') Return _ParseCSV($sFile, $sSeparator, $sQuote) EndFunc For me, the function seems to be quite fast. Edit: with v0.6, a CSV including quotes multiline requires 560 milliseconds to load incorrect, with my _ParseCSV, 200ms are required Edited April 7, 2010 by ProgAndy *GERMAN* [note: you are not allowed to remove author / modified info from my UDFs]My UDFs:[_SetImageBinaryToCtrl] [_TaskDialog] [AutoItObject] [Animated GIF (GDI+)] [ClipPut for Image] [FreeImage] [GDI32 UDFs] [GDIPlus Progressbar] [Hotkey-Selector] [Multiline Inputbox] [MySQL without ODBC] [RichEdit UDFs] [SpeechAPI Example] [WinHTTP]UDFs included in AutoIt: FTP_Ex (as FTPEx), _WinAPI_SetLayeredWindowAttributes
funkey Posted April 8, 2010 Author Posted April 8, 2010 Thank you very much progandy! Now your edited function works very well! I was able to integrate your function into my project! I modified it to fit to my project: expandcollapse popup; #FUNCTION# ==================================================================================================================== ; Name...........: _ParseCSV ; Description ...: Reads a CSV-file ; Syntax.........: _ParseCSV($sFile, $sDelimiters=',', $sQuote='"', $iFormat=0) ; Parameters ....: $sFile - File to read or string to parse ; $sDelimiters - [optional] Fieldseparators of CSV, mulitple are allowed (default: ,;) ; $sQuote - [optional] Character to quote strings (default: ") ; $iFormat - [optional] Encoding of the file (default: 0): ; |-1 - No file, plain data given ; |0 or 1 - automatic (ASCII) ; |2 - Unicode UTF16 Little Endian reading ; |3 - Unicode UTF16 Big Endian reading ; |4 or 5 - Unicode UTF8 reading ; $iAddIndex - [optional] Adds an index in first column ; $AddHeader - [optional] Adds an automatic header ("Col1", "Col2", ....) ; Return values .: Success - 2D-Array with CSV data (0-based) ; Failure - 0, sets @error to: ; |1 - could not open file ; |2 - error on parsing data ; |3 - wrong format chosen ; Author ........: ProgAndy ; Modified.......: funkey (to fit the function to the CSV-Editor) ; Remarks .......: ; Related .......: _WriteCSV ; Link ..........: ; Example .......: ; =============================================================================================================================== Func _ParseCSV($sFile, $sDelimiters = ',;', $sQuote = '"', $iFormat = 0, $iAddIndex = 0, $AddHeader = 0) Local Static $aEncoding[6] = [0, 0, 32, 64, 128, 256] If $iFormat < -1 Or $iFormat > 6 Then Return SetError(3,0,0) ElseIf $iFormat > -1 Then Local $hFile = FileOpen($sFile, $aEncoding[$iFormat]), $sLine, $aTemp, $aCSV[1], $iReserved, $iCount If @error Then Return SetError(1,@error,0) $sFile = FileRead($hFile) FileClose($hFile) EndIf If $sDelimiters = "" Or IsKeyword($sDelimiters) Then $sDelimiters = ',;' If $sQuote = "" Or IsKeyword($sQuote) Then $sQuote = '"' $sQuote = StringLeft($sQuote, 1) Local $srDelimiters = StringRegExpReplace($sDelimiters, '[\\\^\-\[\]]', '\\\0') Local $srQuote = StringRegExpReplace($sQuote, '[\\\^\-\[\]]', '\\\0') Local $sPattern = StringReplace(StringReplace('(?m)(?:^|[,])\h*(["](?:[^"]|["]{2})*["]|[^,\r\n]*)(\v+)?',',', $srDelimiters, 0, 1),'"', $srQuote, 0, 1) Local $aREgex = StringRegExp($sFile, $sPattern, 3) If @error Then Return SetError(2,@error,0) $sFile = '' ; save memory Local $iBound = UBound($aREgex), $iIndex = $AddHeader, $iSubBound = 1, $iSub = $iAddIndex ;changed Local $aResult[$iBound + $iAddIndex][$iSubBound] ;changed For $i = 0 To $iBound-1 If $iSub = $iSubBound Then $iSubBound += 1 ReDim $aResult[$iBound][$iSubBound] EndIf Select Case StringLen($aREgex[$i])<3 And StringInStr(@CRLF, $aREgex[$i]) ;new line found $iIndex += 1 $iSub = $iAddIndex ;changed ContinueLoop Case StringLeft(StringStripWS($aREgex[$i], 1),1) = $sQuote $aREgex[$i] = StringStripWS($aREgex[$i], 3) $aResult[$iIndex][$iSub] = StringReplace(StringMid($aREgex[$i], 2, StringLen($aREgex[$i])-2), $sQuote&$sQuote, $sQuote, 0, 1) Case Else $aResult[$iIndex][$iSub] = $aREgex[$i] EndSelect $aREgex[$i]=0 ; save memory $iSub += 1 If $iAddIndex Then $aResult[$iIndex][0] = $iIndex ;added Next If $iIndex = 0 Then $iIndex=1 ReDim $aResult[$iIndex][$iSubBound - 1] If $iAddIndex Then $aResult[0][0] = "Index" ;added If $AddHeader Then For $i = 1 To $iSubBound - 2 $aResult[0][$i] = "Col" & $i Next EndIf Return $aResult EndFunc For CSV files without quotes my old parsing is much faster so I made a checkbox to switch behavior. Programming today is a race between software engineers striving tobuild bigger and better idiot-proof programs, and the Universetrying to produce bigger and better idiots.So far, the Universe is winning.
funkey Posted April 8, 2010 Author Posted April 8, 2010 Version 0.7 released: ;Fixed bugs ;Added: Icon ;Added: Save file when exiting ;Added asterisk (star) to see if file has changed ;Added in context menu: row to clip ;Added in context menu: column to clip ;Added: Support full CSV format <-- thanks a lot to progandy!! ;Added: Enter @LF to item with Ctrl+{Enter}I hope that there are not to much bugs in it!Tell me if there are problems. TIA. Programming today is a race between software engineers striving tobuild bigger and better idiot-proof programs, and the Universetrying to produce bigger and better idiots.So far, the Universe is winning.
RagsRevenge Posted April 15, 2010 Posted April 15, 2010 Version 0.7 released: ;Fixed bugs ;Added: Icon ;Added: Save file when exiting ;Added asterisk (star) to see if file has changed ;Added in context menu: row to clip ;Added in context menu: column to clip ;Added: Support full CSV format <-- thanks a lot to progandy!! ;Added: Enter @LF to item with Ctrl+{Enter}I hope that there are not to much bugs in it!Tell me if there are problems. TIA.Wow. I had just started writing something like this. I hope to have a good look at yours over the next week or 2.D
funkey Posted April 23, 2010 Author Posted April 23, 2010 Thx RagsRevenge. Version 0.8 released! ;Added: Cut, copy and paste rows ;Added: Renew index after changing row(s) ;Added: Accelerators ;Added: Search and replace strings ;Added: Hide toolbar if you want ;Added: Buttons for row up / row down ;Added: Create new file Thanks for testing and thanks for comments! Programming today is a race between software engineers striving tobuild bigger and better idiot-proof programs, and the Universetrying to produce bigger and better idiots.So far, the Universe is winning.
jayterry Posted August 22, 2010 Posted August 22, 2010 Thanks guys. I found this code to parse a CSV just when I needed it. Actually, what I really needed was something that worked just like StringSplit, except it actually understood CSV format. So I took all the smart parsing code from _ParseCSV and boiled it down to a simpler form. It doesn't work on a file, just on a string you pass to it. That way I can use it on any string I want, or feed it only the lines I select from a file. Also, it only returns a 1 dimensional array, with the first element ($array[0]) containing the number of elements found - just like StringSplit returns. This makes it almost a "drop-in" replacement for StringSplit. Thought I'd share in case it might be useful to anyone else. Although most of the credit goes to ProgAndy for coming up with that cool parsing code! P.S.: I don't know what's going on with the indentation on the code I pasted in below. There must be something I'm just not getting about how to insert code into a forum message. expandcollapse popup; #FUNCTION# =================================================================== ; Name...........: _StringSplitCSV ; Description ...: Parses a CSV formatted string ; Syntax.........: _StringSplitCSV($sString, $sDelims=',', $sQuote='"') ; Parameters ....: $sString - String to parse ; $sDelims - [optional] Field delimiters of CSV ; |mulitple characters are allowed (default: ,;) ; $sQuote - [optional] Char to quote strings (default: ") ; Return values .: Success - 1D Array with CSV data ; |1-based with count in [0] (like StringSplit) ; Failure - 0, sets @error to: ; |1 - error on parsing data ; Author ........: ProgAndy, Jay Terry ; Modified.......: ; Remarks .......: ; Related .......: ; Link ..........: ; Example .......: ; ; Notes: ; The complex part of this function, the regex handling, by ProgAndy. ; Minor modifications by Jay Terry. ; ToDo: ; ============================================================================== Func _StringSplitCSV($sString, $sDelims=',;', $sQuote='"') If $sDelims = "" Or IsKeyword($sDelims) Then $sDelims = ',;' If $sQuote = "" Or IsKeyword($sQuote) Then $sQuote = '"' $sQuote = StringLeft($sQuote, 1) Local $srDelims = StringRegExpReplace($sDelims,'[\\\^\-\[\]]', '\\\0') Local $srQuote = StringRegExpReplace($sQuote,'[\\\^\-\[\]]', '\\\0') Local $sPattern = StringReplace( _ StringReplace( _ '(?m)(?:^|[,])\h*(["](?:[^"]|["]{2})*["]|[^,\r\n]*)(\v+)?', _ ',',$srDelims,0,1),'"',$srQuote,0,1) Local $aRegex = StringRegExp($sString,$sPattern,3) If @error Then Return SetError(1,@error,0) _ArrayInsert($aRegex,0,UBound($aRegex)) Return $aRegex EndFunc ;==>_StringSplitCSV
taietel Posted October 4, 2010 Posted October 4, 2010 I know it is a bit old, but I use it and I have some functions that can improve the functionality (I didn't start another thread because funkey, ProgAndy, wraithdu etc did a great job on this one): - there is no need to enter the delimiter used in CSV (at least with the files I have used so far) - filtering by column or by value etc. expandcollapse popup#include <File.au3> #include <Array.au3> ; ; Some functions for working with CSV files... ; after using funkey's CSV Editor (http://www.autoitscript.com/forum/index.php?showtopic=110463&view=findpost&p=775767) ; SOME NOTES: ; - you don't need to specify the delimiter; ; - if you use them, comment/delete _ArrayDisplay(...) from the function; this was only for testing and for some examples ; - array returned is array[row][col], with row and col starting at 1, not zero ; - any sugestions/critics are welcome! ; - make changes to fit your needs ; #cs ;================ EXAMPLES: ============== $samplecsv = @ScriptDir&"\sample.csv" ;find the delimiter: ConsoleWrite(_CSV_FindDelimiter($samplecsv)&@CRLF) ;filter CSV file by a given value: _CSV_FilterByValue($samplecsv,"someword") ;filter CSV file by a given value from a given column: _CSV_FilterByColumn($samplecsv,4,"blabla") ;convert CSV to HTML: _CSV_ToHTML($samplecsv) ;read CSV file into an array: _CSV_ToArray($samplecsv) ;another example of _CSV_ToArray Example_CSV2Array() Func Example_CSV2Array() $a = _CSV_ToArray(@ScriptDir&"\spspv.csv") $sOut = "Rows: "&@TAB&$a[0][0]&@CRLF $sOut &= "Columns:"&@TAB&$a[0][1]&@CRLF $sOut &= "Row 2: " For $i=1 To $a[0][1] $sOut &= @TAB&$a[2][$i] Next $sOut &= @CRLF $sOut &= "Value at [Row1][Col3]="&$a[1][3]&@CRLF MsgBox(0,"_CSV_ToArray",$sOut) EndFunc ;================ END EXAMPLES ============== #ce #Region FUNCTIONS Func _CSV_FilterByValue($sFile,$sValue) Local $a = _CSV_ToArray($sFile) Local $arTemp[$a[0][0]][$a[0][1]] Local $k=1 For $m=1 To $a[0][1]-1 $arTemp[1][$m]=$a[1][$m] Next For $i=1 To $a[0][0] For $j=1 To $a[0][1]-1 If StringInStr($a[$i][$j],StringUpper($sValue))<>0 Then $k += 1 For $l=1 To $a[0][1]-1 $arTemp[$k][$l]=$a[$i][$l] Next EndIf Next Next $arTemp[0][0]=$k _ArrayDisplay($arTemp);just for testing Return $arTemp EndFunc ; #FUNCTION# ==================================================================================================== ; Name...........: _CSV_FilterByColumn ; Description....: Filter records in a CSV file, based on a value from a column ; Syntax.........: _CSV_FilterByColumn($sFile,$iColumn,$sValue) ; Parameters.....: $sFile - Path and filename of the file ; $iColumn - ; $sValue - ; Return values..: Success - filtered array ; Failure - ; Author.........: Mihai Iancu (taietel@yahoo.com) ; =============================================================================================================== Func _CSV_FilterByColumn($sFile,$iColumn,$sValue) Local $a = _CSV_ToArray($sFile) Local $arTemp[$a[0][0]][$a[0][1]] Local $k=1 For $m=1 To $a[0][1]-1 $arTemp[1][$m]=$a[1][$m] Next For $i=1 To $a[0][0] If StringInStr($a[$i][$iColumn], StringUpper($sValue))<>0 Then $k += 1 For $j=1 To $a[0][1]-1 $arTemp[$k][$j]=$a[$i][$j] Next EndIf Next $arTemp[0][0]=$k _ArrayDisplay($arTemp);just for testing Return $arTemp EndFunc ; #FUNCTION# ==================================================================================================== ; Name...........: _CSV_FindDelimiter ; Description....: Finds the delimiter in a CSV file ; Syntax.........: _CSV_FindDelimiter($sFile) ; Parameters.....: $sFile - Path and filename of the file ; ; Return values..: Success - Delimiter used ; Failure - 0, sets @error to: ; |1 - could not open file ; Author.........: Mihai Iancu (taietel@yahoo.com) ; =============================================================================================================== Func _CSV_FindDelimiter($sFile) Local $sDelimiter = StringSplit(@TAB&"m|m;m,;m,m"&"'m"&'"',"m",2) Local $hFile = FileOpen($sFile) If $hFile=-1 Then Return SetError(1,@error,0) For $i=0 To UBound($sDelimiter)-1 If StringInStr(FileReadLine($hFile,1),$sDelimiter[$i])<>0 And StringInStr(FileReadLine($hFile,2),$sDelimiter[$i])<>0 Then Return $sDelimiter[$i] ExitLoop EndIf Next FileClose($hFile) EndFunc ; #FUNCTION# ==================================================================================================== ; Name...........: _CSV_ToArray ; Description....: ; Syntax.........: _CSV_ToArray($sFile) ; Parameters.....: $sFile - Path and filename of the file ; ; Return values..: Success - $aRecord[$iRows][$iColumn] ; |$aRecord[0][0] = number of rows ; |$aRecord[0][1] = number of columns ; |$aRecord[X][Y] = coresponding value at row X and column Y ; ; Failure - 0, sets @error to: ; |1 - could not parse CSV ; ; Author.........: M. Iancu (taietel@yahoo.com) ; =============================================================================================================== Func _CSV_ToArray($sFile) Local $aTemp, $sDelim = _CSV_FindDelimiter($sFile) If Not _FileReadToArray($sFile,$aTemp) Then Return SetError(1,@error,0) Exit Else Local $aCols = StringSplit($aTemp[1],$sDelim) Local $aRecord[$aTemp[0]+1][$aCols[0]+1] $aRecord[0][0]= $aTemp[0] $aRecord[0][1]= $aCols[0] For $i=1 To $aTemp[0] Local $iCols = StringSplit($aTemp[$i],$sDelim) For $j=1 To $iCols[0] $aRecord[$i][$j] = $iCols[$j] Next Next EndIf _ArrayDisplay($aRecord);just for testing Return $aRecord EndFunc ; #FUNCTION# ==================================================================================================== ; Name...........: _CSV_ToHTML ; Description....: Converts CSV into HTML with the same name, in the scriptdir ; Syntax.........: _CSV_ToHTML($sFile) ; Parameters.....: $sFile - Path and filename of the file ; $sOutputFile - [Optional] Default is the name of the CSV file ; ; Return values..: Success - @ScriptDir\csv_name.htm ; Failure - 1 ; Author.........: Mihai Iancu (taietel@yahoo.com) ; =============================================================================================================== Func _CSV_ToHTML($sFile, $sOutputFile="",$sTitle="") If $sTitle = "" Then $sTitle = "CSV REPORT: "&$sFile Local $aTemp, $sDelim = _CSV_FindDelimiter($sFile) If Not _FileReadToArray($sFile,$aTemp) Then Exit(1) Else Local $aCols = StringSplit($aTemp[1],$sDelim) Local $aRecord[$aTemp[0]+1][$aCols[0]+1] $aRecord[0][0]= $aTemp[0] $aRecord[0][1]= $aCols[0] For $i=1 To $aTemp[0] Local $iCols = StringSplit($aTemp[$i],$sDelim) For $j=1 To $iCols[0] $aRecord[$i][$j] = $iCols[$j] Next Next EndIf Local $sHTML = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">' & @CRLF $sHTML &='<html>'&@CRLF&' <head>'&@CRLF $sHTML &=' <title>'&$sTitle&'</title>'&@CRLF $sHTML &=' <style type="text/css">'&@CRLF $sHTML &=' #main { font-family:"Times New Roman", Arial, Helvetica, sans-serif; font-size:11px; border-collapse:collapse; background-color:#fffff1;}'&@CRLF $sHTML &=' #main td, #main th { font-size:11px; border:1px solid #222222; padding:2px 4px 2px 4px;}'&@CRLF $sHTML &=' #main th {font-size:1.1em; text-align:left; padding-top:5px; padding-bottom:4px; background-color:#cccccc; color:#444444; }'&@CRLF $sHTML &=' #main tr.alt td { color:#000000; background-color:#EAF2D3; }'&@CRLF $sHTML &=' </style>'&@CRLF $sHTML &=' </head>'&@CRLF $sHTML &=' <body style="font-size:11px; color:#000; background-color:#FEFEFE; padding:4px;">'&@CRLF&' <h2><center>'&$sTitle&'</center></h2>'&@CRLF $sHTML &=' <table id="main" width=100%>'&@CRLF $sHTML &=' <tr>'&@CRLF For $i=1 To $iCols[0] $sHTML &= ' <th><center>'&$aRecord[1][$i]&'</center></th>'&@CRLF Next $sHTML &=' </tr>'&@CRLF For $i = 2 To $aTemp[0] Switch Mod($i,2) Case 0 $sHTML &= ' <tr>'&@CRLF For $j=1 To $iCols[0] $sHTML &= ' <td>'&$aRecord[$i][$j]&'</td>'&@CRLF Next $sHTML &= ' </tr>'&@CRLF Case Else $sHTML &= ' <tr class="alt">'&@CRLF For $j=1 To $iCols[0] $sHTML &= ' <td>'&$aRecord[$i][$j]&'</td>'&@CRLF Next $sHTML &= ' </tr>'&@CRLF EndSwitch Next $sHTML &=' </table>'&@CRLF $sHTML &= 'Creeat azi: ' & StringFormat("%s.%s.%s la %s:%s:%s", @MDAY, @MON, @YEAR, @HOUR, @MIN, @SEC) & ' de <a href="http://sites.google.com/site/cmiancu/" target="_blank" style="color:#003D71; text-decoration:none;">Mihai Iancu</a>' & @CRLF $sHTML &= ' </body>'&@CRLF&'</html>'&@CRLF If $sOutputFile = "" Then $sOutputFile = StringTrimRight($sFile,3) & "htm" Local $hOutFile = FileOpen($sOutputFile,2) FileWrite($hOutFile,$sHTML) FileClose($hOutFile) ShellExecute($sOutputFile);just for testing EndFunc #EndRegion M.I. Things you should know first...In the beginning there was only ONE! And zero... Progs: Create PDF(TXT2PDF,IMG2PDF) 3D Bar Graph DeskGadget Menu INI Photo Mosaic 3D Text
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