Item database project (for all these spreadsheets) - Page 2

Lurchington · 04/09/07, 9:46 PM

link received and downloaded.

Good suggestion on the part of Disquette to post any parsing efforts so as not to duplicate work.

I think I'd like to look at a method of implementing some of wowhead's more useful filtering options, and make that part of the conversion to flat file. For example armor type, usable by, required level, item level.

edit: this will be primarily something I proscrastinate with during work, so anything that happens will come about during that time.

Disquette · 04/10/07, 7:36 AM

Yeah, to follow what lurchington said, please use this thread to let people know how far you are in the parsing creator. We have 3 or more people working on this individually. Alternatively, if we dont want to bog down this thread/forum with extra posts, anyone that wants can email me (disco at discofiend dot com) and I'll get us all on the same email group.

Grizlock · 04/10/07, 8:15 PM

I have a script running to parse each file, and create a tag->value combination for every element in the xml file. Once I have every possible tag, I'm going to run a script to generate a flat file with every tag as a column, and then parse every file again to place each value in the correct field.

Actually I'm starting to regret not combining it all into one pass since there are so many files. Nevertheless, I'll let everyone know when I get it all into a flat file database.

Disquette · 04/10/07, 9:16 PM

Hey man, awesome to hear. If you still have time to stop it (dont know if you're talking hours or just minutes), you might want to grab just the last 3k files or so. Those are the ones added to the game most recently, and especially if you also filtered out non-equipable and only greens or better, i'm guessing you get every tag already.

I'm interested in how you're accommodating things such as the multiple "on use" bonuses.

Lurchington · 04/10/07, 10:05 PM

I'm not nearly as familiar with XML syntax as I probably should be. After fiddling with it for a good amount, I think I might be able to more easily jump in when it's a flat file.

Any chance of posting the scripts after it completes? I'm curious to see how you handled some of the stuff I ran into.

edit: oops. didn't refresh for a bit, and caught disquette's post

Disquette · 05/02/07, 8:35 AM

There was a good bit of discussion going on here, which dropped to nothing after April 11th. Did anyone get a full database parser running?

If not, I'll write one, but I don't want to reinvent the wheel.

The Iron Colonel · 05/02/07, 8:52 AM

I'm not currently developing a spreadsheet; however, in discussions regarding Cheeky's sheet he alluded to a master/standardized gear list that the numerous spreadsheets for various classes could reference. I don't know if the parser could be configured to create tab-delimited files (which could be converted easily to XLS), but I thought I would bring it up here to show that there is at least some interest in this type of project.

Farstrider · 05/02/07, 9:27 AM

XML is usable by excel, correct?

I think that most people would want to set up some sort of lookup for an items stats within a spreadsheet, so an XML that I could export into an excel worksheet, or even just a giant pivot table, would be amazing.

Stirius · 05/02/07, 1:16 PM

I have written a tool in dot.net that parses some basic stats from http://wow.allakhazam.com/dyn/items/iname1.html etc.. I simply wget these 20 .html pages (see the overview at http://wow.allakhazam.com/dyn/items/index.html), copy these into one file and stick it into my parser which outputs an xml-file with all items available in wow. The file looks like this:

  <Table1>
    <Name>Ata'mal Crown</Name>
    <ID>30922</ID>
    <Slot>Head</Slot>
    <WearType>Cloth</WearType>
    <Armor>114</Armor>
    <Intellect>16</Intellect>
    <Spirit>14</Spirit>
    <Stamina>21</Stamina>
    <SpellDmg>46</SpellDmg>
  </Table1>

It currently exports only these attributes visible in the example, because these are the only stats i needed as a mage :-)
The final .xml-file is about 2.5mb large and can be directly read into excel, where i budgeted my stats and finally got a score for each item.
If anyone is interested, let me know (the .xml or the parser source). Adding additional stats like strength is no problem at all, set-boni and special stuff (e.g. trinkets) is more difficult so i didn't bother.

Disquette · 05/02/07, 2:37 PM

I would love to have a copy of the parser source! Also, I'm interested in how you put this in excel. When i save the code snippet you provided as an xml file and opened with Excel, I simply got a cell with the values entered as text.

Is there a correct way to get this data into excel so that each attribute is busted out into its own column?

Stirius · 05/02/07, 4:57 PM

Originally Posted by Disquette

I would love to have a copy of the parser source! Also, I'm interested in how you put this in excel. When i save the code snippet you provided as an xml file and opened with Excel, I simply got a cell with the values entered as text.

ok, i will clean it up and post it here in a few days.

Is there a correct way to get this data into excel so that each attribute is busted out into its own column?

well i posted only a snipped of the file. Try this snippet, save it as test.xml and just open it in a recent excel version (edit: i tried it with Excel 2002 and OpenOffice 1.1, that didn't work, but Excel 2003 (XP?) did it correctly). It should fill the columns like you wanted. If you dont like this strange xml markup (dropdowns, blue border etc), select everything and "copy"-"paste contents" into a new sheet.

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table1>
    <Name>Ata'mal Crown</Name>
    <ID>30922</ID>
    <Slot>Head</Slot>
    <WearType>Cloth</WearType>
    <Armor>114</Armor>
    <Intellect>16</Intellect>
    <Spirit>14</Spirit>
    <Stamina>21</Stamina>
    <SpellDmg>46</SpellDmg>
  </Table1>
  <Table1>
    <Name>Battle-Mage's Helmet</Name>
    <ID>29773</ID>
    <Slot>Head</Slot>
    <WearType>Mail</WearType>
    <Armor>465</Armor>
    <Intellect>18</Intellect>
    <Stamina>25</Stamina>
    <SpellDmg>30</SpellDmg>
    <SpellCrit>26</SpellCrit>
  </Table1>
</NewDataSet>

If someone finds a way to import data from a file that looks similar (otherwise I will try a simple .csv) into an older Excel version and/or OpenOffice, please let me know. It was just the first option I tried and it just worked...

Stirius · 05/03/07, 11:28 AM

okay, I modified the tool, so that it also exports a simple .csv file (that Excel 2002 can open, and it is alot smaller). I recommend to use the .csv-file for Excel-import (simply double-click the file).
To download the dot-net project with source, executable and the data pulled from allakhazam yesterday, go to to filefront here:
http://hosted.filefront.com/stirius/
and download ExtractItem_v1.zip (about 1mb).

The database contains about 12k items but only with a few stats. The data.csv looks like this:

Name;ID;Slot;WearType;Armor;Intellect;Spirit;Stamina;SpellHit;SpellDmg;SpellFrost;SpellCrit;SpellPen;Heal;mp5;Sockets
68 TEST Green Cloth Head of;26132;Head;Cloth;108;;;;;;;;;;;
Abjurer's Hood of;9940;Head;Cloth;53;;;;;;;;;;;
Admiral's Hat;10030;Head;Cloth;51;;;;;;;;;;;
Adventurer's Bandana of;10261;Head;Leather;130;;;;;;;;;;;
Adventurer's Pith Helmet;9420;Head;Leather;94;;7;14;;;;;;;;
Alabaster Plate Helmet;8317;Head;Plate;419;;15;16;;;;;;;;
Amethyst Beholder Eye of;31220;Head;Cloth;120;;;;;;;;;;;
Ancient Crown of;15602;Head;Mail;202;;;;;;;;;;;
Ancient Terokkar Hood;31784;Head;Leather;188;;;21;;;;;;;;
Ango'rosh Helm of;24930;Head;Plate;638;;;;;;;;;;;
Ango'rosh Souleater's Cowl;25558;Head;Cloth;103;25;16;25;;28;;;;;;
Antlers of Malorne;29093;Head;Leather;271;25;19;22;;32;;20;;;;2
...

the xml-file like this:

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table1>
    <Name>68 TEST Green Cloth Head of</Name>
    <ID>26132</ID>
    <Slot>Head</Slot>
    <WearType>Cloth</WearType>
    <Armor>108</Armor>
  </Table1>
  <Table1>
    <Name>Abjurer's Hood of</Name>
    <ID>9940</ID>
    <Slot>Head</Slot>
    <WearType>Cloth</WearType>
    <Armor>53</Armor>
  </Table1>
  <Table1>
    <Name>Admiral's Hat</Name>
    <ID>10030</ID>
    <Slot>Head</Slot>
    <WearType>Cloth</WearType>
    <Armor>51</Armor>
  </Table1>
  <Table1>
    <Name>Adventurer's Bandana of</Name>
    <ID>10261</ID>
    <Slot>Head</Slot>
    <WearType>Leather</WearType>
    <Armor>130</Armor>
  </Table1>
  <Table1>
    <Name>Adventurer's Pith Helmet</Name>
    <ID>9420</ID>
    <Slot>Head</Slot>
    <WearType>Leather</WearType>
    <Armor>94</Armor>
    <Spirit>7</Spirit>
    <Stamina>14</Stamina>
  </Table1>
  <Table1>
    <Name>Alabaster Plate Helmet</Name>
    <ID>8317</ID>
    <Slot>Head</Slot>
    <WearType>Plate</WearType>
    <Armor>419</Armor>
    <Spirit>15</Spirit>
    <Stamina>16</Stamina>
  </Table1>
...

post here, if you have any questions.

Disquette · 05/03/07, 12:03 PM

You sir, ROCK. Thanks much - I'll be looking at this tonight.

Asylum · 05/03/07, 12:25 PM

Even though you already worked around the older versions thing by going to CSV, here is some follow-up:

Excel 2003 is where XML really starts to enter, as far as I know. It's pretty well-supported in 2003, and 2007 takes it a step beyond that with actually having the native Excel file format be an XML package.

In 2003 (and 2007, anything prior, at least at my job, is really non-existent) you can actually create an Excel XML map of the file. What this does is if you tell it to create an XML map, and point it to your XML data, it will auto-generate a schema for you and create a table of items that represent the elements of your XML. You can literally then drag these elements onto the worksheet, and Excel will automatically generate pivot tables of all of the data. This allows you to do formatting, etc. etc. and have a baseline template to act from. Then, once the map is done, it's literally a matter of telling it to import an actual XML data file, and boom, all the fields you dragged over are auto-populated based on the XML data.

It's trivial in .NET to reference Excel as an object and do all of this automatically, spitting as an end-result an Excel file, formatted, and with all the data present from whatever XML file you point it to. You could also create Excel macros that will automate pulling XML data in even more easily.

I just wanted to illustrate this mapping approach, as it is much, much, much more powerful than just a simple XML import.

Disquette · 05/03/07, 1:13 PM

Alright, the excel conversion worked awesomely. Thanks again!