25 replies to this topic

[funkey]

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 Colour of the listview
;WinSetOnTop
;Drop files in listview to load
;Resizing
;HTML-Export

Version 0.5: March 04, 2010
;Show the index if you want
;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 @LF 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

Attached Files

•  CSV-Editor.ico   11.73K   806 downloads
•  CSV-Editor V0.8.au3   68.63K   1521 downloads

Edited by funkey, 23 April 2010 - 06:52 PM.

[ReFran]

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]

Update to Version 0.4: February 25, 2010

;Changed the Colour of the listview
;WinSetOnTop
;Drop files in listview to load
;Resizing
;HTML-Export

[funkey]

Version 0.5: March 04, 2010
;Show the index if you want
;Listview with $GUI_BKCOLOR_LV_ALTERNATE

[funkey]

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

[funkey]

Added icon.
No more comments?

[wraithdu]

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 by wraithdu, 05 April 2010 - 05:13 PM.

[funkey]

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

[wraithdu]

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 by wraithdu, 06 April 2010 - 03:50 PM.

[ProgAndy]

I tried to create some CSV functions. All files I checked were parsed wihtout errors:

AutoIt        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 by ProgAndy, 07 April 2010 - 05:01 PM.

[ajag]

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 by ajag, 06 April 2010 - 07:02 PM.

[wraithdu]

@ProgAndy
Nice indeed.

@Funkey
You'll also have to rework your listview to handle the possibility of multiline fields.

[funkey]

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 by funkey, 08 April 2010 - 01:05 PM.

[ProgAndy]

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 by ProgAndy, 07 April 2010 - 06:15 PM.

[funkey]

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:

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

[funkey]

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.

[RagsRevenge]

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]

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!

[jayterry]

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.

Plain Text        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]

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.

AutoIt        expandcollapse  popup

#include <File.au3>
#include <Array.au3>
;
; Some functions for working with CSV files...
; after using funkey's CSV Editor (<a href='http://www.autoitscript.com/forum/index.php?showtopic=110463&view=findpost&p=775767' class='bbc_url' title=''>http://www.autoitscript.com/forum/index.php?showtopic=110463&view=findpost&p=775767</a>)
; 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" "<a href='http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd%22>' class='bbc_url' title='External link' rel='nofollow external'>http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">'</a> & @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="<a href='http://sites.google.com/site/cmiancu/' class='bbc_url' title='External link' rel='nofollow external'>http://sites.google.com/site/cmiancu/"</a> 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.