Results 1 to 4 of 4
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a large file (1m records, 300mb) that I want to sort based on multiple field values. Any one got any handy vba code that will do this for me?

    I've been googling for an answer and I think that I have an approach

    Step 1 - create 'n' temporary sorted files
    1) read t records into array (memory)
    2) sort records
    3) write temp file
    4) repeat until all input records have been read

    Step 2 - merge temporary files
    1) read top record from every file
    2) sort file pointers based on sort key
    3) write to output file from file with lowest key
    4) read another record from file in 3)
    5) go to 2 until no more records

    Any one got any code they want to donate? Or suggestions on improvements?

    Its a little be more difficult than that
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  2. #2
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,833
    Thanks
    7
    Thanked 252 Times in 237 Posts
    Depending on how often you want to do the sort and whether you want to do different sorting, you could load the data into a database and then extract what you need in the order you want.

    Can you be more specific about your needs?

    cheers, Paul

  3. #3
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think I will be running the sort process 3 to 5 times (different sort keys) each quarter. I am certainly looking for a 'set and forget' approach that doesn't involved import, sort, export.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  4. #4
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,833
    Thanks
    7
    Thanked 252 Times in 237 Posts
    3 to 5 different sort keys usually means "can I now have an extra couple of sort keys and then some sub keys". I would definitely use a temporary database so you only need to load the data once, then query as required. SQL Express should do the job and you can use almost any front end, web, VB, SQL client etc.

    cheers, Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •