Export full game list data to excel

MAME Gurus come to your aide to help you. Please use the search before adding a topic. This will help cut down on having multiple topics with the same subject.
Post Reply
User avatar
torrentula
Member
Member
Posts: 4
Joined: January 15th, 2020, 12:06 pm

Export full game list data to excel

Post by torrentula » January 15th, 2020, 5:19 pm

Hi there,
I'd need some help regarding 'database' management.
I'm using a frontend with all my emulators so I'd need an excel table of Mame roms to showcase them in a proper way, else my frontend would just show a bunch of zipped codenames sorted alphabetically which is far from ideal.

I need to make my shortcut names as follow :
[year of device] . [name of device] - [proper game name] ([rom code] [rom year]) [COMPATIBILITY STATUS].bat

This helps me a lot with filtering / organizing.

But...

When using the export xml feature of Mame, importing it in excel is quite a challenge. The 69Mb xml file, can't be directly imported to excel due to a parsing error. However I can synchronize it to exctrat it's data. But when trying to expand in excel, the loading screen prompts i'd take more then 350 million rows (sure more, I stopped it after an hour of loading and it was still counting)...
Obviously something went wrong, as it should be 12758 rows long.

Is there an already existing up to date (0.217) excel file list of roms out there with the data I need ? something like http://adb.arcadeitalia.net/lista_mame.php (options>export>csv). But fully covering 12758 roms, not leaving 2720 games without data.
If not, how can you import your xml in excel properly ? Or is there a command line parameter option to Mame that only exports the data I want ?


Using Mame64 0.217 + 0.217 full romset.

User avatar
torrentula
Member
Member
Posts: 4
Joined: January 15th, 2020, 12:06 pm

Re: Export full game list data to excel

Post by torrentula » January 16th, 2020, 10:37 am

Didn't find a way to edit my post.
Please don't hesitate to point me if my post isn't clear (english isn't my first language), and also if I'm missing anything obvious, I'm totally new to Mame stuff. I've seen a few rom management tools but they're looking like they're mostly used to clean up /merge / split romsets. Romlister looked promising but doesn't output the data needed. Or does it ?

Plus, I should'nt need any third paty lister as Mame already exports everything.

Talking list, I just though this pattern would be even better and allow me even more control over my shortcuts :
[year of device] . [name of device] - [proper game name] ([rom code] [rom year]) [SCREEN ORIENTATION], [CONTROLLER TYPE], [COMPATIBILITY STATUS].bat

Still no idea as to how to import this xml in excel ? :mellow:

User avatar
Hierophant
Site Administrator
Site Administrator
Posts: 3491
Joined: December 11th, 2008, 8:41 pm
Favorite Game: Alien vs Predator
Location: LV-426 Acheron
Contact:

Re: Export full game list data to excel

Post by Hierophant » January 17th, 2020, 1:10 am

G'day Torrentula :hello:

I don't use any front-ends, so I'm unable to help with your question. Maybe one of the other guys knows something about it, though.

User avatar
torrentula
Member
Member
Posts: 4
Joined: January 15th, 2020, 12:06 pm

Re: Export full game list data to excel

Post by torrentula » January 23rd, 2020, 12:34 pm

Hi, so I managed to load the list in excel, had to fiddle around. Sorry for the other guys that might look for an easy guide, but you'll have to fiddle yourself too. Basically, load data > xml, transform table and expand what you want. But you might want to expand those tables one by one, else you'll face millions of duplicate rows.

I created all my shortcuts, organized'em as I want, all working good. Now, how do I trim the rom directory to only the necessary ?
I know this has been asked a lot of times but I came across an interesting answer here saying in clrmamepro I could import my list of games and wipe the "useless" data. Using the "Set Info button" in the Scanner. Only problem is I don't see any "Set Info button" in there...

My list of tools to manage my roms is soon getting more cluttered than my romset itself... :lipssealed:
Is there any tool that can take care of parent/bios etc for you when you throw at it your rom wishlist ?

Or what info do I need to extract from MAME's xml datalist to make the connections myself ?

User avatar
Hierophant
Site Administrator
Site Administrator
Posts: 3491
Joined: December 11th, 2008, 8:41 pm
Favorite Game: Alien vs Predator
Location: LV-426 Acheron
Contact:

Re: Export full game list data to excel

Post by Hierophant » January 24th, 2020, 5:03 pm

Can Romcenter do what you want?

User avatar
torrentula
Member
Member
Posts: 4
Joined: January 15th, 2020, 12:06 pm

Re: Export full game list data to excel

Post by torrentula » January 28th, 2020, 12:56 pm

Thanks Hierophant, I have that tool but was'nt able to load another xml to update the database.
I saw we can easily wipe off mechanical/casinos/empties/imperfect and these kind of stuff by the push of a button. The thing is, I wanted to clean my romset myself the hard way from the begining. Processing 12000 roms, comparing snaps, eliminating strange controllers via excel, playtest & made sure all were working etc. I bet the results won't be the same with the automatic tool (I didn't see any controller filter for example) + even with mame's latest filter I still ended up with a few poker games, print clubs, mahjongs, ddrs, I had to take out manually. I don't want to lose all that work.

I have a clean list of rom names I want to keep, both in txt & xml.
I desperately need a tool that could take those & simply add to it the bios & parent roms that the games from my list need.

There's something I must be missing... or I got wrong.

Update : Got it ! The process wasn't obvious, had to backup my roms in a different folder and load only my roms (based on my "cleanlist") in Mame to create a new "exported" xml that Romcenter could load. And it seems that making a "missing" rom list has done the trick actually. Thanks :hooray: Will have to double check tho ^^

User avatar
Hierophant
Site Administrator
Site Administrator
Posts: 3491
Joined: December 11th, 2008, 8:41 pm
Favorite Game: Alien vs Predator
Location: LV-426 Acheron
Contact:

Re: Export full game list data to excel

Post by Hierophant » January 28th, 2020, 9:48 pm

Hey, that sounds promising! :link:

When you said you were having trouble with clrmamepro, I remembered hearing that Romcenter is much easier to use and hoped it might help.

Post Reply