Gamelist Excel export script

FetusSandwich

@mitu Hopefully you don't mind me resurrecting this thread, as this was an amazing tool when it was working for me and I think more people in the RetroPie need visibility into your awesome work.

Problem for me now is that: it was working for me, but now it has stopped running, and I'm completely unsure as to why.

Here is the error at the end of the script. It seems to get through all gamelists with no errors, then provides me with:

Traceback (most recent call last):
  File "export.py", line 583, in <module>
    systems = parse_systems()
  File "export.py", line 261, in parse_systems
    gamelist = et.parse(gamelist_path)
  File "/usr/lib/python2.7/xml/etree/ElementTree.py", line 1182, in parse
    tree.parse(source, parser)
  File "/usr/lib/python2.7/xml/etree/ElementTree.py", line 656, in parse
    parser.feed(data)
  File "/usr/lib/python2.7/xml/etree/ElementTree.py", line 1653, in feed
    self._raiseerror(v)
  File "/usr/lib/python2.7/xml/etree/ElementTree.py", line 1517, in _raiseerror
    raise err
xml.etree.ElementTree.ParseError: not well-formed (invalid token): line 564, column 32

Any troubleshooting or insight you could provide here would be greatly appreciated. Thanks!

mitu

@FetusSandwich Run it with --debug and see where it stops. Looks like an issue with a malformed gamelist.xml file.

FetusSandwich

@mitu Thanks for your help on this, mitu.

I was indeed able to get this script running eventually and found my way eventually with error logs to the problem child gamelist. What a lifesaver this thing is...

Thanks for the help.

bgennette

Just one tiny (or two) things to add ...

You must 'cd' back to the /home/pi/retropie directory to run the 'export.py' script,
otherwise it won't be found.

And speaking of finding ... the list will be at /home/pi/retropie/RetroPie/roms/export_list.xlsx

bye.

2Play

@mitu
thank you for the py script!

I got from git the latest gamelist.py . i fixed an error found in one list (had &)

my case the error is below

2021-01-27 20:19:47,680 INFO   Exporting to file /home/pi/RetroPie/roms/export_list.xlsx
Traceback (most recent call last):
  File "gamelist.py", line 600, in <module>
    xlsx_export_workbook(systems, output, collections)
  File "gamelist.py", line 416, in xlsx_export_workbook
    string="{0} ({1})".format(s.info['fullname'], s.info['name'])
UnicodeEncodeError: 'ascii' codec can't encode character u'\xd6' in position 1: ordinal not in range(128)

My locale is all set to US UTF8, latest Raspbian OS updates, pi4

mitu

@2play If you provide the offending gamelist.xml, I can attempt to add a fix to the script.

2Play

@mitu I cant find a bad xml
I also replaced few 'Ö' (no other 'Ö', 'ğ', 'Ş', Ç')
re run and get same result. I googled the error:
'ascii' codec can't encode character u'\xd6'
and most pointers relate to how python converts things. And that this was fixed on Python3!
Ive exported full run if helps
https://pastebin.com/11GS3ZS5

Lolonois

@2play From the distance: I assume your ~/.emulationsstation/es_systems.cfg contains a non ASCII character (Ö). Have a look there.
If you want to keep your O-umlaut, maybe adding a declaration to the es_systems.cfg is honored by the XML parser(elementree), so add <?xml version="1.0" encoding="UTF-8" standalone="yes" ?> and give it a try.

HTH

mitu

@2play said in Gamelist Excel export script:

Ive exported full run if helps
https://pastebin.com/11GS3ZS5

Unfortunately that doesn't hint me on what is wrong. Maybe @Lolonois has a point, check the systems' config and make sure it's properly XML formed.
Alternatively, you could try running the script via python3 (instead of just python), but make sure you install the XLSX module with python-pip3 so it's available for python3 also.

2Play

@Lolonois & @mitu
First of all a big thank you for your kind replies!
It is an amazing script @mitu

@Lolonois I have changed the xml header: Not working with python v2
@Lolonois You are correct on the Ö. It is in the LÖVE 2d Game Engine system name tag. If I remove command runs fine ( @mitu )
@mitu Since I have Python 3.7.3 installed, I did as you advised.
Python 3 works and exports excel list without any changes (no removal of Ö from LÖVE)

So if I may suggest to ppl do below:

Check Python version 2 or 3

python --version
or
python3 --version

Install Python3 If No Result Above

sudo apt-get install -y python3-dev python3-setuptools

GET MITU GAMELIST LATEST FILE:

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

XLSXWRITER Installation:

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

RUN (normal):

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

RUN (debug):

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

mitu

@2play said in Gamelist Excel export script:

@Lolonois You are correct on the Ö. It is in the LÖVE 2d Game Engine system name tag. If I remove command runs fine ( @mitu )

I see. I'll try to see if it would work with Python2, otherwise I'll update the installation instruction on the first post.

Thanks for the update.

mitu

@mitu said in Gamelist Excel export script:

It is in the LÖVE 2d Game Engine system name tag. If I remove command runs fine ( @mitu )

I already had Love as a system, but RetroPie doesn't configure it with LÖVE, just Love. Did you modify the name of the system manually ?

Nevertheless, I added a fix for the issue and also fixed the bug of exporting empty comments for games that don't have any description.

2Play

@mitu welcome, yes and thank you :-)
Tested and now python 2 also works ok with your fix!

2Play

@mitu gm friend

i was checking today my es reported arcade set at 340 and export xls reports 331
I have to check what is missing. but later due to work
do you think is name related or script uses name tag counting?

MrPacMan17

Wow, this script is awesome! Thank you very much for it. Much appreciated!

joesnow81

I know this thread is very old now but hoping someone may be able to assist. I have used this script in the past and it worked flawlessly but when I went to run it today I got the following error

2022-01-04 13:53:37,126 INFO Exporting to file /home/pi/RetroPie/roms/export_list.xlsx
Traceback (most recent call last):
File "export.py", line 599, in <module>
xlsx_export_workbook(systems, output, collections)
File "export.py", line 394, in xlsx_export_workbook
if len(custom_collections)>0 :
TypeError: object of type 'NoneType' has no len()

The file is not being saved. Any idea what the above means and how to resolve it?

mitu

@joesnow81 Can you run the script with the debug flag enabled (-d) and post the output ? If it's large, use pastebin.com to post it.
Since it seems to crash during the collection parsing part, can you also run

ls -l $HOME/.emulationstation/collections

and post the output ?

kelvinator3300

@mitu
What are your thoughts on exporting to CSV (comma seperated values) so that any spreadsheet program can open?
Also, your thoughts on being able to edit the exported file and then export/convert to gamelist.xml?

mitu

@kelvinator3300 said in Gamelist Excel export script:

What are your thoughts on exporting to CSV (comma seperated values) so that any spreadsheet program can open?

Exporting to CSV is easy enough that it doesn't need a special script - can be done with xmlstarlet without problems. However, this will produce one CSV per gamelist, whereas a spreadsheet can contain all the info.

Is there a spreadsheet program that can't open XLS(X) files ?

Also, your thoughts on being able to edit the exported file and then export/convert to gamelist.xml?

That's something that was asked before, but it's not something that I'm interested in particular - the script is meant just to produce a summary of your gamelist(s). If you want to edit your gamelists, use something like ARRM, which can do that without issues.

nelsonchelini

Hi @mitu,

I'm sorry to post here something nothing to do with this topic, but I've being trying to submit a Help post and I can't because it requires a tag. However, I can't find the tag field to include them. Could you help me with that, please? No need to say I'm a newbie here... and not so good with these social media platform either. I tried to send a message to somebody asking, but didn't get that too. Since you are a moderator, I just gave it a shot here. Thanks a lot!