NoizeBit Posted September 14, 2015 Share Posted September 14, 2015 (edited) Hi,I'd like to ask for a little assistance again, but this time related to array sorting.I have the following code:Global $sSourceCSV Global $sCSVContent DeleteCSVEOLCommas() Func DeleteCSVEOLCommas() ;Deletes end of line commas from source .csv file Local $aArray = FileReadToArray($sSourceCSV) For $i = 0 To UBound($aArray) - 1 $sCSVContent &= StringTrimRight($aArray[$i], 1) & @LF Next Return $sCSVContent EndFunc ;==>DeleteCSVEOLCommasAnd the content of $sSourceCSV:Instrument,Type,Asset,Order #,Order Time,Invested,Target Price,Expiry Price,Expiry Time,Return, High / Low,Low,AUD/USD,36007202,02-09-15 17:51:06,24.00,0.70351,0.70414,02-09-15 18:30:00,0.00, High / Low,Low,AUD/USD,36007200,01-09-15 17:45:03,24.00,0.70350,0.70411,02-09-15 18:30:00,0.00, High / Low,Low,NZD/USD,36004443,02-09-15 16:53:52,24.00,0.63465,0.63534,02-09-15 17:30:00,0.00, High / Low,Low,AUD/USD,36004413,02-09-15 16:53:20,24.00,0.70238,0.70347,02-09-15 17:30:00,0.00,This script removes the commas at the end of every line and adds a line feed - this is necessary to be able to process it further with an external application and it's working as intended so far What I'd like to achieve is to sort the lines first according to the "Expiry Time" field (ascending) and then by the "Order Time" field (also ascending). So the result would be this:Instrument,Type,Asset,Order #,Order Time,Invested,Target Price,Expiry Price,Expiry Time,Return High / Low,Low,AUD/USD,36004413,02-09-15 16:53:20,24.00,0.70238,0.70347,02-09-15 17:30:00,0.00 High / Low,Low,NZD/USD,36004443,02-09-15 16:53:52,24.00,0.63465,0.63534,02-09-15 17:30:00,0.00 High / Low,Low,AUD/USD,36007200,01-09-15 17:45:03,24.00,0.70350,0.70411,02-09-15 18:30:00,0.00 High / Low,Low,AUD/USD,36007202,02-09-15 17:51:06,24.00,0.70351,0.70414,02-09-15 18:30:00,0.00The function should be extended with this part so the returned $sCSVContent var would contain the result. Any ideas are welcome and appreciated Edited September 14, 2015 by NoizeBit Link to comment Share on other sites More sharing options...
czardas Posted September 14, 2015 Share Posted September 14, 2015 (edited) You could do something like this. Make sure you check out all the functions in this script using the help file.#include <Array.au3> #include <File.au3> Local $aRetArray ; read csv to an array _FileReadToArray('my.csv', $aRetArray, $FRTA_NOCOUNT, ',') ;_ArrayDisplay($aRetArray) ; get rid of the extra column ReDim $aRetArray [UBound($aRetArray)][UBound($aRetArray, 2) -1] _ArrayDisplay($aRetArray) ; sort by order time _ArraySort ($aRetArray , 0, 1, 0 ,4) _ArrayDisplay($aRetArray) Edited September 14, 2015 by czardas NoizeBit 1 operator64 ArrayWorkshop Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted September 14, 2015 Moderators Share Posted September 14, 2015 NoizeBit,You could use my ArrayMultiColSort UDF to do this easily - but you will need to swap the column order as the UDF only sorts them in left-to-right order:#include <Array.au3> #include <File.au3> #include "ArrayMultiColSort.au3" $sFile = "test.csv" ; Read file into a 2D array Global $aArray _FileReadToArray($sFile, $aArray, $FRTA_NOCOUNT, ",") ; And here it is _ArrayDisplay($aArray, "Original", Default, 8) ; Swapt the columns to get them in the right order _ArraySwap($aArray, 4, 8, True) _ArrayDisplay($aArray, "Swapped", Default, 8) ; Sort in the required order Local $aSortData[2][2] = [[4, 0], [8, 0]] _ArrayMultiColSort($aArray, $aSortData, 1) ; And her is the result _ArrayDisplay($aArray, "Sorted", Default, 8) ; Finally swap the columns back _ArraySwap($aArray, 4, 8, True) _ArrayDisplay($aArray, "Swapped back", Default, 8)Please ask if you have any questions.M23 NoizeBit 1 Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
czardas Posted September 14, 2015 Share Posted September 14, 2015 Duh, I misunderstood this simple request. operator64 ArrayWorkshop Link to comment Share on other sites More sharing options...
NoizeBit Posted September 14, 2015 Author Share Posted September 14, 2015 NoizeBit,You could use my ArrayMultiColSort UDF to do this easily - but you will need to swap the column order as the UDF only sorts them in left-to-right order:#include <Array.au3> #include <File.au3> #include "ArrayMultiColSort.au3" $sFile = "test.csv" ; Read file into a 2D array Global $aArray _FileReadToArray($sFile, $aArray, $FRTA_NOCOUNT, ",") ; And here it is _ArrayDisplay($aArray, "Original", Default, 8) ; Swapt the columns to get them in the right order _ArraySwap($aArray, 4, 8, True) _ArrayDisplay($aArray, "Swapped", Default, 8) ; Sort in the required order Local $aSortData[2][2] = [[4, 0], [8, 0]] _ArrayMultiColSort($aArray, $aSortData, 1) ; And her is the result _ArrayDisplay($aArray, "Sorted", Default, 8) ; Finally swap the columns back _ArraySwap($aArray, 4, 8, True) _ArrayDisplay($aArray, "Swapped back", Default, 8)Please ask if you have any questions.M23Hi Melba,Thanks a lot, just when I thought I'd have to automate a csv editor to do the sorting you've supplied the best solution Great work! Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted September 14, 2015 Moderators Share Posted September 14, 2015 NoizeBit,Glad I could help.M23 NoizeBit 1 Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted September 15, 2015 Moderators Share Posted September 15, 2015 NoizeBit,Slight change to the UDF and it now sorts without having to swap the columns - the new files are in the UDF thread.#include <Array.au3> #include "ArrayMultiColSort.au3" Global $aArray[][] = [ _ ["Instrument", "Type", "Asset", "Order #", "Order Time", "Invested", "Target Price", "Expiry Price", "Expiry Time", "Return"], _ ["High / Low", "Low", "AUD/USD", "36007202", "02-09-15 17:51:06", "24.00", "0.70351", "0.70414", "02-09-15 18:30:00", "0.00"], _ ["High / Low", "Low", "AUD/USD", "36007200", "01-09-15 17:45:03", "24.00", "0.70350", "0.70411", "02-09-15 18:30:00", "0.00"], _ ["High / Low", "Low", "NZD/USD", "36004443", "02-09-15 16:53:52", "24.00", "0.63465", "0.63534", "02-09-15 17:30:00", "0.00"], _ ["High / Low", "Low", "AUD/USD", "36004413", "02-09-15 16:53:20", "24.00", "0.70238", "0.70347", "02-09-15 17:30:00", "0.00"]] _ArrayDisplay($aArray, "Original", Default, 8) ; Sort in the required order Local $aSortData[2][2] = [[8, 0], [4, 0]] _ArrayMultiColSort($aArray, $aSortData, 1) ; And here is the result _ArrayDisplay($aArray, "Sorted", Default, 8)M23 NoizeBit 1 Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
NoizeBit Posted September 15, 2015 Author Share Posted September 15, 2015 (edited) NoizeBit,Glad I could help.M23Just one thing: how do I return the result to a variable? If I do so it returns only 0, but how do I return the sorted content?#include <Array.au3> #include <File.au3> #include <ArrayMultiColSort.au3> Global $sSourceCSV = @ScriptDir & "\test.csv" Global $sCSVContentSorted SortCSVLines() MsgBox(0, "", $sCSVContentSorted) Func SortCSVLines() ;Sorts lines first by Expiry Time and then by Order Time Local $aArray _FileReadToArray($sSourceCSV, $aArray, $FRTA_NOCOUNT, ",") Local $aSortData[2][2] = [[4, 0], [8, 0]] $sCSVContentSorted = _ArrayMultiColSort($aArray, $aSortData, 1) Return $sCSVContentSorted EndFunc ;==>SortCSVLines Edited September 15, 2015 by NoizeBit Link to comment Share on other sites More sharing options...
NoizeBit Posted September 15, 2015 Author Share Posted September 15, 2015 NoizeBit,Slight change to the UDF and it now sorts without having to swap the columns - the new files are in the UDF thread.#include <Array.au3> #include "ArrayMultiColSort.au3" Global $aArray[][] = [ _ ["Instrument", "Type", "Asset", "Order #", "Order Time", "Invested", "Target Price", "Expiry Price", "Expiry Time", "Return"], _ ["High / Low", "Low", "AUD/USD", "36007202", "02-09-15 17:51:06", "24.00", "0.70351", "0.70414", "02-09-15 18:30:00", "0.00"], _ ["High / Low", "Low", "AUD/USD", "36007200", "01-09-15 17:45:03", "24.00", "0.70350", "0.70411", "02-09-15 18:30:00", "0.00"], _ ["High / Low", "Low", "NZD/USD", "36004443", "02-09-15 16:53:52", "24.00", "0.63465", "0.63534", "02-09-15 17:30:00", "0.00"], _ ["High / Low", "Low", "AUD/USD", "36004413", "02-09-15 16:53:20", "24.00", "0.70238", "0.70347", "02-09-15 17:30:00", "0.00"]] _ArrayDisplay($aArray, "Original", Default, 8) ; Sort in the required order Local $aSortData[2][2] = [[8, 0], [4, 0]] _ArrayMultiColSort($aArray, $aSortData, 1) ; And here is the result _ArrayDisplay($aArray, "Sorted", Default, 8)M23Thanks Melba, I'll update my script accordingly. Link to comment Share on other sites More sharing options...
NoizeBit Posted September 17, 2015 Author Share Posted September 17, 2015 (edited) I'm trying to put yor UDF inside a function, but I can't figure out why does it return the original data instead of the sorted result?#include <Array.au3> #include <File.au3> #include <ArrayMultiColSort.au3> Global $sSourceCSV = @ScriptDir & "\test.csv" Global $aArray, $sCSVContentSorted SortCSVLines() MsgBox(0, "", $sCSVContentSorted) Func SortCSVLines() ;Sorts lines first by Expiry Time and then by Order Time _FileReadToArray($sSourceCSV, $aArray, ",") For $i = 0 To UBound($aArray) - 1 Local $aSortData[2][2] = [[4, 0], [8, 0]] _ArrayMultiColSort($aArray, $aSortData, 1) $sCSVContentSorted &= $aArray[$i] & @LF Next Return $sCSVContentSorted EndFunc ;==>SortCSVLines Edited September 17, 2015 by NoizeBit Link to comment Share on other sites More sharing options...
czardas Posted September 17, 2015 Share Posted September 17, 2015 (edited) Does this do what you want?#include <Array.au3> #include <File.au3> #include <ArrayMultiColSort.au3> Global $sSourceCSV = @ScriptDir & "\test.csv" Global $sCSVContentSorted SortCSVLines() MsgBox(0, "", $sCSVContentSorted) Func SortCSVLines() ;Sorts lines first by Expiry Time and then by Order Time Local $aArray _FileReadToArray($sSourceCSV, $aArray, $FRTA_NOCOUNT, ",") Local $aSortData[2][2] = [[4, 0], [8, 0]] _ArrayMultiColSort($aArray, $aSortData, 1) For $i = 0 To UBound($aArray) - 1 ; first loop For $j = 0 To UBound($aArray, 2) - 1 ; second loop $sCSVContentSorted &= $aArray[$i][$j] & "," Next $sCSVContentSorted = StringTrimRight($sCSVContentSorted, 1) $sCSVContentSorted &= @LF Next ; There is no need to return a global variable because it has been modified in the global scope ;Return $sCSVContentSorted EndFunc ;==>SortCSVLinesEdit - I forgot to change the csv file name back to the original: try it now.Edit2- Removed $aArray to local scope where it belongs and fixed a small problem with the code.You might want to replace @LF with @CRLF Edited September 17, 2015 by czardas NoizeBit 1 operator64 ArrayWorkshop Link to comment Share on other sites More sharing options...
NoizeBit Posted September 17, 2015 Author Share Posted September 17, 2015 Does this do what you want?#include <Array.au3> #include <File.au3> #include <ArrayMultiColSort.au3> Global $sSourceCSV = @ScriptDir & "\test.csv" Global $sCSVContentSorted SortCSVLines() MsgBox(0, "", $sCSVContentSorted) Func SortCSVLines() ;Sorts lines first by Expiry Time and then by Order Time Local $aArray _FileReadToArray($sSourceCSV, $aArray, $FRTA_NOCOUNT, ",") Local $aSortData[2][2] = [[4, 0], [8, 0]] _ArrayMultiColSort($aArray, $aSortData, 1) For $i = 0 To UBound($aArray) - 1 For $j = 0 To UBound($aArray, 2) - 1 $sCSVContentSorted &= $aArray[$i][$j] & "," Next StringTrimRight($sCSVContentSorted, 1) $sCSVContentSorted &= @LF Next ; There is no need to return a global variable because it has been modified in the global scope ;Return $sCSVContentSorted EndFunc ;==>SortCSVLinesEdit - I forgot to change the csv file name back to the original: try it now.Edit2- Removed $aArray to local scope where it belongs.Thanks for the fast reply, czardas! I see now what was missing, thanks for the contribution czardas 1 Link to comment Share on other sites More sharing options...
czardas Posted September 17, 2015 Share Posted September 17, 2015 No worries. You will notice I overlooked a couple of things in my first post. I'm a bit hung over - that's my excuse. ^^ Grab the modified code above. NoizeBit 1 operator64 ArrayWorkshop Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted September 17, 2015 Moderators Share Posted September 17, 2015 NoizeBit,Are you all set now? This works for me:#include <Array.au3> #include <File.au3> #include "M:\Program\Au3 Scripts\UDFs\ArrayMultiColSort\ArrayMultiColSort.au3" Global $aArray SortCSVLines() _ArrayDisplay($aArray, "", Default, 8) Func SortCSVLines() ;Sorts lines first by Expiry Time and then by Order Time ; Simulate reading file Global $aArray[][] = [ _ ["Instrument", "Type", "Asset", "Order #", "Order Time", "Invested", "Target Price", "Expiry Price", "Expiry Time", "Return"], _ ["High / Low", "Low", "AUD/USD", "36007202", "02-09-15 17:51:06", "24.00", "0.70351", "0.70414", "02-09-15 18:30:00", "0.00"], _ ["High / Low", "Low", "AUD/USD", "36007200", "01-09-15 17:45:03", "24.00", "0.70350", "0.70411", "02-09-15 18:30:00", "0.00"], _ ["High / Low", "Low", "NZD/USD", "36004443", "02-09-15 16:53:52", "24.00", "0.63465", "0.63534", "02-09-15 17:30:00", "0.00"], _ ["High / Low", "Low", "AUD/USD", "36004413", "02-09-15 16:53:20", "24.00", "0.70238", "0.70347", "02-09-15 17:30:00", "0.00"]] ; Set sort order Local $aSortData[2][2] = [[8, 0], [4, 0]] ; Sort array _ArrayMultiColSort($aArray, $aSortData, 1) EndFunc ;==>SortCSVLinesM23 NoizeBit 1 Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
NoizeBit Posted September 17, 2015 Author Share Posted September 17, 2015 No worries. You will notice I overlooked a couple of things in my first post. I'm a bit hung over - that's my excuse. ^^ Grab the modified code above.Got it, I'll put it to good use Link to comment Share on other sites More sharing options...
NoizeBit Posted September 17, 2015 Author Share Posted September 17, 2015 NoizeBit,Are you all set now? This works for me:#include <Array.au3> #include <File.au3> #include "M:\Program\Au3 Scripts\UDFs\ArrayMultiColSort\ArrayMultiColSort.au3" Global $aArray SortCSVLines() _ArrayDisplay($aArray, "", Default, 8) Func SortCSVLines() ;Sorts lines first by Expiry Time and then by Order Time ; Simulate reading file Global $aArray[][] = [ _ ["Instrument", "Type", "Asset", "Order #", "Order Time", "Invested", "Target Price", "Expiry Price", "Expiry Time", "Return"], _ ["High / Low", "Low", "AUD/USD", "36007202", "02-09-15 17:51:06", "24.00", "0.70351", "0.70414", "02-09-15 18:30:00", "0.00"], _ ["High / Low", "Low", "AUD/USD", "36007200", "01-09-15 17:45:03", "24.00", "0.70350", "0.70411", "02-09-15 18:30:00", "0.00"], _ ["High / Low", "Low", "NZD/USD", "36004443", "02-09-15 16:53:52", "24.00", "0.63465", "0.63534", "02-09-15 17:30:00", "0.00"], _ ["High / Low", "Low", "AUD/USD", "36004413", "02-09-15 16:53:20", "24.00", "0.70238", "0.70347", "02-09-15 17:30:00", "0.00"]] ; Set sort order Local $aSortData[2][2] = [[8, 0], [4, 0]] ; Sort array _ArrayMultiColSort($aArray, $aSortData, 1) EndFunc ;==>SortCSVLinesM23Yeah, only the returning part was a bit cloudy for me, but now it produces the exact result I was looking for, thanks. Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted September 17, 2015 Moderators Share Posted September 17, 2015 NoizeBit,Excellent.M23 NoizeBit 1 Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
czardas Posted September 17, 2015 Share Posted September 17, 2015 It's Melba who did all the hard work really. It's good you tried to figure it out yourself. NoizeBit 1 operator64 ArrayWorkshop Link to comment Share on other sites More sharing options...
NoizeBit Posted September 17, 2015 Author Share Posted September 17, 2015 It's Melba who did all the hard work really. It's good you tried to figure it out yourself.I can't argue with that, but I thank both of you for pushing me toward the right direction Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now