OOTP Developments Forums - View Single Post - Is there a report that lists all the teams in my baseball world?

stealofhome · 01-04-2019, 11:03 AM

There is a way to do this that takes a little work and requires Excel/something similar but makes it possible. It only took me a couple minutes.

In the game, go to Game -> Game Settings -> Database
Select the Database Tools dropdown and then Configure data export to CSV files
Click deactivate all
Check these boxes: Replace accents, Dump only cities in use, Dump only nations in use, Insert column names/CSV headings, nations, cities, teams_basic
Click Save
Click the Database Tools dropdown again and select Export data to CSV files
Click the Database Tools dropdown once more and select Open data import/export folder
Open cities.csv, states.csv, and teams.csv in Excel - they should open as regular Excel formatted files.

Depending on your level of familiarity with Excel, this can get tricky - you basically use the VLOOKUP command to link up the teams, cities, and states.

Insert three columns in the teams.csv file after the city_id column (select the heading, right click, insert). Name the first column City, the second column state_id, and the third column State.

In the first new column (City), put the formula =VLOOKUP(city_id,cities.csv table,4,0)
-the city_id will be the cell directly next to where you are typing, probably F2
-while editing the formula, you can go to the cities.csv file and select the appropriate range. Mine was A2:D5478, this would change based on the number of cities in your game.
My final formula looks like this: =VLOOKUP(F2,cities.csv!$A$2:$D$5478,4,0)
Press enter and drag down the length of the file.

In the second new column (state_id), put the formula =VLOOKUP(city_id,cities.csv table,3,0)
Follow the same rules as before but you will be referencing the third column instead.
Here's my final formula: =VLOOKUP(F2,cities.csv!$A$2:$D$5478,3,0)
Press enter and drag down the length of the file.

In the third new column (State), put the formula =VLOOKUP(state_id,state.csv table,3,0)
Follow the same rules as before but you will be referencing the state.csv file instead.
Here's my final formula: =VLOOKUP(H2,states.csv!$A$2:$C$1204,3,0)
Press enter and drag down the length of the file.

You now have Team, City, and State for all teams in the universe.

You can clean the file up by selecting the columns you just created, right click copy, then right click, paste as values. This will remove the formulas and only leave the values in place. If you move or delete the other files that you're referencing, you can run into problems. You can also delete the state_id column as you probably won't need it anymore.

Hope this helps!

01-04-2019, 11:03 AM	#3
stealofhome Hall Of Famer Join Date: Apr 2014 Posts: 2,282 Blog Entries: 1	There is a way to do this that takes a little work and requires Excel/something similar but makes it possible. It only took me a couple minutes. In the game, go to Game -> Game Settings -> Database Select the Database Tools dropdown and then Configure data export to CSV files Click deactivate all Check these boxes: Replace accents, Dump only cities in use, Dump only nations in use, Insert column names/CSV headings, nations, cities, teams_basic Click Save Click the Database Tools dropdown again and select Export data to CSV files Click the Database Tools dropdown once more and select Open data import/export folder Open cities.csv, states.csv, and teams.csv in Excel - they should open as regular Excel formatted files. Depending on your level of familiarity with Excel, this can get tricky - you basically use the VLOOKUP command to link up the teams, cities, and states. Insert three columns in the teams.csv file after the city_id column (select the heading, right click, insert). Name the first column City, the second column state_id, and the third column State. In the first new column (City), put the formula =VLOOKUP(city_id,cities.csv table,4,0) -the city_id will be the cell directly next to where you are typing, probably F2 -while editing the formula, you can go to the cities.csv file and select the appropriate range. Mine was A2:D5478, this would change based on the number of cities in your game. My final formula looks like this: =VLOOKUP(F2,cities.csv!$A$2:$D$5478,4,0) Press enter and drag down the length of the file. In the second new column (state_id), put the formula =VLOOKUP(city_id,cities.csv table,3,0) Follow the same rules as before but you will be referencing the third column instead. Here's my final formula: =VLOOKUP(F2,cities.csv!$A$2:$D$5478,3,0) Press enter and drag down the length of the file. In the third new column (State), put the formula =VLOOKUP(state_id,state.csv table,3,0) Follow the same rules as before but you will be referencing the state.csv file instead. Here's my final formula: =VLOOKUP(H2,states.csv!$A$2:$C$1204,3,0) Press enter and drag down the length of the file. You now have Team, City, and State for all teams in the universe. You can clean the file up by selecting the columns you just created, right click copy, then right click, paste as values. This will remove the formulas and only leave the values in place. If you move or delete the other files that you're referencing, you can run into problems. You can also delete the state_id column as you probably won't need it anymore. Hope this helps!