CSV file editor

[jchd]

One more suggestion: add a "natural sort" feature. This is the sort Windows uses for listing entries in Explorer. Every numeric part of an entry is collated numerically, but text parts are collated lexicographically, until end of string entry.

I believe there is already threads here about natural sort.

[pixelsearch]

@jchd : thanks for your input
Is this the kind of sort you are talking about ?

This image is taken from another thread where we talked about it and I explained how slow it would be (10 times slower than numeric sort or string sort)

It would require to use GUICtrlListView_RegisterSortCallBack() and its 2nd parameter which is very slow for sorting when equal to 2 (Use Windows API StrCmpLogical : better for "x1y" < "x10y")

Actually, in the script, I use this 2nd parameter for sorting (0 = String Compare, 1 = Numeric compare) ... slowliness starts when this parameter equals 2

Edited January 4, 2020 by pixelsearch

[pixelsearch]

Another idea to work on : the Text qualifier character (a character that encloses values in a text file)
In the script, double quotes (without other choice) are used as text qualifier, i.e  chr(34) => "

Excel would allow another alternative (the simple quote) as shown in the pic below

Apparently it's not enough. Look at the problem described in 2019 by this guy, in an Excel forum. Though his csv file is comma delimited, its text qualifier is a tilde ~ and he can't import his file in Excel :

~PartyID~,~PartyTypeID~,~PartyTitle~,~PartyLastName~,~PartyFirstName~,~PartyMiddleName~,~PartyMiddleName2~,~PartyNickName~,~PartySuffix~,~PartyMaidenName~,~PartyAlternateLastName~,~PartyDegree~,~PartyGender~,~PartyBirthDate~,~PartyDeathDate~,~UpdatePartyNameVariations~,~PartyNameVariations~
~1524~,~1~,~~,~Abbott~,~George~,~Knapp~,~~,~~,~~,~~,~~,~~,~M~,~~,~~,~True~,~G. Abbott, G. K. Abbott, G. Knapp Abbott, George Abbott, George K. Abbott, George Knapp Abbott~
~1525~,~1~,~~,~Aldrich~,~Jotham~,~M~,~~,~~,~~,~~,~~,~~,~M~,~~,~~,~True~,~J. Aldrich, J. M. Aldrich, Jotham Aldrich, Jotham M. Aldrich~

Here is his final wish to Excel programmers, in his own words :
"Please allow a user to choose a TEXT QUALIFIER of their liking!"

If a personalized text qualifier should appear one day in the script, then Czardas initial import function _CSVSplit() would need to be reworked, as he didn't give us a choice to choose a text qualifier. He used the double quote, no other choice. Other parts of the script would probably need some rework too.

Guinness gave a choice in his export function _GUICtrlListView_SaveCSV.au3, where the text qualifier is a parameter, bravo

 

[jchd]

I'm well aware natural sort is slow if implemented in Pure AutoIt.  Do to the usefulness of your program, maybe a machine code guru could come up with an elegant solution to make it useable.

[pixelsearch]

On 1/4/2020 at 9:28 PM, jchd said:

an elegant solution...

@jchd : not sure if the solution I found during the night was elegant or not, but the "natural sort" applied to the "Street" column (pic in a precedent post above) seems to work fine now, its speed is at least 6 or 7 times faster than option 2 of GUICtrlListView_RegisterSortCallBack (which uses StrCmpLogicalW)

It is fast because I use the same buffer & pointers for reading & writing the whole listview (using Melba23's ArrayMultiColSort after having transferred the content of the listview in an array)

But I don't know if the computer memory is stable after the rewriting of the listview because I changed the buffer size (found in _GUICtrlListView_SetItemText) from StringLen($sText) + 1 ... to 4096 (same size than the buffer size in _GUICtrlListView_GetItemText)

Maybe there is an automatic null character added by Windows at the end of each string that saves the whole process. The goal was to prevent the creation of a buffer each time a cell is read or written, which means not using the functions _GUICtrlListView_GetItemText() and _GUICtrlListView_SetItemText() in their original form.

Jpm wrote here that I "optimized carefully the use of _GUICtrlListView_GetItemText() when used in loops"
Not sure he would say same concerning my changes in _GUICtrlListView_SetItemText() , time will tell...

Edited January 6, 2020 by pixelsearch
deleted the code as it's found now in version 901u

[Musashi]

I am almost sure that @pixelsearch has already found / read this thread . If not, it might be worth a closer look.

natural-order-string-comparison

[pixelsearch]

@Musashi: thanks for the link
I did know this thread, you guessed well

Edited January 5, 2020 by pixelsearch

[pixelsearch]

Version 901u (Jan 6, 2020)

1 functionality added : Natural sort. Thanks to jchd for the idea and Melba23 for his function ArrayMultiColSort() included in the script.

Czardas read this thread a couple of days ago and that's great, I hope he liked the way his CSV splitting function was reused

Download link at the end of 1st post

Edited January 6, 2020 by pixelsearch

[pixelsearch]

Version 901w (Jan 10, 2020)

Two functionalities added :
1) Close File button, which allows to import other csv file(s) during the same session

2) Import speed has been improved (and probably reached his maximum with this version)

Download link at the end of 1st post

Edited January 10, 2020 by pixelsearch

[pixelsearch]

Version 901x (Jan 14, 2020)

One minor functionality added : number of rows is now displayed just under the listview, it may be handy sometimes.

Other minor changes included (natural sort speed improved) 

Download link at the end of 1st post

[Djey51]

Hello @pixelsearch,

Congratulation for this very nice development.

I'd like to discuss some point with you.

1- I have case where at the beginning of my csv file there is somes lines to introduce the data base. So, is it possible to declare an other row than the first one as the headers ?

2- Is it possible to make somes column unchangeable (i mine that it is not possible to change the values of this column)

3- Is it possible to hide some column by default ?

4- I have csv in UTF8-BOM. When i export CSV it is saved in UTF8 : is it possible to choose the export fotmat to UTF8-Bom ?

5- When we display the window in full screen, the GUI header is stretched : is it possible to fix it in heigth direction to a constant value ?

 

That' all, for the moment 🙄.

Thanks for your answers and have a nice day !!

Djey51

[HJL]

 

Hello @pixelsearch,

this is a wonderful script! Unfortunately I am far from understanding how it works.
Is it possible to display only 2-3 specific columns instead of the whole file? I would need column 1 for the labels and another column xx to change the values.

Thanks for your help.

[pixelsearch]

Hi HJL
Actually the script will display all columns of the file but remember, you can drag the column headers to any place you wish.

For example, you could drag the "labels" column to the very left, then drag another already existing column next to it and start your chained vertical modifications in that 2nd column (the "Chained Edit : Vertically" radio button could be a big help in your case)

If you prefer to insert a blank column next to the labels column, you can do it by right-clicking the column header #2 and insert a blank column there. When you'll save your modifications, the saved file will be saved with the new columns order (the original file isn't overwritten unless you want it to)

Good luck