Sorting Rows of 8 Columns (Comma or Tab Separated) Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
carno
Posts: 195
Joined: 20 Jun 2014, 16:48

Sorting Rows of 8 Columns (Comma or Tab Separated)

I have borrow the following code from an archive (https://autohotkey.com/board/topic/3710 ... ng-script/).

It sorts column B (2nd from left) in this example. However, I would like it to do a descending sort on the last column, which is column H (8th from left). Can anyone figure this out?

Code: Select all

``````csv =
(
Prospect Capital Corporat,74348TAQ5,141,04/15/20,99.63,5.040,BBB,4.92
Whiting Pete Corp New,966387AL6,25,04/01/20,95.75,4.692,BB,3.88
E M C Corp Mass,568648AQ5,250,06/01/20,97.49,4.456,BB-,4.03
Ford Motor Credit Co Llc,345397XF5,375,03/27/20,97.74,4.297,BBB,3.87
)

FileDelete %A_Temp%\csvin.txt
FileAppend, %csv%, %A_Temp%\csvin.txt

; * * * now read and sort the file * * *
/*
SubStr(String, StartingPos [, Length])
InStr(Haystack, Needle [, CaseSensitive = false, StartingPos = 1])
*/
Loop, parse, csv, `n
{
field1 := SubStr(A_LoopField, 1, InStr(A_LoopField,",")-1)
remainder := SubStr(A_LoopField, InStr(A_LoopField,",")+1)
temp .= remainder ","field1 "`n"
}

Sort, temp
Loop, parse, temp, `n
{
fieldLast := SubStr(A_LoopField,InStr(A_LoopField,",",false,0)+1)
remainder1 := SubStr(A_LoopField,1,Instr(A_LoopField,",",false,0))
csvout .= fieldLast "," SubStr(remainder1, 1, -1) "`n"
}
csvout := Substr(csvout, 1, StrLen(csvout)-3)"`n"
msgbox, % "---input---`n" csv "`n`n`n---output---`n" csvout

; * * * now write out the sorted data * * *
FileDelete, %A_Temp%\list.csv
FileAppend, %csvout%, %A_Temp%\list.csv
ExitApp``````
Last edited by carno on 15 Jan 2019, 00:12, edited 3 times in total.
IMEime
Posts: 750
Joined: 20 Sep 2014, 06:15

Re: Sorting CSV Rows of 8 Columns

sounds like 8D array issue..

If I was not wrong..

Pls see the old earlier QA of mine
https://www.autohotkey.com/boards/viewt ... 0&hilit=2d
carno
Posts: 195
Joined: 20 Jun 2014, 16:48

Re: Sorting CSV Rows of 8 Columns

IMEime wrote:
14 Jan 2019, 12:09
sounds like 8D array issue..

If I was not wrong..

Pls see the old earlier QA of mine
https://www.autohotkey.com/boards/viewt ... 0&hilit=2d
That is not a sort function. I am looking for a descending sort function that sorts the last column (8 from left).
IMEime
Posts: 750
Joined: 20 Sep 2014, 06:15

Re: Sorting CSV Rows of 8 Columns

ah sorry
This is Not 8D at all, but just simple 2D
Last edited by IMEime on 14 Jan 2019, 12:58, edited 1 time in total.
IMEime
Posts: 750
Joined: 20 Sep 2014, 06:15

Re: Sorting CSV Rows of 8 Columns

Detour solution is quite simple
Make the item you wanted at the first place (and the others followed, in this case 8th1st2nd3rd....7th9th)
Sort, descend/ascend whatever you wanted
Make the item back just like the original state ( 1st2nd3rd...8th9th)
This is the most easy way, And I guess, the only solution.
IMEime
Posts: 750
Joined: 20 Sep 2014, 06:15

Re: Sorting CSV Rows of 8 Columns

ah...
you want "the final" and "all done" codes ?
ha...
That is your homework. I guess...
User
Posts: 407
Joined: 26 Jun 2017, 08:12

Re: Sorting CSV Rows of 8 Columns

Hope this help

ddd.png (12.46 KiB) Viewed 992 times

Code: Select all

``````csv =
(join`r`n
Col_1,Col_2,Col_3,Col_4,Col_5,Col_6,Col_7,Col_8
Prospect Capital Corporat,74348TAQ5,141,04/15/20,99.63,5.040,BBB,4.92
Whiting Pete Corp New,966387AL6,25,04/01/20,95.75,4.692,BB,3.88
E M C Corp Mass,568648AQ5,250,06/01/20,97.49,4.456,BB-,4.03
Ford Motor Credit Co Llc,345397XF5,375,03/27/20,97.74,4.297,BBB,3.87
)

LV_ModifyCol(8, "SortDesc")

gui, show	;this line is optional

msgbox, % ListViewSave(",")

guiclose:	;______________________
exitapp

ListViewSave(Separator := "	")	;_________________ (Modified) __________________
{
;Default "Separator" is "Tab" Character \ "Separator" can be any character except "#" \ Avoid using "\$" as "Separator" because it is a special character in "RegExReplace" third parameter!

TotalColumns := LV_GetCount("Column")	;"LV_GetCount("Column")" retrieves total columns in the listview control

loop, % LV_GetCount( ) + 1	;"LV_GetCount( )" retrieves listview total rows \ "+1" plus the listview Columns "Header" row
{
RowNumber := a_index - 1

if RowNumber > 0	;"0" is the Columns Header Row (not necessary to add "`r`n" before this row)
TableText .= "`r`n"

loop, %TotalColumns%
{
LV_GetText(TempText, RowNumber, a_index)		;"TempText", variable to store value from a given Cell
;"RowNumber", "0" gets text from columns "Header" Row
;"a_index" is the column number

TempText := RegExReplace(TempText, "#", "##")		;ensures that no character\string is enclosed by "##"
TempText := RegExReplace(TempText, "\Q" Separator, "#S#")	;replace any "Separator" character with "#S#" string - "\Q" treats any RegEx Special characters at its right as literal characters (except "\E" special string)
TempText := RegExReplace(TempText, "\r", "#R#")		;"\r" represents "carriage return" character (replace any "`r" with "#R#")
TempText := RegExReplace(TempText, "\n", "#N#")		;"\n" represents "linefeed" character (replace any "`n" with "#N#")

if a_index = 1
TableText .= TempText
else
TableText .= Separator TempText
}
}

;TableText .= "`r`nEnd >`r`r`r`r`r`r`r`r`r`r< End`r`n`r`n"		;"End >`r`r`r`r`r`r`r`r`r`r< End" indicates the "End" of ListView contents (Extra texts, like "Keys = Values" can be added below)
;since any "`r" (carriage return) is saved as "#R#", it is not possible to find any "`r`r`r`r`r`r`r`r`r`r" string above this line
return, TableText
}

ListViewLoad(FileText, ColOptions := "50", RowOptions := "", Separator := "	")	;__________________ ListView Load (Function) _____________________
{
;Default "Separator" is "Tab" Character \ "Separator" can be any character except "#" \ Avoid using "\$" as "Separator" because it is a special character in "RegExReplace" third parameter!
;Default "ColOptions" is "50", which is the columns width \ Others options such as sort type can be specified, for example: "50 Integer" or "Auto Integer" or "100 Text Logical", etc, etc ...
;Default "RowOptions" is "Blank"! Row options such as "Check", "Select" or "Check Select", etc, etc can be specified! (Probably not necessary, but added this parameter anyway)
;The function returns all the text below "End >`r`r`r`r`r`r`r`r`r`r< End" line! If the returned text contains "Key = Values" contents, "VarRead( )" function can be used to read the Keys values!

LV_Delete( )	;delete all rows

loop, % LV_GetCount("Column")	;"LV_GetCount("Column")" retrieves listview total columns
LV_DeleteCol(1)		;Delete "Column 1" multiple times until there are no more columns to delete

Loop, Parse, FileText, `n, `r	;"`n" is the Delimiter \ any "`r" will be excluded from the beginning and end of each substring (remove any extra "`r" character added while saving)
{
if (A_LoopField == "End >`r`r`r`r`r`r`r`r`r`r< End")	;"= =" is case-sensitive \ since any "`r" (carriage return) is saved as "#R#", if "`r`r`r`r`r`r`r`r`r`r" string is found indicates the end of lisview contents
{
RegExMatch(FileText, "s)End >`r`r`r`r`r`r`r`r`r`r< End(.*)", ExtraText)		;"s)" allows "." to match "`r`n" newlines too \ "ExtraText" contains all the match found \ "ExtraText1" contains the match from the first "( )" \ "ExtraText2" contains the match from the second "( )" \ and so on ...
return, ExtraText1						;The function returns all the text below the first "End >`r`r`r`r`r`r`r`r`r`r< End" line\string found
}

RowNumber := a_index - 1

if RowNumber > 0	;"0" is the columns Header Row (This Row already exist by default, so, does not need to be added)

loop, parse, A_LoopField, %Separator%
{								;"s)" option allows "." to match "`r`n" newlines too \ "?", prevents skipping all the string at once between the first "#" and last "#" character!
TempText := RegExReplace(A_LoopField, "s)#S#|#.*?#(*SKIP)(*F)", Separator)		;skip any "# 0 or more characters #", but, any "#S#" shall be replaced with "Separator" character
TempText := RegExReplace(TempText, "s)#R#|#.*?#(*SKIP)(*F)", "`r")		;skip any "# 0 or more characters #", but, any "#R#" shall be replaced with "carriage return" character (`r)
TempText := RegExReplace(TempText, "s)#N#|#.*?#(*SKIP)(*F)", "`n")		;skip any "# 0 or more characters #", but, any "#N#" shall be replaced with "linefeed" character (`n)
TempText := RegExReplace(TempText, "##", "#")				;remove any extra "#" character added while saving

if RowNumber = 0
{
NextCol++
LV_InsertCol(NextCol, ColOptions, TempText)	;"NextCol" the new column is added to the end of the list (to the right of the last column)
;"TempText" is the column Title \ "ColOptions" specify columns options such as "width", "sort type", etc, etc, ...
}
else
LV_Modify( RowNumber, "Col" a_index " " RowOptions, TempText)	;Fill ListView Control Cells \ "TempText" is the Cell Value
;"RowOptions", specify row options such as "Select", "Check" or "Select Check", etc, etc (propably not necessary, but added it anyway)
}
}
}
``````
rommmcek
Posts: 539
Joined: 15 Aug 2014, 15:18

Re: Sorting CSV Rows of 8 Columns

Her is one very simple examp:

Code: Select all

``````csv =
(
Prospect Capital Corporat,74348TAQ5,141,04/15/20,99.63,5.040,BBB,4.92
Whiting Pete Corp New,966387AL6,25,04/01/20,95.75,4.692,BB,3.88
E M C Corp Mass,568648AQ5,250,06/01/20,97.49,4.456,BB-,4.03
Ford Motor Credit Co Llc,345397XF5,375,03/27/20,97.74,4.297,BBB,3.87
)

mySort( s1, s2 ) {
Return StrSplit(s1, ",").8 < StrSplit(s2, ",").8 ? 1 : -1
}

Sort, csv, CL F mySort
MsgBox % csv``````
Authors: IMEime, (Drugwash, and me)
If you need more speed (for very large csv-s), just tell, I got such a solution too!
Edit: Optimization!
carno
Posts: 195
Joined: 20 Jun 2014, 16:48

Re: Sorting CSV Rows of 8 Columns

Thanks, User! That was more than I expected. I really appreciate your time and I'll save this script as one of the best.
carno
Posts: 195
Joined: 20 Jun 2014, 16:48

Re: Sorting CSV Rows of 8 Columns

Thanks, rommmcek, IMEime and Drugwash. This community is smoking now. Lots of great contributions!
rommmcek
Posts: 539
Joined: 15 Aug 2014, 15:18

Re: Sorting CSV Rows of 8 Columns

This is the fastest one:

Code: Select all

``````csv =
(
Prospect Capital Corporat,74348TAQ5,141,04/15/20,99.63,5.040,BBB,4.92
Whiting Pete Corp New,966387AL6,25,04/01/20,95.75,4.692,BB,3.88
E M C Corp Mass,568648AQ5,250,06/01/20,97.49,4.456,BB-,4.03
Ford Motor Credit Co Llc,345397XF5,375,03/27/20,97.74,4.297,BBB,3.87
)

arr_temp:=[]
loop, parse, csv, `n, `r
Nmrd:=Round(StrSplit(A_LoopField, ",").8*100) ; note 100 is for 2 decimal points (for 3 use 1000 etc.)
, (arr_temp[-Nmrd]="")? arr_temp[-Nmrd]:=[]: "", arr_temp[-Nmrd].push(A_LoopField)
for k, len_k in arr_temp
for l, str in len_k
SortedList.=str "`n"
MsgBox % SortedList``````
User
Posts: 407
Joined: 26 Jun 2017, 08:12

Re: Sorting CSV Rows of 8 Columns

carno wrote:
14 Jan 2019, 14:27
Thanks, User! That was more than I expected. I really appreciate your time and I'll save this script as one of the best.
You welcome!

[Obs]: ListviewSave() and ListviewLoad() had already been written a long time ago, it just took me 5 to 10 minutes to write the above example code!
carno
Posts: 195
Joined: 20 Jun 2014, 16:48

Re: Sorting CSV Rows of 8 Columns

rommmcek wrote:
14 Jan 2019, 14:41
This is the fastest one:

Code: Select all

``````csv =
(
Prospect Capital Corporat,74348TAQ5,141,04/15/20,99.63,5.040,BBB,4.92
Whiting Pete Corp New,966387AL6,25,04/01/20,95.75,4.692,BB,3.88
E M C Corp Mass,568648AQ5,250,06/01/20,97.49,4.456,BB-,4.03
Ford Motor Credit Co Llc,345397XF5,375,03/27/20,97.74,4.297,BBB,3.87
)

arr_temp:=[]
loop, parse, csv, `n, `r
Nmrd:=Round(StrSplit(A_LoopField, ",").8*100) ; note 100 is for 2 decimal points (for 3 use 1000 etc.)
, (arr_temp[-Nmrd]="")? arr_temp[-Nmrd]:=[]: "", arr_temp[-Nmrd].push(A_LoopField)
for k, len_k in arr_temp
for l, str in len_k
SortedList.=str "`n"
MsgBox % SortedList``````
Thanks, and talking about speed, the last column (8th) and the 6th one (from left) originally included percent signs (%), which I had stripped. I see the code does not work with the percent sign (%), although it works with the dollar sign (\$) in the 5th column. I used the following additional script to first remove % sign, sort and then add it back. It works fine, but doesn't seem to me the most optimum way. Is there any better way? (I am now using tab instead of comma in this example of Clip):

Code: Select all

``````
Clip := "
(Join`r`n
Prospect Capital Corporat	74348TAQ5	141	04/15/20	\$99.63	5.040%	-/ BBB-	4.92%
Whiting Pete Corp New	966387AL6	25	04/01/20	\$95.75	4.692%	B2/ BB	3.88%
E M C Corp Mass	268648AQ5	250	06/01/20	\$97.49	4.456%	Ba2/ BB-	4.03%
Teva Pharmaceutical Finan	88166HAD9	250	03/18/20	\$97.40	4.406%	Ba2/ BB	3.89%
Ford Motor Credit Co Llc	345397XF5	375	03/27/20	\$97.74	4.297%	Baa3/ BBB	3.87%
)"

Loop, Parse, Clip, `n, `r
{
StringReplace, NLF, A_Loopfield, `%, prcnt, All
CLP .= NLF . "`r`n"
}
;MsgBox % CLP

mySort( s1, s2 ) {
_s1:=StrSplit(s1, A_Tab), _s2:=StrSplit(s2, A_Tab)
Return _s1.8 < _s2.8 ? 1 : -1
}
Sort, CLP, CL F mySort
;MsgBox % CLP

Loop, Parse, CLP, `n, `r
{
StringReplace, NLF2, A_Loopfield, prcnt, `%, All
CLP2 .= NLF2 . "`r`n"
}
;MsgBox % CLP2
Clip := CLP2``````
Last edited by carno on 15 Jan 2019, 07:33, edited 1 time in total.
rommmcek
Posts: 539
Joined: 15 Aug 2014, 15:18

Re: Sorting Rows of 8 Columns (Comma or Tab Separated)

If column contains additional signs, you have to strip them. For speed you need to do it "on the fly". So use:
for 8. column Round(RTrim((StrSplit(A_LoopField, ",").8), "%")*100) and
for 5. column Round(LTrim((StrSplit(A_LoopField, ",").5), "\$")*100) respectively.
The trick is to get pure Integer (note *100 converting decimal to Int) and then put the whole row into appropriate array row!

For Sort would be:
Return RTrim(StrSplit(s1, ",").8, "%") < RTrim(StrSplit(s2, ",").8, "%") ? 1 : -1 and
Return LTrim(StrSplit(s1, ",").5, "\$") < LTrim(StrSplit(s2, ",").5, "\$") ? 1 : -1 respectively.
No need for integer here since we can compare decimal numbers easily, however, Sort using custom user function is inherently slower. (look to "F MyFunction")

P.s.: This is for Csv. For Tab separator replace "," with A_Tab as you did in your example.
carno
Posts: 195
Joined: 20 Jun 2014, 16:48

Re: Sorting Rows of 8 Columns (Comma or Tab Separated)

rommmcek wrote:
15 Jan 2019, 06:03
If column contains additional signs, you have to strip them. For speed you need to do it "on the fly". So use:
for 8. column Round(RTrim((StrSplit(A_LoopField, ",").8), "%")*100) and
for 5. column Round(LTrim((StrSplit(A_LoopField, ",").5), "%")*100) respectively.
The trick is to get pure Integer (note *100 converting decimal to Int) and then put the whole row into appropriate array row!

For Sort would be:
Return RTrim(StrSplit(s1, ",").8, "%") < RTrim(StrSplit(s2, ",").8, "%") ? 1 : -1 and
Return LTrim(StrSplit(s1, ",").5, "\$") < LTrim(StrSplit(s2, ",").5, "\$") ? 1 : -1 respectively.
No need for integer here since we can compare decimal numbers easily, however, Sort using custom user function is inherently slower. (look to "F MyFunction")

P.s.: This is for Csv. For Tab separator replace "," with A_Tab as you did in your example.
Thanks, again! I'll use your on the fly patch. One more thing. I noticed in your original script (the one before the faster version), I could easily modify for all columns 1 through 8 even for those with only alpha characters (column 1) or dates (column 3). But it seems the faster (current version) works only with pure values and not with alphabets or dates (columns 1 or 3). Is my understanding correct?

Original version that also works with column 1 (or column 3 and all other columns) and can be either ascend or descend:

Code: Select all

``````mySort( s1, s2 ) {
_s1:=StrSplit(s1, A_Tab), _s2:=StrSplit(s2, A_Tab)
Return _s1.1 > _s2.1 ? 1 : -1
}
Sort, Clip2, CL F mySort``````
Current faster version doesn't seem to work with column 1 (or columns 2 and 3):

Code: Select all

``````arr_temp:=[]
loop, parse, csv, `n, `r
Nmrd:=Round(StrSplit(A_Loopfield, A_Tab).1*100) ; note 100 is for 2 decimal points (for 3 use 1000 etc.)
, (arr_temp[-Nmrd]="")? arr_temp[-Nmrd]:=[]: "", arr_temp[-Nmrd].push(A_Loopfield)
for k, len_k in arr_temp
for l, str in len_k
SortedList.=str "`n"
MsgBox % SortedList``````
rommmcek
Posts: 539
Joined: 15 Aug 2014, 15:18

Re: Sorting Rows of 8 Columns (Comma or Tab Separated)  Topic is solved

Sort with array:
carno wrote:But it seems the faster (current version) works only with pure values
Actually with pure integer values.
Yes, since we fill then an array based on that particular integer value thus having them sorted!
Theoretically you could convert string into integer, but that would work only for very short strings, because numbers would be way to big for longer ones. Maybe with some clever algorithm even that'll be possible...˘
For ascending sort use: , (arr_temp[Nmrd]="")? arr_temp[Nmrd]:=[]: "", arr_temp[Nmrd].push(A_LoopField) if you didn't figured it out yet.

Sort with Sort using custom function:
This is more flexible. Depends of course, how we prepare comparation! Be sure to distinguish between alphabetical and logical sort!

Edit: Hint for ascending sort with array.
carno
Posts: 195
Joined: 20 Jun 2014, 16:48

Re: Sorting Rows of 8 Columns (Comma or Tab Separated)

Thanks rommmcek for a very clear explanation.
rommmcek
Posts: 539
Joined: 15 Aug 2014, 15:18

Re: Sorting Rows of 8 Columns (Comma or Tab Separated)

No problem, I'm still learning too!
You won't believe it, the story isn't over yet! Related to this thread I made one more variant with standard Sort command. It's very fast and the "magic character" seems to be suitable for alphabetical sort too (not very good example and not tested extensively though).

Code: Select all

``````#NoEnv
#SingleInstance, force
SetWorkingDir, %A_ScriptDir%
;SetBatchLines, -1 ; uncomment for max speed

csv := "
(Join`r`n
74348TAQ5,Prospect Capital Corporat,141,04/15/20,\$99.63,5.040%,-/ BBB-,4.92%
966387AL6,Whiting Pete Corp New,25,04/01/20,\$95.75,4.692%,B2/ BB,3.88%
268648AQ5,E M C Corp Mass,250,06/01/20,\$97.49,4.456%,Ba2/ BB-,4.03%
345397XF5,Ford Motor Credit Co Llc,375,03/27/20,\$97.74,4.297%,Baa3/ BBB,3.87%
)"

arr_temp:=[], RTrim(csv, "`r`n") ; RTrim is very fast and prevents "empty line" at the end
loop, parse, csv, `n, `r
Nmrd:=-Round(RTrim((StrSplit(A_LoopField, ",").8), "%")*100) ; Remove "-" for ascending sort
, (arr_temp[Nmrd]="")? arr_temp[Nmrd]:=[]: "", arr_temp[Nmrd].push(A_LoopField)
for k, len_k in arr_temp
for l, str in len_k
csv0.=str "`n"
MsgBox % csv0

loop, parse, csv, `n, `r
csv1.= RTrim((StrSplit(A_LoopField, ",").8), "%") . Chr(1) . A_LoopField . "`n"
Sort, csv1, NR ; Remove "R" for ascending sort
loop, parse, csv1, `n
line:=StrSplit(A_loopField, Chr(1)), csv2.=line.2 "`n"
MsgBox % RTrim(csv2, "`n")

loop, parse, csv, `n, `r
csv3.= StrSplit(A_LoopField, ",").2 . Chr(1) . A_LoopField . "`n"
Sort, csv3, CLR ; Remove "R" for ascending sort
loop, parse, csv3, `n
line:=StrSplit(A_loopField, Chr(1)), csv4.=line.2 "`n"
MsgBox % RTrim(csv4, "`n")``````
P.s.: I included sort with array again, since I gave you very awkward instructions for ascending/descending option (now simple with just one character).
Edit: Numerical sort can handle decimal numbers! Updated the script.
carno
Posts: 195
Joined: 20 Jun 2014, 16:48

Re: Sorting Rows of 8 Columns (Comma or Tab Separated)

Thanks, I also tested and looks good so far. Now this is a comprehensive solution!