Jump to content
Search In
  • More options...
Find results that contain...
Find results in...
Sign in to follow this  
TimeOfDeath

2011 wads - excel file to webpage help

Recommended Posts

excel file: 2011stats.xlsx

I made an excel file from fullsort that has all released/updated wads from /idgames during 2011 (Dec10 2010 - Dec8 2011). There are 388 entries.




- Does someone want to make or know how to make a webpage out of the excel file?

- On the webpage, how do you make the column titles clickable, so that when you click them it sorts all the data by that column, ascending and descending?

- How do you make links from the data in the ID NUMBER column, so that you just press the number and it sends you to the /idgames page?

Share this post


Link to post

Perhaps your best chance is to export the data into a Word document (as a table) and use the doc-to-html functionality therein. Although I think that there is an export-as-html option buried somewhere in Excel, too.

Share this post


Link to post

For anyone who knows their way around Rails, Sinatra, PHP or Django, the best data format would undoubtedly be a CSV (comma separated values) file, which is a simple text file you can extract to from any half-decent spreadsheet program.

Share this post


Link to post

Neat. Thanks for making this. Now to see if I missed anything worth checking this year...

Share this post


Link to post

I saw one .zip in there near the bottom claiming to be from August 2010, so that probably shouldn't be there. Also the ordering is a bit odd, as it goes from Jan-Dec 2011 and then jumps to Dec 31st 2010 and works backwards to the 8th or thereabouts.

Share this post


Link to post

Fullsort lists wads that were released or updated. So that one wad from Aug2010 was updated in 2011 (I added it to the list anyway because there's no entry for it during it's original upload date, fullsort only lists the most recent update date). Maybe there could be an "update date" column too.

I started at Jan 1st and was going to do just 2011, but I didn't want to wait for the end of the year, so I did the "cacoward method" instead.

You can sort any column in excel with Data -> Sort.

Share this post


Link to post
TimeOfDeath said:

- Does someone want to make or know how to make a webpage out of the excel file?

Load the spreadsheet into OpenOffice 3.3 (freeware) and save as an HTML file.



result : http://www.mediafire.com/?a8og6b6mh5uxtah

I don't have a current Microsoft Office, so I don't know from which version on this feature is available.

TimeOfDeath said:

- On the webpage, how do you make the column titles clickable, so that when you click them it sorts all the data by that column, ascending and descending?

I imagine that it could be done with some scripting, but I'm not up to that.

TimeOfDeath said:

- How do you make links from the data in the ID NUMBER column, so that you just press the number and it sends you to the /idgames page?

That can be done easily, but it's tedious and time consuming. Each link must be inserted manually.

Share this post


Link to post

I'm slightly confused as to the point of doing this. Am I to understand that you've gone to the effort of creating and maintaining this file by hand? The majority, if not all of this data, is already available through the idgames web interface and is extracted automatically from the files, so it should theoretically be trivial to export it to CSV format. Compare the spreadsheet with the /newstuff interface for example.

On a side note, does anyone else find it annoying that such thing as a "idgames ID number" even exists? The Doom wiki for example has a template that makes use of it. I really wish the idgames web interface could be switched to use the actual filenames of the files rather than just arbitrary numbers. It should be "http://www.doomworld.com/idgames/?path=utils/sound_edit/bsdmidi.zip" rather than "http://www.doomworld.com/idgames/?id=15233"

Share this post


Link to post

I've been playing around with some javascript stuff recently, and I had a couple of hours free yesterday, so here you go:

http://www.brillskills.com/doomdata/

Probably won't work right or perform well in older browsers. Use a recent version of Chrome or Firefox and you'll be fine.

Share this post


Link to post

http://chicago.sourceforge.net/xlhtml/

Edit: yeah, I don't like the "id" thing either, as it's not canonical (it doesn't exist on the FTP servers). A few months ago someone wanted to write a Doom shell that pulled ratings from the page, but he couldn't find an easy way to map the path to its id (which only exists in the doomworld database).

Share this post


Link to post
kb1 said:

What is this "fullsort"?

Fullsort is a chronologically sorted list of the archive's contents in a gzip archive, which (AFAIK) is updated daily.

Share this post


Link to post

LOL @ bottom of fullsort.gz:

1993/02/03 260606 historic/doom0_2.zip
1993/02/03 540 historic/doom0_2.txt
1991/12/03 5022 utils/graphics_edit/cmappov2.txt
1991/01/16 2097 levels/doom2/deathmatch/a-c/bull2.txt
1980/01/08 2018 levels/hexen/deathmatch/m-o/nexeh.txt
1980/01/03 3207 levels/doom2/deathmatch/v-z/wargames.txt

Share this post


Link to post
Jonathan said:

That's perfect, thanks a lot! Is it possible to add a "input date" and "input order" column as well? ("input date" would be useful for the rating/vote columns, "input order" would be useful for total number of entries)

fraggle said:

I'm slightly confused as to the point of doing this. Am I to understand that you've gone to the effort of creating and maintaining this file by hand? The majority, if not all of this data, is already available through the idgames web interface and is extracted automatically from the files, so it should theoretically be trivial to export it to CSV format.

Yes, I did it by hand. It would be much better if most of the data could be extracted automatically. Do you know how to do that?


I just thought it would be cool to have all of this year's wads on one page where it's easy to sort through multiple categories. Consider it the 2011 Participation Awards. :)


Bloodshedder - could this be hosted on doomworld when it's done?

Share this post


Link to post

What other archives did you have in mind? Pros about idgames are that it's big, easy to navigate, and I don't have to download any wad files to view data. Otherwise, it would take much longer to input by hand.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  
×