Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

CSV-Editor V0.8


  • Please log in to reply
25 replies to this topic

#1 funkey

funkey

    New Dad

  • Active Members
  • PipPipPipPipPipPip
  • 652 posts

Posted 21 February 2010 - 02:46 PM

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

Attached Files


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

Programming today is a race between software engineers striving to

build bigger and better idiot-proof programs, and the Universe

trying to produce bigger and better idiots.

So far, the Universe is winning.








#2 ReFran

ReFran

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 554 posts

Posted 21 February 2010 - 06:35 PM

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

#3 funkey

funkey

    New Dad

  • Active Members
  • PipPipPipPipPipPip
  • 652 posts

Posted 25 February 2010 - 06:28 AM

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 to

build bigger and better idiot-proof programs, and the Universe

trying to produce bigger and better idiots.

So far, the Universe is winning.


#4 funkey

funkey

    New Dad

  • Active Members
  • PipPipPipPipPipPip
  • 652 posts

Posted 07 March 2010 - 10:10 AM

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 to

build bigger and better idiot-proof programs, and the Universe

trying to produce bigger and better idiots.

So far, the Universe is winning.


#5 funkey

funkey

    New Dad

  • Active Members
  • PipPipPipPipPipPip
  • 652 posts

Posted 03 April 2010 - 05:44 PM

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 to

build bigger and better idiot-proof programs, and the Universe

trying to produce bigger and better idiots.

So far, the Universe is winning.


#6 funkey

funkey

    New Dad

  • Active Members
  • PipPipPipPipPipPip
  • 652 posts

Posted 05 April 2010 - 04:06 PM

Added icon.
No more comments?Posted Image

Programming today is a race between software engineers striving to

build bigger and better idiot-proof programs, and the Universe

trying to produce bigger and better idiots.

So far, the Universe is winning.


#7 wraithdu

wraithdu

    this noise inside my head

  • MVPs
  • 2,412 posts

Posted 05 April 2010 - 05:10 PM

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.


#8 funkey

funkey

    New Dad

  • Active Members
  • PipPipPipPipPipPip
  • 652 posts

Posted 06 April 2010 - 01:52 AM

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 to

build bigger and better idiot-proof programs, and the Universe

trying to produce bigger and better idiots.

So far, the Universe is winning.


#9 wraithdu

wraithdu

    this noise inside my head

  • MVPs
  • 2,412 posts

Posted 06 April 2010 - 03:49 PM

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.


#10 ProgAndy

ProgAndy

    You need AutoItObject

  • MVPs
  • 2,508 posts

Posted 06 April 2010 - 06:56 PM

I tried to create some CSV functions. All files I checked were parsed wihtout errors:
AutoIt         
; #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.

*GERMAN* Posted Image [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

#11 ajag

ajag

    Wayfarer

  • Active Members
  • Pip
  • 95 posts

Posted 06 April 2010 - 06:59 PM

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.

Rule #1: Always do a backupRule #2: Always do a backup (backup of rule #1)

#12 wraithdu

wraithdu

    this noise inside my head

  • MVPs
  • 2,412 posts

Posted 06 April 2010 - 08:24 PM

@ProgAndy
Nice indeed.

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

#13 funkey

funkey

    New Dad

  • Active Members
  • PipPipPipPipPipPip
  • 652 posts

Posted 07 April 2010 - 05:47 PM

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.

Programming today is a race between software engineers striving to

build bigger and better idiot-proof programs, and the Universe

trying to produce bigger and better idiots.

So far, the Universe is winning.


#14 ProgAndy

ProgAndy

    You need AutoItObject

  • MVPs
  • 2,508 posts

Posted 07 April 2010 - 06:04 PM

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.

*GERMAN* Posted Image [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

#15 funkey

funkey

    New Dad

  • Active Members
  • PipPipPipPipPipPip
  • 652 posts

Posted 08 April 2010 - 08:35 AM

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         
; #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 to

build bigger and better idiot-proof programs, and the Universe

trying to produce bigger and better idiots.

So far, the Universe is winning.


#16 funkey

funkey

    New Dad

  • Active Members
  • PipPipPipPipPipPip
  • 652 posts

Posted 08 April 2010 - 02:43 PM

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 to

build bigger and better idiot-proof programs, and the Universe

trying to produce bigger and better idiots.

So far, the Universe is winning.


#17 RagsRevenge

RagsRevenge

    Wayfarer

  • Active Members
  • Pip
  • 89 posts

Posted 15 April 2010 - 08:15 AM

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

#18 funkey

funkey

    New Dad

  • Active Members
  • PipPipPipPipPipPip
  • 652 posts

Posted 23 April 2010 - 06:54 PM

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 to

build bigger and better idiot-proof programs, and the Universe

trying to produce bigger and better idiots.

So far, the Universe is winning.


#19 jayterry

jayterry

    Seeker

  • Active Members
  • 17 posts

Posted 22 August 2010 - 02:59 PM

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         
; #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


#20 taietel

taietel

    I'm the third from the left...

  • Active Members
  • PipPipPipPipPipPip
  • 726 posts

Posted 04 October 2010 - 05:50 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users