Gamelist Excel export script

mitu

@papinist Unless I have the gamelist.xml to see how it's structured, I can't tell why the difference comes up.
But if it's the same situation as the atari7800 system, then there's nothing I can do - it seems the gamelist.xml does not contain all the entries ES counts.

papinist

Thank you @mitu, you already did an enormous work!

I'm really a beginner with retropie, so I don't know why ES and folders have different numbers compared to gamelist :)

thanks again!

pkzipfish

I get an immediate error on the first system the script tries to analyze (arcade). Any suggestion for a workaround?

pi@retropie:~ $ sudo python export.py -d ~/test.xlsx
2018-10-03 17:27:11,209 DEBUG  Starting
2018-10-03 17:27:11,211 INFO   Emulationstation systems file used: /etc/emulationstation/es_systems.cfg
2018-10-03 17:27:11,253 DEBUG  Analyzing system: Arcade (arcade)
2018-10-03 17:27:11,255 DEBUG  Gamelist for arcade is read from /home/pi/RetroPie/roms/arcade/gamelist.xml
Traceback (most recent call last):
 File "export.py", line 584, in <module>
   systems = parse_systems()
 File "export.py", line 274, in parse_systems
   rom.info['realpath'] = get_rom_path(s.info['path'], rom.info['path'])
 File "export.py", line 201, in get_rom_path
   return os.path.realpath(path_to_check)
UnboundLocalError: local variable 'path_to_check' referenced before assignment

mitu

OK, fixed the error. Can you re-download again the script and try again ?

pkzipfish

@mitu said in Gamelist Excel export script:

OK, fixed the error. Can you re-download again the script and try again ?

Thanks!

Almost works. It sorts all my systems, then throws this error when trying to write the Excel file:

2018-10-03 17:50:35,826 INFO   Total games after parsing gamelist files - 5140
2018-10-03 17:50:35,826 INFO   Exporting to file /home/pi/test.xlsx
Traceback (most recent call last):
  File "export.py", line 595, in <module>
    xlsx_export_workbook(systems, output, collections)
  File "export.py", line 390, in xlsx_export_workbook
    if len(custom_collections)>0 :
TypeError: object of type 'NoneType' has no len()

pkzipfish

@pkzipfish said in Gamelist Excel export script:

@mitu said in Gamelist Excel export script:

OK, fixed the error. Can you re-download again the script and try again ?

Thanks!

Almost works. It sorts all my systems, then throws this error when trying to write the Excel file:
2018-10-03 17:50:35,826 INFO   Total games after parsing gamelist files - 5140
2018-10-03 17:50:35,826 INFO   Exporting to file /home/pi/test.xlsx
Traceback (most recent call last):
  File "export.py", line 595, in <module>
    xlsx_export_workbook(systems, output, collections)
  File "export.py", line 390, in xlsx_export_workbook
    if len(custom_collections)>0 :
TypeError: object of type 'NoneType' has no len()

Nevermind this error - it goes away if I don't run the script as sudo.

But I do get another error, which seems to be because one of my custom collections' names is too long:

2018-10-03 17:56:55,633 INFO   Total games after parsing gamelist files - 5140
2018-10-03 17:56:55,634 INFO   Exporting to file /home/pi/test.xlsx
2018-10-03 17:56:55,642 DEBUG  Adding custom collections to export
2018-10-03 17:56:55,643 DEBUG  Exporting custom collection Best platform games
2018-10-03 17:56:55,644 DEBUG  Exporting custom collection Best rpgs (ign)
2018-10-03 17:56:55,645 DEBUG  Exporting custom collection Best adventure games (ign+)
2018-10-03 17:56:55,646 DEBUG  Exporting custom collection Weird games
2018-10-03 17:56:55,648 DEBUG  Exporting custom collection Best games of all time (ign)
2018-10-03 17:56:55,649 DEBUG  Exporting custom collection Best multiplayer games
2018-10-03 17:56:55,650 DEBUG  Exporting custom collection Best puzzle games
2018-10-03 17:56:55,651 DEBUG  Exporting custom collection Best strategy games
2018-10-03 17:56:55,651 DEBUG  Exporting custom collection Best japan-only games
2018-10-03 17:56:55,652 DEBUG  Exporting custom collection N favorites
2018-10-03 17:56:55,654 DEBUG  Exporting custom collection Best games of all time (polygon)
Traceback (most recent call last):
  File "export.py", line 595, in <module>
    xlsx_export_workbook(systems, output, collections)
  File "export.py", line 396, in xlsx_export_workbook
    s = wb.add_worksheet(collection)
  File "build/bdist.linux-armv7l/egg/xlsxwriter/workbook.py", line 174, in add_worksheet
  File "build/bdist.linux-armv7l/egg/xlsxwriter/workbook.py", line 691, in _add_sheet
  File "build/bdist.linux-armv7l/egg/xlsxwriter/workbook.py", line 741, in _check_sheetname
xlsxwriter.exceptions.InvalidWorksheetName: Excel worksheet name 'Best games of all time (polygon)' must be <= 31 chars.
'''

mitu

@pkzipfish said in Gamelist Excel export script:

xlsxwriter.exceptions.InvalidWorksheetName: Excel worksheet name 'Best games of all time (polygon)' must be <= 31 chars.

Nothing I can do here, except truncate the name.

detron

@mitu

I decided to run your amazing export tool again (have not ran it since you first made it), and it seems to be failing.

not sure if you still work on this or not.

below is what returned, and I did try it with and without a "sudo".

thank you either way.

pi@retropie:~ $ python export.py /home/pi/RetroPie/roms/export_list.xlsx
2019-01-01 23:28:33,368 INFO Emulationstation systems file used: /etc/emulationstation/es_systems.cfg
2019-01-01 23:29:11,228 INFO System RetroPie is skipped as configured
Traceback (most recent call last):
File "export.py", line 591, in <module>
systems = parse_systems()
File "export.py", line 264, 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 9062, column 24

detron

@mitu

I should add this is a new 4.4 build on a raspberry Pi 3B+
any other data that might be useful, I will happily grab it for you

mitu

@detron I'm certainly interested in fixing any errors, so can you please run the export with the -d option and then paste the error log on pastebin.com ? It must be something in one of the gamelist.xml that's unhandled.

detron

@mitu

thank you. I will do that later today.

I appreciate your help

detron

@mitu

error (link to pastebin)

error text

2019-01-02 17:08:18,944 DEBUG  Analyzing system: Super Famicom Disk System (sfc)
2019-01-02 17:08:18,945 DEBUG  Gamelist for sfc is read from /home/pi/RetroPie/roms/sfc/gamelist.xml
Traceback (most recent call last):
  File "export.py", line 591, in <module>
    systems = parse_systems()
  File "export.py", line 264, 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 9062, column 24


I looked all over for a log file, but found none.  if there is one and you want it, just let me know where it is.

thank you for your help.

mitu

@detron said in Gamelist Excel export script:

parse_systems

I think your gamelist.xml file for the SFC system is not a well formed XML document. The error is at line 9062. You can upload the file also to pastebin.com (if it's not too large for them) or pastebin.ubuntu.com so I can take a look.

detron

@mitu

I do not even have any sfc roms, so I removed it, and it worked as expected.

it was too big for pastebin, but here is a link to the prebuilt sfc gamelist file if you are curious

sfc gamelist on google drive

again, thank you for your help.

mitu

@detron OK, glad it worked. For the record, at line 9062 is an incorrect XML fragment

<path>./Dragon Quest I & II (Japan) (Translated En).7z</path>

The & character is a special character in XML and cannot be used as is.

detron

@mitu said in Gamelist Excel export script:

@detron OK, glad it worked. For the record, at line 9062 is an incorrect XML fragment
<path>./Dragon Quest I & II (Japan) (Translated En).7z</path>
The & character is a special character in XML and cannot be used as is.

Ahh,

thank you for your help. I love this script!

I understand how csv can be exported, but how do you create an Excel tab? that to me is just magic. lol

papinist

In excel, click on 'data' tab, then'load external data" "from text", select your .csv file and then choose if you have a delimiter character or fixed lenght data.
(I'm not sure about the exact command names since my excel is not in english :) )

detron

@papinist said in Gamelist Excel export script:

In excel, click on 'data' tab, then'load external data" "from text", select your .csv file and then choose if you have a delimiter character or fixed lenght data.
(I'm not sure about the exact command names since my excel is not in english :) )

@papinist

thank you, but I mean make Excel tabs when not in Excel, like this script does.

papinist

Oh, I just forgot that the script outputs directly excel tabs! You are right, seems like magic :)

detron

@papinist

I still appreciate the willingness to help.

thank you.

(yes, it is like magic)