| Pages: [1] :: one page |
| Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Johnathan Walker
Caldari
Frontier Elements
   |
Posted - 2010.03.29 12:11:00 -
[1]
Hello! 
I've been scouring the forums (and loveable Google) for a solution to a puzzling challenge: making sense of the corporate assets XML imported into Excel. While I am able to retrieve the XML and get a nice pretty table with lots of rows and columns, the data is pretty much meaningless.
I'm specifically looking for the ability to import the amount of ore/minerals from a container within a corporate hangar at an NPC station; the container does not move or get repackaged (it's there to stay).
If there's any kind soul who could help me out in pulling this data, I would greatly appreciate it; this would save me from having to input it manually (yikes!).
Warmly
"The Bear" JW
|
Dragonaire
Caldari
Corax.
New Eden Retail Federation
|
Posted - 2010.03.29 22:59:00 -
[2]
What you're dealing with is how to store and use hierarchical data in a flat DB table. I'd point you at a couple wiki pages from Yapeal that might be helpful in finding out more about the options available.
http://code.google.com/p/yapeal/wiki/HierarchicalData
http://code.google.com/p/yapeal/wiki/HierarchicalData2
The first one is more general with links to some of the better stuff I found while researching the problem and the second one show how nested sets work which is what is being used it Yapeal since it seemed the best method after looking at all of them and the API. Nested sets can take a little more time to understand but can do things that none of the others can. You may decide you don't need that much with what you're doing but if your project goes like most and just keeps growing you might be glad you "overbuilt" to start with 
--
Finds camping stations from the inside much easier.
Designer of Yapeal for Eve API.
|
Sidrat Flush
Caldari
Audit Services Inc
|
Posted - 2010.03.29 22:59:00 -
[3]
What you will need to do first is to convert the locationId to the station ID http://wiki.eve-id.net/APIv2_Corp_AssetList_XML
When you've done that (by inserting an extra column with the relevant formula) you can then see the following in the order from left to right.
Offices - Divisions, Containers (giant secure containers, station vaults) and ships too - ship fittings and holds.
It's a rather confusing table at first, however what I believe will really help you is to link the sheet to access and via the data dump link the typeID's so you can see the name of each item.
From there you can add Group and Category if you wish.
Seeing a pivot table of all that you own and where is pretty cool though once it's up and running.
Make sure the link you create on excel doesn't auto update though as that in my case clears the data, which is bad.
View The Eve Industrial Organiser Site
|
Johnathan Walker
Caldari
Frontier Elements
|
Posted - 2010.03.30 11:43:00 -
[4]
Edited by: Johnathan Walker on 30/03/2010 11:44:38
Thanks for the replies folks, but to be honest... what are you talking about?  I don't speak Greek
Would either of you or the community at large mind explaining this linking and converting business?
Warmly,
"The Bear" JW
|
Dragonaire
Caldari
Corax.
New Eden Retail Federation
|
Posted - 2010.03.30 15:34:00 -
[5]
You might try looking at the XML for the asset list first before it's added to your excel table. You'll see several rowsets in the result which contain one or more rows of XML data which may also contain rowsets with one or more rows. The first outer rows have the location of the item(s). You have to use the SQL as described in corp asset list to convert that location to a station, system or corp hanger if working with a corp asset list. If you look at any of the inner rows you'll notice they don't include the locationID so you have to use some method to add that information or at least something that let's you figure it out to each row (item) when you add it to your table. If you don't then there's no way to know if something is inside, or outside of a container or even at a totally different station. As stated in the first link I give you  Quote:
The Asset List is in a hierarchical form so to keep the relationships office->hanger->can->item we'll need to find a method to store that relationship in a flat DB table.
What we both gave you are two ways to do that. If that doesn't make sense to you you might want to learn more about them by looking at some of the links of the first page I gave you as well as this Wikipedia article about Hierarchical database model and if you're still confused after look at some or all of that and re-reading what we gave you you might think about finding an existing application that does what you need because I'm sorry to say if this confuses you there's not much hope you'll understand the query you'll need to use to convert all the numbers into names etc that will be required or one needed to find the data you're looking for.
Good luck
--
Finds camping stations from the inside much easier.
Designer of Yapeal for Eve API.
|
Johnathan Walker
Caldari
Frontier Elements
|
Posted - 2010.03.30 16:41:00 -
[6]
Originally by: Dragonaire
If that doesn't make sense to you you might want to learn more about them by looking at some of the links of the first page I gave you as well as this Wikipedia article about http://en.wikipedia.org/wiki/Hierarchical_database_modelHierarchical database model[/url.
FYI: The DBSMAG URL's at the Yappeal wiki are outdated and do not connect.
Originally by: Dragonaire
and if you're still confused after look at soght think about finding an existing application
Good luck
Tried to do just that, but I have not found an application/spreadsheet that can tell me how many units of pyerite I have in our corporate hangar; hence the post. If you know of something that would prove this to be false, by all means I am all ears and eyes.
In short what it sounds like is this:
1) Download the corporate asset XML
2) Create references between each entry's ID to their corresponding values in the data dump, whether via Yappeal, Access, or (worst case scenario) a VLOOKUP.
3) Make useful presentation of the data obtained, for example, a pivot table.
Warmly,
"The Bear" JW
|
Dragonaire
Caldari
Corax.
New Eden Retail Federation
|
Posted - 2010.03.30 17:40:00 -
[7]
Sorry hadn't checked the links lately it seem the first web site no longer exists  I'll see if I can find some links to the same info again but I was just looking at some more articles about this stuff on Wikipedia and someone has been expanded on them so they are probably a better source now and should be used instead.
--
Finds camping stations from the inside much easier.
Designer of Yapeal for Eve API.
|
Sidrat Flush
Caldari
Audit Services Inc
|
Posted - 2010.03.30 17:46:00 -
[8]
if you just want to know what minerals you've got in your corp hangar - just use Eve HQ
simple as.
View The Eve Industrial Organiser Site
|
Johnathan Walker
Caldari
Frontier Elements
|
Posted - 2010.03.31 01:13:00 -
[9]
Originally by: Sidrat Flush
if you just want to know what minerals you've got in your corp hangar - just use Eve HQ
simple as.
While not exactly what I was looking to do, EveHQ opens up so much more functionality that I never even thought of to begin with. Brilliant suggestion Sidrat; thank you kindly!
Warmly,
"The Bear" JW
|
Sidrat Flush
Caldari
Audit Services Inc
|
Posted - 2010.04.02 00:04:00 -
[10]
I know it doesn't give you pivot tables by category (which is a shame), but yes it does do a lot more than it seems to at first glance.
Icons and tabs are the key to using that programme to it's full extent.
Of course you can export many files and import to access and export to excel for the pivot tables you want to glare at in loving admiration.
View The Eve Industrial Organiser Site
|
| |
|
| Pages: [1] :: one page |
| First page | Previous page | Next page | Last page |