NoizeBit Posted September 14, 2015 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
czardas Posted September 14, 2015 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
Moderators Melba23 Posted September 14, 2015 Moderators 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: Reveal hidden contents 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
czardas Posted September 14, 2015 Posted September 14, 2015 Duh, I misunderstood this simple request. operator64 ArrayWorkshop
NoizeBit Posted September 14, 2015 Author Posted September 14, 2015 On 9/14/2015 at 1:35 PM, Melba23 said: 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!
Moderators Melba23 Posted September 14, 2015 Moderators 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: Reveal hidden contents 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
Moderators Melba23 Posted September 15, 2015 Moderators 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: Reveal hidden contents 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
NoizeBit Posted September 15, 2015 Author Posted September 15, 2015 (edited) On 9/14/2015 at 3:59 PM, Melba23 said: 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
NoizeBit Posted September 15, 2015 Author Posted September 15, 2015 On 9/15/2015 at 10:01 AM, Melba23 said: 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.
NoizeBit Posted September 17, 2015 Author 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
czardas Posted September 17, 2015 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
NoizeBit Posted September 17, 2015 Author Posted September 17, 2015 On 9/17/2015 at 5:25 PM, czardas said: 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
czardas Posted September 17, 2015 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
Moderators Melba23 Posted September 17, 2015 Moderators 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: Reveal hidden contents 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
NoizeBit Posted September 17, 2015 Author Posted September 17, 2015 On 9/17/2015 at 5:46 PM, czardas said: 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
NoizeBit Posted September 17, 2015 Author Posted September 17, 2015 On 9/17/2015 at 5:54 PM, Melba23 said: 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.
Moderators Melba23 Posted September 17, 2015 Moderators 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: Reveal hidden contents 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
czardas Posted September 17, 2015 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
NoizeBit Posted September 17, 2015 Author Posted September 17, 2015 On 9/17/2015 at 6:09 PM, czardas said: 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
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