Gamelist Excel export script

mitu

As a fun side project, I created a Python script that will export the game list in Emulationstation to an Excel file. You can use it to get an inventory for the games/roms you have in your system. It uses the excellent XlsxWriter Python module to generate the file.

Installation

Download the script from https://gist.github.com/cmitu/73cf0783e9e1e4e02d8ce5c44f7a8984 :

wget -O export.py https://gist.githubusercontent.com/cmitu/73cf0783e9e1e4e02d8ce5c44f7a8984/raw/4a7a8176f8b73ee7bc41ec8d884a9820d84ab639/gamelist.py

Install the XlsxWriter dependency. Easiest way, without relying on pip:

git clone --depth=1 https://github.com/jmcnamara/xlsxwriter
cd xlsxwriter 
sudo python3 setup.py install

Run the script:

python3 export.py /home/pi/RetroPie/roms/export_list.xlsx

The Excel file will be exported at /home/pi/RetroPie/roms/export_list.xlsx, but you can change the path - it's the only parameter to the script. By default, if no path is given, it will export to a file named export-<DATE>.xlsx, in the same folder where the command was executed.

Features

The xlsx file will have 1 sheet per each system with the games present in that system. The sheet contains an auto-filtered table with the list of games, so it's easy to filter by any of the games' metadata.
A summary (index) sheet is generated as the 1st sheet of the file, with links to each system and a total of games for each system/overall
Special sheets are generated for the Favorite, All and Kid Games collections. They'll have an extra column for the system where the game is found, similar to the way they're shown in Emulationstation.
The ROM/game path is not visible, but it's exported as a hidden column (after the Name column).
The special All sheet has the Description for each game as a Comment. Since this information is not easily formatted into a table structure, I've put it as a comment and didn't replicate the info into each system sheet.
Important - the script will try to determine if the ROM/Game file exist on disk, so for large collections it might run a little slow.

Notes

The file exported can be opened by other spreadsheet programs. I've tested it with LibreOffice, Numbers (on macOS) or Google Docs. Not all of the programs will display the file as Excel, but the differences are minor (i.e. LibreOffice does not show the table header colors, Numbers doesn't show the filters and doesn't understands the links, Google Docs doesn't show the filters).

Screenshots

The index/summary

The All sheet, with game descriptions in the comments

Exported system sheet

ChangeLog

2018.02.25 Added Custom collection export, if found in $HOME/.emulationstation/collections

cyperghost

@mitu said in Gamelist Excel export script:

The ROM/game path is not visible, but it's exported as a hidden column (after the Name column).

Excellent!

Please correct links in your posting
https://gist.github.com/cmitu/73cf0783e9e1e4e02d8ce5c44f7a8984
and
https://github.com/jmcnamara/xlsxwriter
for the xlsxwriter module ;)

Do you think you can also read-out the custom collections?
As they are just pure TXT-files ;)

MrSco

awesome script! thanks for sharing.... I'm getting an error however,

pi@retropie:~ $ python export.py /home/pi/RetroPie/roms/export_list.xlsx 2018-02-18 00:16:17,335 INFO Emulationstation systems file used: /home/pi/.emulationstation/es_systems.cfg
2018-02-18 00:16:17,372 INFO System Kodi is skipped as configured
Traceback (most recent call last):
File "export.py", line 460, in <module>
systems = parse_systems()
File "export.py", line 241, in parse_systems
rom = Game(game)
File "export.py", line 146, in init
self.info[attr] = get_xml_element_date(obj, attr)
File "export.py", line 69, in get_xml_element_date
if len(date_text) < len('19860101T000000'):
TypeError: object of type 'NoneType' has no len()

here is the es_systems.cfg ... pastebin

mitu

@mrsco Thanks for trying it out.
The problem is actually in one of the gamelist.xml files, not in the es_systems.cfg. If you could run the script with the -d (debug) parameter to see which system it's reading and then attach the system's gamelist.xml to pastebin.com, that would be more helpful.

MrSco

It looks like C64, pastebin

pi@retropie:~ $ python export.py /home/pi/RetroPie/roms/export_list.xlsx -d 2018-02-18 16:33:03,732 DEBUG Starting
2018-02-18 16:33:03,733 INFO Emulationstation systems file used: /home/pi/.emulationstation/es_systems.cfg
2018-02-18 16:33:03,776 INFO System Kodi is skipped as configured
2018-02-18 16:33:03,777 DEBUG Analyzing system: Commodore Amiga (amiga)
2018-02-18 16:33:03,796 DEBUG ROM A-Train_Disk1 not found in /home/pi/RetroPie/roms/amiga, removed from export
2018-02-18 16:33:03,798 DEBUG Found 1 game(s) for Commodore Amiga (amiga)
2018-02-18 16:33:03,799 DEBUG Analyzing system: Amstrad CPC (amstradcpc)
2018-02-18 16:33:03,802 DEBUG Found 0 game(s) for Amstrad CPC (amstradcpc)
2018-02-18 16:33:03,803 DEBUG System amstradcpc has no games/roms, it's excluded from the export
2018-02-18 16:33:03,804 DEBUG Analyzing system: Arcade (arcade)
2018-02-18 16:33:07,499 DEBUG Found 2147 game(s) for Arcade (arcade)
2018-02-18 16:33:07,500 DEBUG Analyzing system: Atari 2600 (atari2600)
2018-02-18 16:33:08,648 DEBUG Found 646 game(s) for Atari 2600 (atari2600)
2018-02-18 16:33:08,649 DEBUG Analyzing system: Atari 5200 (atari5200)
2018-02-18 16:33:08,761 DEBUG Found 64 game(s) for Atari 5200 (atari5200)
2018-02-18 16:33:08,763 DEBUG Analyzing system: Atari 7800 ProSystem (atari7800)
2018-02-18 16:33:08,887 DEBUG Found 63 game(s) for Atari 7800 ProSystem (atari7800)
2018-02-18 16:33:08,887 DEBUG Analyzing system: Atari 800 (atari800)
2018-02-18 16:33:08,889 DEBUG Found 0 game(s) for Atari 800 (atari800)
2018-02-18 16:33:08,889 DEBUG System atari800 has no games/roms, it's excluded from the export
2018-02-18 16:33:08,890 DEBUG Analyzing system: Atari Lynx (atarilynx)
2018-02-18 16:33:09,024 DEBUG Found 76 game(s) for Atari Lynx (atarilynx)
2018-02-18 16:33:09,025 DEBUG Analyzing system: Atari ST (atarist)
2018-02-18 16:33:09,027 DEBUG Found 0 game(s) for Atari ST (atarist)
2018-02-18 16:33:09,028 DEBUG System atarist has no games/roms, it's excluded from the export
2018-02-18 16:33:09,029 DEBUG Analyzing system: Commodore 64 (c64)
Traceback (most recent call last):
File "export.py", line 460, in <module>
systems = parse_systems()
File "export.py", line 241, in parse_systems
rom = Game(game)
File "export.py", line 146, in init
self.info[attr] = get_xml_element_date(obj, attr)
File "export.py", line 69, in get_xml_element_date
if len(date_text) < len('19860101T000000'):
TypeError: object of type 'NoneType' has no len()

MrSco

Ok, I made a couple edits https://pastebin.com/uT9TjqGR to fix the errors but now I'm hitting this while writing the excel file. I get the file but it errors...

2018-02-18 17:46:16,871 INFO Total games after parsing gamelist files - 13005
2018-02-18 17:46:16,872 INFO Exporting to file /home/pi/RetroPie/roms/export_list.xlsx
/usr/local/lib/python2.7/dist-packages/XlsxWriter-1.0.2-py2.7.egg/xlsxwriter/worksheet.py:2348: UserWarning: Name looks like a cell name in add_table(): 'c64'
/usr/local/lib/python2.7/dist-packages/XlsxWriter-1.0.2-py2.7.egg/xlsxwriter/worksheet.py:2348: UserWarning: Name looks like a cell name in add_table(): 'n64'
/usr/local/lib/python2.7/dist-packages/XlsxWriter-1.0.2-py2.7.egg/xlsxwriter/worksheet.py:2342: UserWarning: Invalid Excel characters in add_table(): 'sg-1000'
Traceback (most recent call last):
File "export.py", line 468, in <module>
xlsx_export_workbook(systems, output)
File "export.py", line 392, in xlsx_export_workbook
xlsx_export_system_row(wb, sheet, j+1, g, g.info["system"])
File "export.py", line 434, in xlsx_export_system_row
game.info['desc'], {'x_scale': 4, 'y_scale': 4})
File "build/bdist.linux-armv7l/egg/xlsxwriter/worksheet.py", line 65, in cell_wrapper
File "build/bdist.linux-armv7l/egg/xlsxwriter/worksheet.py", line 1150, in write_comment
TypeError: object of type 'NoneType' has no len()

mitu

@mrsco I've found the error, can you re-download the script and try again ?

mitu

@cyperghost Sorry, I didn't see your update (probably edited). I've corrected the link, so the : is removed.
I've thought about adding the custom collections part, I just don't use any and didn't know they're saved. I'll see about this next week and give it a try.

cyperghost

@mitu No problem
custom collections are stored in /opt/retropie/configs/all/emulationstation/collections

and their naming is always custom-AaBbCcDdEe.cfg
AaBbCcDdEe represents the name of the collection ;)

Inside is just plain text with Romname and complete path.

Take a look here

PokeEngineer

@mitu

Huh, neat.

detron

I get the same error as @MrSco , but I will keep a close eye on this thread, since i think this looks amazing! so excited to play with this in the future

@mrsco said in Gamelist Excel export script:

Traceback (most recent call last):
File "export.py", line 460, in <module>
systems = parse_systems()
File "export.py", line 241, in parse_systems
rom = Game(game)
File "export.py", line 146, in init
self.info[attr] = get_xml_element_date(obj, attr)
File "export.py", line 69, in get_xml_element_date
if len(date_text) < len('19860101T000000'):
TypeError: object of type 'NoneType' has no len()

mitu

@cyperghost Shoudn't be so hard to add, I just need to compute the fullpath (which I already do anyway to check if the Rom exists) and then parse the .cfg file.

mitu

@detron Have you tried after my last update (1h ago ?) to download the script and try again ? If you still get the error - run with the -d flag and upload the gamelist.xml for the system with the error on pastebin.com so I can take a look.

detron

@mitu said in Gamelist Excel export script:

@detron Have you tried after my last update (1h ago ?) to download the script and try again ? If you still get the error - run with the -d flag and upload the gamelist.xml for the system with the error on pastebin.com so I can take a look.

I got the error about 2 minutes before my post, which was after your update. but I will try again from scratch.

thank you for the assistance. I would love this to be a retropie standard, allowing you to create a report with ease

detron

@mitu said in Gamelist Excel export script:

@detron Have you tried after my last update (1h ago ?) to download the script and try again ? If you still get the error - run with the -d flag and upload the gamelist.xml for the system with the error on pastebin.com so I can take a look.

I still get the same error, if there is any other info that I could provide besides the info below, I am happy to grab any info that might help.

here is the pastebin

link to pastebin

mitu

@detron @MrSco Beginner's error - gist.github.com changes the URL after an edit, so my new version was at a different URL. Try again now, I have updated the URL in the initial post. Overwrite the initial export.py script by downloading it again

wget -O export.py https://gist.githubusercontent.com/cmitu/73cf0783e9e1e4e02d8ce5c44f7a8984/raw/7f61f714bdf9fd49a690cf75cc719b8ffcdf404f/gamelist.py

detron

a lot more activity now, but a different error happened.

again, thank you for your help.

pastebin version

Traceback (most recent call last):
File "export.py", line 464, in <module>
systems = parse_systems()
File "export.py", line 251, in parse_systems
rom.info['name'], s.info['path']))
UnicodeEncodeError: 'ascii' codec can't encode character u'\xe9' in position 3: ordinal not in range(128)

mitu

@detron Please give me the gamelist.xml file where the error occurs. When running with -d parameter, it will show you which system is trying to parse. Get the system's gamelist.xml file and upload it to pastebin.com.

detron

I got it to work. the problem was with a pre-built gamelist file (for a system I have zero roms for.)

this is really cool, thank you for the program, and the support

the prebuilt gamelist has some foreign letters that may be the issue. the file is too big for the free pastebin, but if you really need it, I am sure I can upload it latter to google drive or something for you

mitu

@detron Please do, I'd like to have a look at it. Normally, the gamelist is generated by ES and should be valid UTF8 (which the script can handle), if it's generated externally it might have a different encoding. If you can upload the file as-is (no pasting), that would allow me to try and find a fix/solution for the error.