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

Stirius · 05/02/07, 5: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, 12:28 PM

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, 1:03 PM

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

Asylum · 05/03/07, 1: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, 2:13 PM

Alright, the excel conversion worked awesomely. Thanks again!

Nomad_Wanderer · 05/03/07, 2:26 PM

Originally Posted by Stirius

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).
post here, if you have any questions.

The csv seems to be seperate by semi-colons, not commas, I had to use the data import and specify semi-colon as the seperator for it to work for me.

Very awesome work though.

I loaded this data into excel, turned on autofilters, and was able to quickly make some farming lists.

darmy · 05/23/07, 5:42 PM

Anyone care to post an updated list for 2.1?

• Disquette · 05/23/07, 5:44 PM

I second the motion, especially if melee stats were also worked in to the mix.

Stirius · 05/23/07, 8:25 PM

just a quick note: I'm working on an updated version with the new 2.1.0 Items and many new Stats (user-selectable!). stay tuned...

Stirius · 05/24/07, 3:50 AM

Okay, here it is: ExtractItem v2.

Download from: http://hosted.filefront.com/stirius/

see the contained README.txt for configuring the parser, weartypes and stats (no compilation needed, only edit .txt files!).

Contained is the raw data from wow.allakhazam.com downloaded yesterday containing about 12k items.

Post here, if you somehow improved the parsing (i don't really understand the differents stats regarding Block, Blockvalue, Shieldblockvalue, Blockrating, etc.) or if you need help.

Darlal · 05/24/07, 11:32 AM

If you check over at Rogue Gear Spreadsheet you'll see we've been toying with the idea of creating a rogue database to do predictions and gear tests. Getting access to the bliz-formatted data from the armory is a great help.

Vielen dank.

• Disquette · 05/24/07, 8:05 PM

I appreciate the code very much, but i get an error when trying to use it. i'm using Win2k (with updated 1.1 .Net framework). Here's a screenshot of the error:

Also, if it helps, I've attached my stats.txt file (i didn't change any of the others)

Stirius · 05/24/07, 8:32 PM

Originally Posted by Disquette

I appreciate the code very much, but i get an error when trying to use it. i'm using Win2k (with updated 1.1 .Net framework). Here's a screenshot of the error:

Also, if it helps, I've attached my stats.txt file (i didn't change any of the others)

alright, I forgot to test that again before uploading.

The solution ist pretty simple:
Insert a newline before the line with the stats in the stats.txt.

• Disquette · 05/24/07, 8:36 PM

You sir (or madam), deserve every positive rep you get for this. Thanks so much!

Elby · 05/25/07, 4:38 PM

Incidentally, I've been using the armory as well and I've noticed it leaves a lot to be desired: various items are not loadable from the armory on an apparently random basis. As I mentioned in another thread, I think this is probably a data-mining protection mechanism much like the servers have to prevent data mining with item links.

Aadar · 06/06/07, 7:53 AM

I have updated the parse.txt to count the different sockets individually with this...

Red:^Red Socket$
Yellow:^Yellow Socket$
Blue:^Blue Socket$
Meta:^Meta Socket$

What I would also like is to be able to extract the text of the Socket Bonus into a column, is there a way of achieving this?

e.g. For the line "Socket Bonus: +3 Spell Crit Rating "

I would like "+3 Spell Crit Rating" written into a column.

Also knowing whether it is a green, blue, purple would be useful by reference to or and so on.

Bury · 07/30/07, 2:54 PM

I can't seem to get dl.bat to work well, getting a bunch of 404 errors when trying to access http://wow.allakhazam.com/dyn/items/inameXX.html

Did Allakhazam change its URLs by any chance?

Bury · 08/01/07, 4:51 PM

Hrm, does anyone have a working parser? This other thread [WoWEquip/Item Database doesn't seem to have something I can use, so for now I'm stuck manually typing item stats into Excel.

Kurthios · 08/02/07, 9:27 PM

Originally Posted by Disquette

Hi all,

based on initial tests, I think I can have a full armory-sourced set of files with each blizzard item. My questions to you are:

would having this data in a local file format be useful to you?

If so, what file type would be most accessible?
- xml (by far the easiest for me)
- Microsoft Access
- Any format Access can export to (including tab delmited text)

XML.

Also, if someone wanted to host an xml-rpc/soap service, that would be nice too. :-)

Thott/allakhazam/armory/whatever are fine, but without being able to mine that information without screen-scraping, it sucks. (*really* wish armory had a soap interface)

• tsigo · 08/07/07, 4:41 AM

Originally Posted by Disquette

+ what are the max number of on-equip / on-use features per item?
- for example, kiss of the spider has 1 on use (haste rating) and 2 on equip spells (hit rating, crit rating). can an item have 3 of one and 0 of another? can an item have more than 3 combined?

Just off the top of my head,

14:11:24 called in wowhead_item::start:324 Item not found!

has a total of 4.

Stirius · 08/07/07, 5:08 AM

Sry, i haven't updated this project for a while, because I'm pretty happy with lootzor.com - World of Warcraft search and rate items - profile your wow character.

The urls on allakhazam have changed indeed.

If there is still demand for my solution, I try to update it in a few days.

• Lurchington · 08/09/07, 12:33 AM

I actually picked this up the other day as something to do while on business travel. Didn't realize there were any further developments since the original post and hadn't seen anyone else's effort til I was ready to post.

Took a quickish but very dirty attempt in python.

run it in the directory with all your XML tooltip files, and you'll get an output.csv file that has each unique field as a column header.

Several sacrifices were made for simplicity, but if I was still maintaining my hunterlist, I'd be able to easily use the output in conjuction with my spreadsheet's sort/filter tools.

rename to .py extension

edit: as far as I can tell, this depends on having something similar to the individual .xml file per tooltip that disquette originally provided

Stigg · 08/16/07, 1:52 PM

Disregard this if this isn't any use to you, but this may have value (if allakhazam links are really dead)

http://www.wowhead.com/?search=nordrassil&xml

http://www.wowhead.com/?item=29356&xml

Rezarel · 08/16/07, 2:18 PM

Originally Posted by Bury

I can't seem to get dl.bat to work well, getting a bunch of 404 errors when trying to access http://wow.allakhazam.com/dyn/items/inameXX.html

Did Allakhazam change its URLs by any chance?

I think the URLs just changed from inameXX.html to iXX.html

Stirius · 08/16/07, 7:06 PM

Originally Posted by Rezarel

I think the URLs just changed from inameXX.html to iXX.html

Correct. I changed the links and it seems to work (only the .bat file included):
filefront download-link