CSV Viewer

[llewxam]

I could swear that I saw someone else had a CSV viewer script posted but no amount of searching found it. If someone knows of one that has been done before, please post a link to it.

I write many scripts that save information to CSV files, but got tired of using Excel to view them because of having to resize the columns to view all of the information. Yes there are ways of resizing the columns, but I decided to write a viewer to make life a little easier.

There are some configurable options, such as font used, font size, weight, and underlining, as well as optional automatic column width resizing thanks to Melba23's StringSize UDF, now built-in rather than having to download the UDF.

Please be aware that when using the resize option it will take about twice as long to run the script. For a fairly sane file it is not at all a big deal, but feed it an 8MB file with 130 columns and 11,500 rows (about 1.5 million elements) and it takes quite a long time!! This script also takes a lot of RAM to run, the 8MB file I used above took over 200MB of RAM.

I'd like to see other ideas, so please post 'em if you got 'em.

Enjoy

Ian

EDIT: Updated code

CSV Viewer.zip

Edited November 28, 2012 by llewxam

[UEZ]

I could swear that I saw someone else had a CSV viewer script posted but no amount of searching found it. If someone knows of one that has been done before, please post a link to it.

Here:

Br,

UEZ

[water]

I use UDF written by progandy to split a CSV into an array.

To display the array I simply use _ArrayDisplay. No resizing etc. and I don't know how it works with large files but it might give you an idea.

[czardas]

It would be better if you could resize the GUI. I also miss the option to open a different document.

[llewxam]

@UEZ:

Thanks for that, but on my torture test 8MB file he had an array overflow, so will get in touch with him. Also, his defaults to semicolon delimiter, odd since the point is a C-SV, not an S-SV Still, I understand the point of him allowing a selectable delimiter and it is a cool idea......

@water:

The splitting is the easy part, no need for anything beyond StringSplt as far as I can work out, but will look at that UDF anyway to see what it has going on. Thanks.

@czardas:

I was contemplating a resizable GUI but have never done that yet, so decided to not try now. Maybe a re-write of this would be a good time to try. As for opening a new file from the GUI, also a good idea so that will also go in to the "next rewrite" box.

Ian

[UEZ]

CSV / SSV: this is historical to middle europe because the default delimiter here is ; and not , but it can be changed easily.

Br,

UEZ

[llewxam]

Interesting, did not know that. Perhaps I should add that as an option then!

Ian

[czardas]

Hmm, array overflow. I fear suffer the same issues. I haven't really tested the limits. I think it will handle several million fields though. It might be slow to test it (better to calculate it).

The format csv has several rules about enclosing the delimeter (commas) within fields. A simple stringsplit will not work on many csv files because of this.

[llewxam]

ProgAndy's failed the 8MB test, the array got too big, but yours passed the 8MB test, and the super-test 24MB that was the original 8MB copied 3 times. Nice job! I will look in to incorporating it.

Thanks!

Ian

[czardas]

I think ProgAndy's code looks quite sophisticated compaired to mine. I don't really understand why it would not handle the same amount of data. It has encoding options which I think I ought to try. Anyway I look forward to seeing your next release. Any problems with the UDF, just ask.

[AZJIO]

Add error handling

$File = FileOpenDialog("Select a CSV File", @DesktopDir, "CSV Files (*.csv)")
If @error Then Exit

Example

#include <ListViewConstants.au3>
$sText= _
'foo,bar,555,"foo,bar",1,2,3' & @CRLF & _
' ;),test,098765,"o),yes","4,3",5,6'
; MsgBox(0, '?', $sText)

$sep = Chr(1)
$sText=StringRegExpReplace($sText, ',(?=(?:[^"]*"[^"]*")*(?![^"]*"))', $sep)

$hGui = GUICreate('My Program', 550, 260)

$aLine=StringSplit($sText, @CRLF, 1)
$iCount = StringSplit($aLine[1], $sep)
$sHeader = ''
For $i = 1 To $iCount[0]
    $sHeader &= $i & '|'
Next
$sHeader = StringTrimRight($sHeader, 1)
$iListView = GUICtrlCreateListView($sHeader, 0, 0, 550, 260 - 30)

$sOptSepDef = Opt('GUIDataSeparatorChar', $sep)
For $i = 1 To $aLine[0]
    GUICtrlCreateListViewItem($aLine[$i], $iListView)
Next
Opt('GUIDataSeparatorChar', $sOptSepDef)
For $i = 0 To $iCount[0]
    GUICtrlSendMsg($iListView, $LVM_SETCOLUMNWIDTH, $i, -1)
    GUICtrlSendMsg($iListView, $LVM_SETCOLUMNWIDTH, $i, -2)
Next
GUISetState()
Do
Until GUIGetMsg() = -3

Edited February 14, 2013 by AZJIO

[llewxam]

You always come up with really nice looking code AZJIO, and your example works well on consistent CSVs, but it couldn't properly display on my mini-test 69 row 3 column CSV because the first line is only 2 columns, but there are some rows that have 3 though. Your script assumes consistency with the way $iCount is determined. With my mid-sized test it wouldn't finish at all, very strange error code came up, 1073741571.

It is very impressive though how small the code is!

Ian

[ProgAndy]

I think ProgAndy's code looks quite sophisticated compaired to mine. I don't really understand why it would not handle the same amount of data. It has encoding options which I think I ought to try. Anyway I look forward to seeing your next release. Any problems with the UDF, just ask.

I know why my UDF does not work with your file. First, it creates an array which can hold all available fields as single records, afterwards it adds a column if required and does not reduce allocated space in the first dimension. This is done only at the very end.

To fix that, you'll have to cut down the record count of the array when adding an additional column which forces some more error checking in return.

Edit: improved wording. I have a fixed version and currently am running some tests with openURL routing data.

Edited October 13, 2012 by ProgAndy

[czardas]

I know why my UDF does not work with your file. First, it creates an array which can hold all available elements as single entries, afterwards it adds a column if required and does not allocated space in the first dimension. This is done only at the very end.

To fix that, you'll have to cut down the row count of the array when adding an additional column which forces some more error checking in return.

I get the picture, but I'm still a bit fuzzy on the details. I'll look at it more closely, thanks!

[llewxam]

First post updated, now using czardas' UDF as well as copied Melba23's StringSize UDF in to the script for ease of use. Thanks all for the suggestions, more revisions may come.

Ian

[czardas]

You seem to have implemented CSVSplit without any problem. I like the colour scheme and the splash screen when loading the file. As mentioned earlier, there are several features you might want to add. Still I think it's a good start. Well done!

[llewxam]

First post updated, probably the last update as far as new features go, but then again, when is a script ever "done"?????

Revamped GUI which looks much better and allows all options to be configured from the GUI, rather than through the code. Much more likely to be useful now because of this!

Any issues please let me know, otherwise enjoy.

Ian

[czardas]

It's much better now you have added the menu. It's true that you can add no end of features. I have an unfinished program similar to this. One of the most useful features I have found (with my program) is being able to sort by clicking on a column header - just another thought.

Edited October 14, 2012 by czardas

[llewxam]

doooohhh, don't give me ideas, then it will NEVER be finished!!! hahahahaha

[UEZ]

Here some more ideas to implement:

• add 1st row of csv to column header
• different line color (odd / even)
• resizeable window
• sort option when clicked on a column header
• search functionality

Br,

UEZ