Aggregate text files

This has had me pulling my hair out for three days now...


Here's the goal: To go through all the database files (.txt format) in a designated folder in order to update an Excel Sheet with the required information from those database text files. Then to summarise the information in a simple table.

The text files are individual Market Exports from a game I play, and are titled as "Region-ItemType-YYYY.MM.DD HHMMSS.txt"

eg. "Heimatar-Tritanium-2009.07.02 133353.txt".

I need to know how to sift through the text files to find the most recent one with matching "Region-ItemType" parts in the file name, and refresh the Excel Sheet with the updated data.

After that, I need to be able to combine all of the cheapest sale prices for each item, with a minimum amount for sale (differs per item), while also being limited to location the item is being sold at, into a single sheet of the important information I wanted to extract in the first place.

At the end of the whole process, I need to have a single Excel Sheet with every different item's lowest price for each different place, displayed in a table I can use to make formulas from.

So if I have an export from a place called "place1" and one from "place2", looking at the same item called "item1", I should have two rows of tabulated data, one for each place's lowest price. And this should update at the touch of the "Refresh All" button, or... However you'd have to do it

I have attached a copy of one of the database text files.

Thanks for ANY help received, rest assured if you reply you've made the world a better place for me to live in :P

Firstly, you would likely get more assistance if you tackled this 'project' one step at a time, as that would also allow you to explain in more detail each section.
Anyhoo, this sounds quite like what I do for a similar game I play online, so lets see...

Step1
So you have a directory that has, for example;

Heimatar-Tritanium-2009.07.02 133353.txt
Heimatar-Tritanium-2009.07.03 133353.txt
SomeWhereElse-SomethingElse-2009.07.02 133353.txt
SomeWhereElse-Tritanium-2009.07.02 133353.txt
Heimatar-SomethingElse-2009.07.02 133353.txt
Heimatar-Tritanium-2009.07.04 133353.txt

So you want to find the file that has the most recent date for "Heimatar-Tritanium"?
How do you get this where-and-what text, (in this case "Heimatar-Tritanium"). Are you wanting to put that in via an inputbox, or should the macro work on every item and every place, and construct this where-and-what text itself?

Step2
Ok, so now we have the most recent file. We can open it as a comma delimeted text file, but then what?
You want to find the cheapest price that is in this file where the qty is greater than or equal to a certain value? If so, where does this comparison value come from? Should the macro prompt you for it, or is it going to be on a spreadsheet somewhere?

So we work through all the files, and we get the cheapest price for each item at each location where the qty is greater than a particular value, then what?

After that, I need to be able to combine all of the cheapest sale prices for each item, with a minimum amount for sale (differs per item), while also being limited to location the item is being sold at, into a single sheet of the important information I wanted to extract in the first place.

Do you just then want the information put onto a single sheet?

As the input data is quite complex it might be worth you uploading a zip file with quite a few input files in it too.

Re: Step 1

In theory, all the name of the file would do is make is possible to distinguish between the different exports (what and where) and to find the newest one.

And you got the examples pretty much spot on ;-) Attached is a few more though.

Step 2

Once we know which text files are going to be used to update/overwrite the database, I'd need to run the following on each export:
1. Limit to "stationID" (two different values, as I'm looking at two different stations. One is located in Heimatar Region, the other in The Forge region.)
2. Limit to "FALSE" in the "bid" column. (This seperates the Buy Orders from the Sell Orders. Probably need to play the game to know what I mean )
3. Limit to a minimum "volRemaining" value using a sheet that I can manually input the value I want for each different item.
4. Retrieve the lowest "Price" value with all the previous filters applied, and put that final value in a specified cell on a "Summary" sheet.

This summary sheet should basically tell me what the cheapest prices are for each item at each station.


If you can get me even one step closer to the end of this damn huge (for me) project, I'd be very happy Thanks for your time so far. I hope I answered your questions accurately enough

4. Retrieve the lowest "Price" value with all the previous filters applied, and put that final value in a specified cell on a "Summary" sheet.

This summary sheet should basically tell me what the cheapest prices are for each item at each station.

When extracting the price information, what else is it that you want 'coupled' with that in the summary sheet?
The item and region name taken from the filename?
Anything else from the table? 'volRemaining', 'stationID', etc?

1. Limit to "stationID" (two different values, as I'm looking at two different stations. One is located in Heimatar Region, the other in The Forge region.)

Where do you get the stationID from to limit the data with? Should these be on a sheet where you can specify a list of station ID's you are interested in?

Ok, so here goes. I have attached a sample workbook.

It doesn't produce data anything like the layout that is in your most recent workbook.
The reason for this is because it looks quite complex, and only you can really decide what data is supposed to go where.

My 'example' workbook has 2 worksheets, 'Summary' and 'User Input'.

The User Input worksheet is where you can have a list of items and the minimum qty's that you want to consider, as you described above.
When you run the 'Update' macro it will;
1) Look in the same directory as the Excel file is in for all text files that have a name of the correct format.
2) Determine the 'Newest' file for each of the Region / Item combinations.
3) Open each of those files, filter by the stationID's you gave, the minimum qtys given on the "User Input" sheet, and the 'bid' column.
4) Once the filters are applied it will then take the cheapest price, and place the information relating to this on the Summary Sheet.

If you step through the code you will be able to see how it is performing these actions.

Any questions please do ask. Hopefully this will show you enough for you to then be able to adapt the code to fit your spreadsheet.

That example layout is exactly what I'm looking to achieve.

But, I ran the "Update" macro and got an error from Microsoft Visual Basic saying "Run-time error '445': Object does not support this action".

So I tried closing Excel (without saving) and running the "reset" macro followed by the "update" macro. Same thing happened

I'll take a good long look through the code and see if there's something obvious I'm missing, like having to put all information concerning the item names and minimum volRemaining into the User Inputs sheet before I run the macro. But even so, when I first opened the .xls there were no entries in the summary sheet with values from the stationID "60003760" even with exports containing that station in the same directory.

Hmm... seems I've got some work to do :D

Thanks so much for your help so far, I'll add any questions to this thread once I've figured out this code stuff. <3

EDIT: I'm getting closer Still haven't completely worked out how to code works yet, but I'm getting there. Might take a couple days to get to the stage where I'm able to modify it to me exact needs, though.

Hmmm, I've just downloaded my example file, and also your example market logs to another PC and run the update and all ran fine with no errors.
Are you using those example files or a larger group of files?
If you are using a larger group of input files then PM me and I'll send you my e-mail address for you to send a zip file to. There might be something 'strange' in one of the files.

As for the missing reports I noticed that there was nothing for "The Forge" when I ran against your sample data, but just took it to be that nothing matched.
I think now that there was a typo in one of your earlier messages:

The two stationIDs I'm interested in are 30003760 and 60004588,

there were no entries in the summary sheet with values from the stationID "60003760" even with exports containing that station in the same directory

The reason there are no reports for 60003760, is because the filter is looking for 30003760

To fix that go to top of the VBA file, and edit the constant "STATION_IDS_STRING" to contain the correct number, eg:

Please Login or Register  to view this content.

The Reset macro is run by the Update macro when it starts anyway, but is also offered to the user in case you want to clear the data for any other reason

With regards to the Item names and min Vol Remaining on the User Input sheet, the macro will look down that list for an item, (there must be no gaps in the list). If the item is not found then it will use a 'min' of 1. This is done to save you having to enter every single item if you just want to know the price whatever the qty is

HOLD THE PHONE!!!!111

I just found out there's a SP2 for Microsoft Office. Aren't a genius? We'll see if that solves this problem... I'll update the thread once it's installed.

EDIT: Didn't help