Gamelist Excel export script

mitu · 18 Feb 2018, 19:59

@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 · 18 Feb 2018, 20:08

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 · 18 Feb 2018, 20:10

@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 · 18 Feb 2018, 20:26

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 · 18 Feb 2018, 20:32

@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.

detron · 18 Feb 2018, 21:36

@mitu

here you go.

link

let me know if it fails, I will fix it.

mitu · 19 Feb 2018, 17:29

@detron Thank you for the file. The problem is seems was in the logging part, which was tripping on the game's game. It should be fixed now (URL updated in the 1st post), so you can re-download the script and try again with your initial gamelist.

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

detron · 21 Feb 2018, 14:27

@mitu

thank you for the help others may be saved by your edit, but I have zero roms for that system. I just thought the idea of a gamelist with all images was brilliant. add your few roms, and the art would show up.

meleu · 21 Feb 2018, 19:31

I have a strong conviction that @Used2BeRX will love this tool! :)

Used2BeRX · 22 Feb 2018, 13:43

This looks AWESOME!!!!! @mitu
I can't wait to try it out. I actually asked a few months ago if somebody could make something EXACTLY like this.

If this works the way that I think it will from your OP, I think this is going to end up saving me countless hours on my work in the future, and I should still be able to get a ton of work out of it for the NES project I'm currently working on.

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).

Thanks for the heads up @meleu.

If this is something that I can use like I think it is, I was hoping that when you get time to do some more things with our script that you might be able to make that reverse script we were talking about that would then take the gamelist.xml and convert it back to the initial synopsis.txt files.

That way when all of the work was done I could re-generate the edited synopsis files for the XBox ready release as well as the Pi Zero and Pi 3 release without having to edit all of them by hand.

Amazing. Thanks for sharing mitu!

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!