Jump to content

Reorder Text File and calculate totals


Recommended Posts


I have a report from our POS system which I have managed to convert into a tab seperated file with only the relavant fields listed.

My next problem is I need to relist the file so the first field is in numerical sequence from lowest to highest. I also have an issue where some of the postings are split across mutiple lines due to the bill having multiple payments. For example;

 956  03/08/14 08:28  85.00
 957  03/08/14 08:30  5.50
 959  03/08/14 08:57  25.50
 968  03/08/14 10:20  29.00
 970  03/08/14 10:23  46.00
 973  03/08/14 10:50 13.50 
 963  03/08/14 10:37  76.50
 964  03/08/14 10:38  204.50
 972  03/08/14 10:49  93.00
 973  03/08/14 10:50  42.30

When Im reordering the file by the first number I also need to check if there are more transactions for that same number and then add together the price field so the transaction is only written once with the correct total.

So for the line with 973 I would need to add together the 13.50 price with 42.30 and then write the line just once with the correct total field of 55.80.

Im not sure how I should do this :( I have attached a sample text file of data and I'm hoping someone can explain the best method to achieve the result I require.



Link to comment
Share on other sites

Hi John,

I had a quick play with these commands but I wasnt sure how I would get the arraysort to only target the first transaction number rather then the whole line.

Would I need to do something like a stringsplit to extract just the transaction number and then sort on these?


Link to comment
Share on other sites


#include <File.au3>
#include <Array.au3>

Local $myfile = "sample.txt"
Local $array
_FileReadToArray($myfile, $array, 0, @TAB)
; _ArrayDisplay($array)
Redim $array[UBound($array)][UBound($array, 2)+1]   ; add a col 4 for count
For $i = 0 to UBound($array)-1
      $array[$i][0] = Number($array[$i][0])  ; to allow numeric sorting
      $array[$i][4] = 1
_ArraySort($array)     ; sort the array by numbers in 1st column
For $i = UBound($array)-2 to 0 step -1   
   If $array[$i][0] = $array[$i+1][0] Then     ; if duplicate number found
       $array[$i][3] = StringFormat("%.2f", $array[$i][3] + $array[$i+1][3])   ; add values in col 3
       $array[$i][4] += $array[$i+1][4]     ; add the count(s)
       _ArrayDelete($array, $i+1)
 _ArrayDisplay($array)   ; enjoy
Edited by mikell
Link to comment
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

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...