Linking Automation SQLite save files into Excel
Automation saves a lot of data about your cars and your campaign runs in its save database files and it would be useful to be able to work with that data in Excel and have it automatically update. Luckily this is entirely possible and this tutorial is all about doing just that!
This tutorial focuses on the Excel side of the process and assumes at least basic Excel skills. We will be using the SQL language to communicate with Automation save files. The tutorial will teach basic required SQL queries, for more advanced uses consider learning more about the language and its SQLite3 implementation in specific.
Prerequisites
Automation stores most of its data in .db files. So what are these exactly? Well, they are SQLite3 database files. So what we need to do is to make Excel load these files.
Unfortunately, Excel does not have native support for SQLite3, however what it does have is support for the ODBC interface. In order for Excel to be able to access our SQLite3 files we need to install an ODBC driver for SQLite3.
There are a couple drivers out there, we will be using one from here, as it is free and gets the job done for us. Follow the link and download "sqliteodbc_w64.exe", then install it. Excel should automatically pick up on this driver once it's installed.
On a sidenote, if you would like an easier graphical way to observe the data stored in the database file I highly recommend getting a browser like DB Browser for SQLite.
Creating a query to the SQLite file and accessing data
After installing the driver we are ready to create an external query from Excel to our database. First we need to get the full path to the file we want to load. Automation save files are saved to "C:\Users\[USER]\AppData\Local\AutomationGame\Saved\UserData", campaign save files are stored in the "Campaign" directory inside there. For this part of the tutorial I am going to load the Ellisbury sandbox save file named "Sandbox_230915.db", so my full path is going to be "C:\Users\[USER]\AppData\Local\AutomationGame\Saved\UserData\Sandbox_230915.db".
Now we want to open up Excel and navigate to "Data->Get Data->From Other Sources->From ODBC":
Excel will prompt us with a dialog. First, pop open the "Data source name (DSN)" menu and select "SQLite3 Datasource". Then unfold "Advanced options", we will need to do a couple of things here:
First we need to fill out the "Connection string" with "database=[PATH TO FILE]". The path itself shall not be surrounded with "quotation marks", otherwise this will not work correctly. For the file I've chosen the connection string is "database=C:\Users\[USER]\AppData\Local\AutomationGame\Saved\UserData\Sandbox_230915.db"
Now we need to fill the SQL query. This is where we decide what data to load from the file. The basic syntax to do so is "SELECT [columns] FROM [table]". For example, to get all names and descriptions of all trims in our savefile we can write "SELECT Name, Description FROM Trims".
For the purposes of this section of the tutorial, however, I want to load all existing data from the Trims table, so I will write "SELECT * FROM Trims", the asterisk (*) symbol is a wildcard to get all columns in the table.
Click "Ok" and if everything was configured correctly you will be presented with the extracted data.
Note that some data in the database is stored in a non-human-readable format, that data cannot be loaded into Excel. Unfortunately, this does mean that some data like tech pool or advanced gearing cannot be accessed this way.
Now we want to click "Transform Data" to make some edits. This will open a "Power Query" window where we can manipulate the data and merge connections like this one with each other.
Here you can delete columns you don't need (although generally the better way would be to never load them with the SQL query), rename columns and rearrange them.
Once ready, click "Close & Load" in the top left corner of the window. However before we do that we want to convert the in-game date format to a more usable one.
Converting Automation dates to the correct format
We can find data like model or trim year in the "InternalDays" column. "InternalDays" is the count of days from 1940, with the value of 1 being January 1st 1940. Automation calculates years on a 360-day basis, 30 days a month. We can convert this column to a readable one right inside "Power Query". Click the small button in the top left corner of the table and select "Add Custom Column...".
To get the year, we want to take "InternalDays" and divide it by 360, then we want to add 1940 to it and round the result down. For sandbox this would be enough and we can always use "1" for the month, for campaign data we also want to get the month by finding the modulo of "InternalDays" from 12 and also round that down. The day of the month is irrelevant in Automation, so we can keep always take that as "1" without any extra calculations.
Having calculated those, we want to call the Excel "date" function, supplying it with the year number, the month number and the day number. I will put these calculations into a column named "Date", the full formula being as follows:
#date(Number.RoundDown([InternalDays]/360) + 1940, Number.RoundDown(Number.Mod([InternalDays], 12)), 1)
Click "OK" and now you will find a table with the correct date format. At this point you may also delete the "InternalDays" column, as long as the deletion is done after adding the custom date column, you can see the action order in the "Applied Steps" panel on the right side of the window. If the panel is hidden, you can open it in "View->Query Settings".
Adding results to trim data
You may notice that the imported data only contains engineering choices for the trims but does not contain any of the results. This is because that data is stored separately in the "TrimResults" table. We can access the results of each trim by taking the UID column from the "Trims" table and matching them to those in "TrimResults" (note that results are only generated once the car has been opened and calculated within the game, if this has not been done the results may be missing entirely). To put everything into one table we need to do a join operation. We can do that operation either in the SQL query or by making a second connection and joining our two connections right in Power Query. For the purposes of this tutorial we will pick the latter option.
First, let's create a new connection by right-clicking inside the "Queries" section, then selecting "New Query" and repeating the process we did earlier, only this time we will change the SQL query.
Instead of loading the "Trims" table we will now load the "TrimResults" table:
Once the new table has been loaded, go to "Home->Merge Queries->Merge Queries as New" to join the two tables into a new one.
In the opened dialog, select the two queries we've created and in each of them click the "UID" column so the tables use those columns as reference for joining the data to the right rows. Then select the type of join you want to use, I will use Left Outer here. Then click "OK".
This will give you a new table, at the end of it you will find the second query as a single row. Click the little unfold button next to the column's name and select the items you want to include from it in the new table. If the tables we are trying to combine may have conflicting column names it's good to leave the "Use original column name as prefix" box ticked.
Adding model data to trims
We may also want to include the "Model" data here too, as currently we only know about the trims. Let's create a query for the models:
Then once again convert the model year to a readable format:
And now merge the table we created from merging the other two queries with the new one by matching the trim's "MUID" to the model's "UID":
And expand the added column. Once ready, click the "Close & Load" button in the top left corner of the window.
This will create a sheet for each of the queries we have made. You may also notice that our date column is wrong again after saving, however this time this is easily fixed by changing the data for the entire column.
If you need to change the file you are reading without changing anything else about the queries themselves, you can do so by opening "Data->Queries & Connections", right clicking the query you want to change the source file of and selecting "Edit" and then clicking the gear icon next to the "Source" step in the "Applied Steps" panel. This may be especially useful if you are dealing with campaign where you might need to change which file you want to look at.
In order to update all your queries at once you want to navigate to "Data->Refresh All". This will re-run all the queries and update the data from the database.
Building pivot tables and graphs with campaign data
Loading data from campaign savefiles may be useful to do extra analysis inside excel. Pivot tables are a great way to quickly overview what you are dealing with and can automatically update themselves and the graphs build with them as your campaign goes on.
Let's load our campaign save file. It's recommended to use files whose names will not be changing. You can either use custom names that you will constantly overwrite, or use the "..._Manual.db" files, for example, so that you don't have to manually change file links all the time.
I will run the following SQL query: "SELECT InternalDays, Amount, Type, Details, Region, Completed FROM Finances".
Don't forget to fix the date format:
I have also removed the "InternalDays" column after doing so. Now we can save the query. Open the newly created sheet and change the "Date" column to the correct data type. Now click anywhere within the created table and click "Table Design->Summarize with PivotTable", put it into a new sheet.
Now check "Date" in the fields tab, this should automatically add several items to the "Rows" panel if you have converted that column to the date type.
Now also check "Amount" or drag it manyally into "Values". It should make a sum of that column by default, we will leave it at that.
Now select any cell inside the newly created pivot table and navigate to "PivotTable Analyze->PivotChart". There, select a column or a line chart.
Now select any cell inside the pivot table again and navigate to "PivotTable Analyze->Insert Timeline". Check "Date" in the menu. Then go to "PivotTable Analyze->Insert Slicer", there select "Type", "Details", "Region" and "Completed".
Now you have a set of selections which you can use to specify what data exactly you want to be seeing. Keep in mind that this data is meant for internal usage by the game, so some things are named and organized in weird ways as they are not exactly meant to be human-readable.
If you would like to make the table itself more detailed, you can move "Type" and "Details" into the "Columns" section in the fields tab. Make sure you put "Details" below "Type", as that will ensure the details are grouped to their corresponding types correctly.
Whenever you want to force everything to update, hit "Data->Refresh All".
This tutorial has only scratched the surface of what is possible to do being able to dynamically load the data from Automation. From here the amount of things one can do is only limited by knowledge of Excel and its features.
Epilogue
While this tutorial is not quite about more complex SQL queries, I will provide some SQL examples that may be useful below:
Useful SQL queries
Select all data only of trims whose names start with "Sedan":
SELECT * FROM Trims WHERE Name LIKE "Sedan%"
Select the name and the safety selection of trims that do not have 70s safety:
SELECT Name, Safety FROM Trims WHERE Safety NOT LIKE '%70s%';
Select revenue from sales and all expenses:
WITH _allInternalDays AS (
SELECT DISTINCT InternalDays FROM Finances
), _sales AS (
SELECT InternalDays, SUM(Amount) AS Sales FROM Finances WHERE Type = 'CarSales' AND Completed = 1
GROUP BY InternalDays
), _expenses AS (
SELECT InternalDays, SUM(Amount) AS Expenses FROM Finances WHERE Amount < 0
GROUP BY InternalDays
)
SELECT _allInternalDays.InternalDays, _sales.Sales, _expenses.Expenses FROM _allInternalDays
LEFT OUTER JOIN _sales ON _allInternalDays.InternalDays = _sales.InternalDays
LEFT OUTER JOIN _expenses ON _allInternalDays.InternalDays = _expenses.InternalDays;
Select model and trim names and the safety option of all cars that don't have above basic 60s or 70s safety or radial tyres:
WITH _passed AS (
SELECT UID FROM Trims WHERE Safety IN ( 'Safety_Standard_60s_Name',
'Safety_Advanced_60s_Name',
'Safety_Standard_70s_Name',
'Safety_Advanced_70s_Name')
AND TyreType = 'TyreType_Radial_Name'
)
SELECT Models.Name AS ModelName, Trims.Name AS TrimName, Trims.Safety FROM Trims
JOIN Models ON Trims.MUID = Models.UID
WHERE Trims.Name LIKE 'Sedan%' AND Trims.UID NOT IN (SELECT UID FROM _passed)