Jump to content
pixelsearch

CSV file editor

Recommended Posts

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Posted (edited)

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

1319175609_arraymultisort.png.3de1133fa7dfad2841f35ceb84425902.png

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) :mellow:

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 by pixelsearch

Share this post


Link to post
Share on other sites

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

1077186315_ExcellimitedTextqualifier.png.f929fe1c47b80d76fef652493f58e510.png

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 :thumbsup:

 

Share this post


Link to post
Share on other sites

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.


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Posted (edited)
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 by pixelsearch
deleted the code as it's found now in version 901u

Share this post


Link to post
Share on other sites
Posted (edited)

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 :)

1195735143_901u-3sorts.png.5d48a679131e403bdccd3019d5b4614c.png

Download link at the end of 1st post

Edited by pixelsearch

Share this post


Link to post
Share on other sites

Version 901w (Jan 10, 2020)

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

989940703_901w-closefile.png.17e88b24eba5bdd399068a325e5a0f95.png

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

Download link at the end of 1st post

Edited by pixelsearch

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...