Sort large file using vba

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote