In my last post I mentioned that I might be clearing out my TSM data in preparation for Mists of Pandaria. The other day I asked my twitter followers “Informal Survey: Will you be clearing your auction house data for MoP or 5.0.4?” and the results were a bit mixed. One suggestion by @Twitchietgw
@Xsinthis All my data goes in a database, and have a table of important dates to help search for specific time periods.
And that got the gears turning in my head to do something similar. In this post I will be discussing on how to import TradeSkillMaster accounting data into a Microsoft Access Database. Some advantages to this:
- Keep a record of all your sales and purchases;
- Enables analysis of your data;
- Lets you clear your in game TSM data without actually losing any data;
- Easier to manage spreadsheets by exporting with queries;
- Database is easier to manage large amounts of data like this than an excel sheet;
- Allows for central data management
As per Twitchie’s suggestion I’m also going to be keeping a table of important dates. I’ll be including a copy of a blank database structure at the end of the post.
Converting the Data to a CSV File
First, we need to find the data. You need to locate your accounting data which will be in your World of Warcraft\WTF\Account\<Account Name>\SavedVariables directory and is called TradeSkillMaster_Accounting.lua (don’t bother opening it, we’ll be doing something to it first).
Margi of The Consortium made a CSV Conversion Tool for TSM (you can access it directly at http://goblineeringtools.herokuapp.com/tsm_accounting_csv) to take that ugly mess of a .lua file and turn it into a much more manageable CSV file (CSV stands for Comma Separated Values). You can either load the file directly or paste it into the text box at the side (which comes with an example to test the data); Once this is done hit convert and save the file somewhere you will remember.
Creating the Database and Populating It
Next we’ll create the actual database itself. Create a new Microsoft Access database (you can simply right click in a folder New>Microsoft Access Database), preferably in the same folder where you just saved your converted TSM data.
Once you’ve got your new file from the top menu select External Data>Text File and load the .csv file you just saved, make sure Import the source data into a new table in the current database is selected and hit OK. If you already have a table in the database you’d like to add the new records to you can select Append a copy of the records to the table: and select the table. (Note: this only is available if you already have a table in the database.)
On the next screen make sure Deliminated is selected and hit next. Before you leave this screen click on the Advanced… button on the bottom right of the screen. Under the Dates, Times, and Numbers section make sure the Date Order is set to YMD to coincide with the date conventions used by TSM so you don’t get a bunch of errors at the end of the process.
Click through the next screen, you should now be presented with this:
Make sure the the screen looks like the image above with Let Access add primary key, this will essentially assign a number id to each transaction in the data base (sales and purchases). Click Next.
Finally you will be prompted to chose a name for the table the data will be placed in. If this is your first batch of data I just suggest “Accounting Data” or even “Transactions” for something nice and generic. Click Finish.
On the left hand side of the screen the new table you just created should be listed. Open that and you should get something similar to the following
Congratulations, all your data is in the database!
Feel free now to clear your TSM data so next time you go to add data you won’t get duplicates (which for our purposes would be bad since the TSM export has no unique identification to catch these things). Alternatively you can continue with your TSM data and the next time you enter data into the database check the last date in the system and only export data after that date/time by deleting everything older than that from the CSV file. We will be covering that briefly in a later post, so check back later for that.
This is the start of a series which I’ll expand on in the next few days, so keep an eye out for more posts. Topic I still intend to cover include (but not limited too):
- Making and exporting queries into Excel files;
- Appending new data;
- Analyzing auction data.
If anyone has some significant dates from the Cataclysm expansion (patch date launches, patch announcements etc.) please post them in the comments below or email them to me, I have very limited info in these areas.