Gamelist Excel export script

mitu · 22 Feb 2018, 18:39

@used2berx said in Gamelist Excel export script:

Is there any chance that you could write a reverse script that would then convert the existing spreadsheet back into a gamelist.xml? That would make this absolutely perfect then and I would be able to do all of my editing inside of a single spreadsheet (assuming it wasn't insanely taxing on my ancient PC to do so).

Having read the other 2 threads about your work on the NES ResurrectionXtra, I know how important this feature would be for you. I'll take a look at producing a gamelist - or any other way of extracting the information out of an Excel spreadsheet. Technically, it shouldn't be to difficult, since this is already supported by Excel (https://bitwizards.com/Thought-Leadership/Blog/2010/November-2010/How-To-Export-an-Excel-2010-Worksheet-to-XML).
I've worked before with VBA in Excel (it's not pretty), I'll see what can be done and how easy would be to make this export happen with just the push of a button.

Used2BeRX · 22 Feb 2018, 21:56

@mitu said in Gamelist Excel export script:

to a gamelist.xml? That would make this absolutely perfect then and I would be able to do all of my editing inside of a single spreadsheet (assuming it wasn't insanely taxing on my ancient PC to do so).

Thanks bud. :)

I can't wait to take this for a spin. Things have been really busy IRL and my focus on the project has been all over the place though. I don't want to half-ass it with my testing when I do it, so I need to make sure I can dedicate some real time to this and make sure everything is working as designed.

Hope you're going to be around and you're not going to disappear any time soon.

This could be something I've been wishing I had for over a decade now man, and it's so cool that you just happened to make it for different reasons than I needed it for. So glad you did it and that @meleu brought your work to my attention. Thanks for looking into the reversal process for me.

MrSco · 24 Feb 2018, 23:36

@mitu thanks for the updates. No more errors now when exporting, but the file has some warnings when opening in excel 2016 ...

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error100720_01.xml</logFileName>
<summary>Errors were detected in file 'D:\raspberry pi\export_25-02-2018.xlsx'</summary>
<removedFeatures>
<removedFeature>Removed Feature: AutoFilter from /xl/tables/table3.xml part (Table)</removedFeature>
<removedFeature>Removed Feature: Table from /xl/tables/table3.xml part (Table)</removedFeature>
</removedFeatures>
</recoveryLog>

the file still seems to have all the data in there. not sure what it had to remove...

detron · 25 Feb 2018, 01:21

@mrsco said in Gamelist Excel export script:

@mitu thanks for the updates. No more errors now when exporting, but the file has some warnings when opening in excel 2016 ...

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error100720_01.xml</logFileName>
<summary>Errors were detected in file 'D:\raspberry pi\export_25-02-2018.xlsx'</summary>
<removedFeatures>
<removedFeature>Removed Feature: AutoFilter from /xl/tables/table3.xml part (Table)</removedFeature>
<removedFeature>Removed Feature: Table from /xl/tables/table3.xml part (Table)</removedFeature>
</removedFeatures>
</recoveryLog>

the file still seems to have all the data in there. not sure what it had to remove...

I got the same thing, but thought it might have been something on my end. I just accepted the changes and saved the file.
since I am not alone, I agree this might be something to investigate

mitu · 4 Mar 2018, 04:05

@mrsco @detron I think it has removed the table and auto-filtering on one of the exported systems. I encountered a related error (which should be fixed now) when the system exported has a - in the name (such as mame-libretro).
Can you see which system (worksheet) doesn't have the table style and the filters ? Do you have any systems with empty list of games ? Alternatively, you could upload the export file somewhere and I could take a look at it.

EDIT: While looking over the code, I think the error you're receiving might appear when you don't have any Favorites of Kid Games. Can you check to see if this is the case ?

mitu · 25 Feb 2018, 06:15

@cyperghost I added the ability to export the custom collections, just re-download the script (URL changed, but it's updated in the 1st post). See if it works out.

detron · 4 Mar 2018, 04:05

@mitu,

sorry for the delay. I do not have any Favorites of kids. this might be the issue.
is there a reason that the "all" has notes with informational text, but the individual system tabs do not?

thank you for all you have done on this. it is great!

@mitu said in Gamelist Excel export script:

@mrsco @detron I think it has removed the table and auto-filtering on one of the exported systems. I encountered a related error (which should be fixed now) when the system exported has a - in the name (such as mame-libretro).
Can you see which system (worksheet) doesn't have the table style and the filters ? Do you have any systems with empty list of games ? Alternatively, you could upload the export file somewhere and I could take a look at it.

EDIT: While looking over the code, I think the error you're receiving might appear when you don't have any Favorites of Kid Games. Can you check to see if this is the case ?

mitu · 4 Mar 2018, 04:44

@detron said in Gamelist Excel export script:

is there a reason that the "all" has notes with informational text, but the individual system tabs do not?

It's an arbitrary decision :). I left out of Description field since it wouldn't look nice in a table, but I decided to add it as a comment for the All sheet.

detron · 4 Mar 2018, 18:57

@mitu said in Gamelist Excel export script:

@detron said in Gamelist Excel export script:

is there a reason that the "all" has notes with informational text, but the individual system tabs do not?

It's an arbitrary decision :). I left out of Description field since it wouldn't look nice in a table, but I decided to add it as a comment for the All sheet.

that makes sense. probably a good descision. thanks

Used2BeRX · 15 Mar 2018, 13:03

@mitu Hey bud. I had a few questions for you. I've only got about 120 more carts to go and should be done with that in a few weeks. Still have all the boxart to work on and tons of other stuff, but I think I need a break from artwork and would love to dive into this when the cart is done.

Were you ever able to make some sort of script or find out if there is a way to convert this spreadsheet back to a gamelist.xml file with all of the tags?
Do you know if this can be used in Google Spreadsheets? I have both, but personally I prefer having my work online where others can view it or even make a copy of their own to further edit when I'm ready for a public release. It's okay if we can't do that, but it would be awesome if we could.
Could you add more tags if I gave you a list? Or... if it's pretty menial to do so and you think you could teach a total newb how to do it would you be able to maybe walk me through that? As of now, most people who would use your script wouldn't benefit from them much if at all, but in order for me to convert my synopsis.txt files to a gamelist.xml file to a Spreadsheet and then all the way back to a synopsis.txt file, I would need to be able to populate all of the fields from my original text files in your spreadsheet or it wouldn't work right for me when it was converted back. Even more importantly, if I wasn't able to carry over gamelist.xml specific tags that won't go back into the synopsis.txt files, it would still leave us with a gamelist.xml file that is missing a lot of tags like locations for artwork and videos and such after I was done with the Spreadsheet edits and converted it back to the gamelist.xml file.

I can't wait to try this out man. I should be able to start using it in about two weeks hopefully. If this works like I think it will I think you might have been able to save me hundreds of hours or more of work if I actually do eventually get around to completing all of the console and handheld systems that I'm planning on working on.

Thanks again!

mitu · 15 Mar 2018, 13:12

@used2berx Excel has a straightforward Export to XML functionality, as long as you feed it the correct schema (xsd) it's really easy to export. One thing that it's not correctly exported is the dates, but that we might address separately. I did a few tests - using my export to get back a gamelist.xml file - and seems simple enough to try.
So, a few questions:

What version of Excel are you using ? (2007, 2010, 2013, 2016)
Do you have a sample sheet for me to try out ? A few lines would be enough and should get you covered for 3. You can give me a Google docs sheet that I can copy and test on it.

Used2BeRX · 15 Mar 2018, 13:19

@mitu Nice. I wasn't aware of the export to .xml functionality. Google Docs may have this as well, and it might be pretty easy to export the Excel to GD since I won't be making it look fancy like my compatibility spreadsheets will be. I believe I have Excel 2007, although I can't remember if I've ever installed it on my "new" ancient computer I'm working from right now. I'll have to answer that one when I get around to this and I'll try to figure out any GD stuff on my own and let you know how it goes.

I have an old gamelist.xml file from about a month ago on my computer I believe. It's outdated from what I have now, but there isn't anything important missing from it that would be on it now. For our testing purposes here it should have most of what you need.

There will still be some more tags down the road to add as well. I have a request in with @meleu for some additional tags that we'll need, but he hasn't had time to get to them yet. At this stage I'm just trying to get all the ducks in a row as much as I can while I'm still working on another aspect of the project.

Give me a few minutes and I should be able to get that gamelist.xml file to you. I'll also pull out one specific good example of all of the current tags meleu has in the script to post right up on the board here to view.

BTW... thanks for doing this. I'm sure most people won't need these tags for now, and some people using them might actually not like even having them. Maybe if you're going to add these tags for me you could have a separate option to load an "Rx Spreadsheet" or something to avoid this?

Used2BeRX · 15 Mar 2018, 13:25

Oh... also BTW....

As far as the "date problem" goes, one of my requests to @meleu was to have an additional tag in there. I don't remember what the exact name of the tag was right now, but it's an alternate date tag that has the "human readable" date on it instead of the one we need to convert it to for use with RetroPie. My synopsis.txt files for use on the XBox emulators use the human readable date. When he adds this, both tags will be in the gamelist.xml file. Maybe this could help you out as well?

Used2BeRX · 15 Mar 2018, 13:44

@mitu Damn.... The gamelist is too big to upload here and when I zip it I get a notification saying that I do not have enough privlidges to preform this action.

I'll just put up a sample game entry for now and if you think you need the full file I'll upload it elsewhere and send it to you.

I also looked back into the request to meleu and I have manually added all of the tags that I have requested that he make in his script. At this point I can't think of other tags that might be necessary, but there might be a couple that I've missed. Fingers crossed. :)

<game>
<name>Mario in Some Usual Day</name>
<path>/home/pi/RetroPie/roms/nes/(3) Unlicensed - Homebrew/(3_10) Super Mario Bros Updates/Mario in Some Usual Day.zip</path>
<image>/home/pi/RetroPie/Media/nes/Artwork/Cart/Mario In Some Usual Day.png</image>
<video/>
<marquee/>
<thumbnail/>
<desc>Some usual day isn't so usual after all. This game features enemies from Zelda, Metroid, and Kirby, new power-ups, and a brand-new secret coin system. And yes, that is a fire-ball-chucking Raccoon Mario below. This is a complete hack of Super Mario Bros 3, featuring 4 new worlds and 1 secret world! All worlds are filled with new levels, new graphics, new asm mods and more!

<link>https://www.romhacking.net/hacks/53/</link>
<releasedate>19900101T000000</releasedate>
<releasetext>1990</releasetext> `Text date that is in our synopsis.txt files` 
<hackreleasedate/>
<transreleasedate/>
<developer>Nintendo R&amp;D2</developer>
<publisher>Nintendo of America Inc.</publisher>
<genre>Action / Platformer</genre>
<players>1</players>
<playerstext>1</playerstext> (Need this to properly convert back to synopsis after edits. Just a pure rip of the synopsis information without your code for converting it to an ES friendly field.  Examples: "1 to 2 VS", "1 or 2 CO-OP")
<region>USA</region>
<platform>nes</platform>
<media>Cartridge</media>
<controller>NES Gamepad</controller>
<gametype>Hack</gametype>
<xtrasname>Mario In Some Usual Day</xtrasname>
<originaltitle>Super Mario Bros. 3</originaltitle>
<alternatetitle/>
<hackedby>JaSp</hackedby>
<translatedby/>
<version>1.1</version>
<boxfront>/home/pi/RetroPie/Media/nes/Artwork/Box Front/Mario In Some Usual Day.png</image>
<cart>/home/pi/RetroPie/Media/nes/Artwork/Cart/Mario In Some Usual Day.png</cart>
<title>/home/pi/RetroPie/Media/nes/Artwork/Titles/Mario In Some Usual Day.jpg</title>
<action>/home/pi/RetroPie/Media/nes/Artwork/Action/Mario In Some Usual Day.jpg</action>
<threedbox>/home/pi/RetroPie/Media/nes/Artwork/3D Boxart/Mario In Some Usual Day.png</threedbox>
<gamefaq/>
<manual/>
<vgmap/>
<license/>
<programmer/>
<musician/>
</game>

Please let me know if you have any questions at all. I will try to get back to you ASAP. I need to sleep soon and might not be able to get to you before I go to work. I will answer any questions you have in no more than 36 hours from now. :)

mitu · 15 Mar 2018, 13:57

@used2berx What I meant for example was an actual Excel file, don't you want to convert from Excel to a gamelist.xml file ? My script does not convert - right now - all the fields you have in this sample gamelist.xml file, it only reads the standard ES metadata tags.
You want me to modify the export script to produce an Excel from this sample gamelist XML file ?

Used2BeRX · 15 Mar 2018, 14:44

@mitu Oh... yeah. I don't have any excel or Google Spreadsheet with any of this information now. I haven't run your script at all yet.

I was actually asking if you could add new columns for your spreadsheet conversion program that would use all of the tags listed in the gamelist.xml file above. That way, after I do the editing to fields like <desc/>, <genre/>, <publisher/>, etc., then I could convert the spreadsheet back to a gamelist.xml file and none of the other fields would be missing when converted back.

Some of these fields will actually never be used in ES, but I'm hoping once I start releasing sets that some of them are, such as manuals, gamefaqs and more image types. The fields that will never be used in ES are actually things that will be carried all the way back to the synopsis.txt files, which are individual files with the game rom name that are used to display all of the game information in the XBox Madmab Edition emulators. (These files are currently how meleu's script gets all of the individual game information into a gamelist.xml file, and with some other wizardry he's managed to also add all of the file location tags so long as media with the exact same name as the rom/synopsis.txt name is in the corresponding location).

This is why I had suggested a "Regular Spreadsheet" and a separate "Rx Spreadsheet" option so people who just want the ES tags wouldn't have all of my tags cluttering up their spreadsheet... or you wouldn't have to go out of your way to make a completely separate script for me that would have to be updated separately if you added other cool stuff to the one you have now.

Sorry for the confusion. :|

Used2BeRX · 16 Mar 2018, 01:58

@mitu BTW... figured I should show you an example of what the synopsis.txt files look like. This is for Mario in Some Usual Day.txt, same game as the example above:

Mario in Some Usual Day
Original Title: Super Mario Bros. 3
Platform: Nintendo Entertainment System
Region: USA
Media: Cartridge
Controller: NES Gamepad
Genre: Action / Platformer
Gametype: Hack
Release Year: 1990
Hack Release Year: 2005
Developer: Nintendo R&D2
Publisher: Nintendo of America Inc.
Hacked by: JaSp
Version: 1.1
Players: 1
_________________________

Some usual day isn't so usual after all. This game features enemies from Zelda, Metroid, and Kirby, new power-ups, and a brand-new secret coin system. And yes, that is a fire-ball-chucking Raccoon Mario below. This is a complete hack of Super Mario Bros 3, featuring 4 new worlds and 1 secret world! All worlds are filled with new levels, new graphics, new asm mods and more!

https://www.romhacking.net/hacks/53/
http://www.mobygames.com/game/nes/super-mario-bros-3

Mario in Some Usual Day.txt is the same name as the <xtrasname/> tag in the gamelist.xml. All media for this game such as the rom, synopsis, artwork, etc. will have this exact file name.

mitu · 17 Mar 2018, 09:52

@used2berx Here it is

https://gist.github.com/cmitu/2f1a0dab4e5086b8fca99b081677b6ec

Installation instructions are the same as my initial script, needs the python module mentioned there in order to work.
You can run it with -h to see the usage, by default reads a gamelist.xml from the running dir and exports to gamelist.xlsx there, but you can change the paths via parameters.
Note that the gamelist fragment you posted has some structure errors - mismatched tags or missing closing tags. You need to validate the XML structure before running the export, since it will error out otherwise.

You can add more tags (see line 120) to be parsed and added to the export. I think I covered most of the ones in your example, but you can freely add more.

Used2BeRX · 17 Mar 2018, 13:49

@mitu Thank you so much man. :)

I don't think I'll have a chance to really dive into this and test it for about two more weeks. Because of the nature of the cart work I'm doing and the way that I have to have my current compatibility spreadsheet that I use to do all of my work off of, I absolutely have to finish them before I can move on to anything else. Works's been getting in the way of it this week, but I will have some free time next week to hopefully finish that up and move on to this so I can give you feedback, and ask you any questions I might have about being able to add any missing tags myself in the future.

Once this is all in place I hope @meleu has some free time to put the other tags in his script that will make converting from synopsis.txt to gamelist.xml to spreadsheet and all the way back again without losing any data a snap.

Please bear with me until I can get the time to properly devote to this. :)

Oh... and yeah. It looks like I forgot to add the </desc> tag when I was doing some manual edits to the file above. I wanted to make sure I added all the tags I could currently think that we'd need... even the ones that meleu hasn't put in the script yet. I didn't see any others that were missing the closing tags. We shouldn't have any problems with this once the script is taking care of this and I don't have to manually edit anything in the gamelist.xml.

papinist · 9 Sept 2018, 10:08

I was just thinking about how to have an excel game list.. and voilà! :)

However since I'm really a noob with linux commands, I'm having an error and no xlsx file at the end.

'''
pi@retropie:~ $ python export.py /home/pi/RetroPie/roms/export_list.xlsx
2018-09-09 12:03:13,223 INFO Emulationstation systems file used: /home/pi/.emulationstation/es_systems.cfg
Traceback (most recent call last):
File "export.py", line 584, 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 657, in parse
self._root = parser.close()
File "/usr/lib/python2.7/xml/etree/ElementTree.py", line 1654, in close
self._raiseerror(v)
File "/usr/lib/python2.7/xml/etree/ElementTree.py", line 1506, in _raiseerror
raise err
xml.etree.ElementTree.ParseError: no element found: line 1, column 0
'''
(sorry I can't remember the 'code' character everytime..)