These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Some help with EVE API in Excel

First post
Author
Previous Topic Next Topic
663
#1 - 2014-11-27 04:31:31 UTC
Hello,

I am having some difficulty pulling API data into Excel, since I don't understand the syntax for objects within the XML.


For example, I want to get the account status, so I use:
=importXML("https://api.eveonline.com/account/AccountStatus.xml.aspx?keyID=XXX&vCode=YYY","//result/@paidUntil")



The XML structure is provided on this website. I can't include it here because EVE forums does not let me put html in a post.


What am I doing wrong? I have tried to replace //result/ with //@key/ but that doesn't work either.

I haven't been able to find any Excel discussion around the API except for market related data. Even Fuzzworks just talks about market data and API but I couldn't find more information on character information


Thanks for your help!
663
#2 - 2014-11-27 04:49:50 UTC
Also looks like some of the information described in some third party websites are changed, like here:
https://neweden-dev.com/Category:EVE_API

The developer blog here:
https://developers.eveonline.com/resource/xml-api

It says you can access "the correct endpoints" but it doesn't specify what those endpoints are, or what the content of the XMLs are. Is there an official complete reference somewhere?
663
#3 - 2014-11-27 06:48:19 UTC

Ok, so I switched to Google Docs since Excel has no importxml function.

I am trying to understand how to extract an attribute from a row in a rowset with multiple rows. For example in https://api.eveonline.com/account/APIKeyInfo.xml.aspx there is:


[rowset name="characters" key="characterID" columns="characterID,characterName,corporationID,corporationName,allianceID,allianceName,factionID,factionName"]

[row characterID="XXX" characterName="YYY"  etc.]

[row characterID="XXX" characterName="YYY"  etc.]

[row characterID="XXX" characterName="YYY"  etc.]



To get the first characterName I say:

=IMPORTXML("https://api.eveonline.com/account/APIKeyInfo.xml.aspx?keyID=1111&&vCode=2222","//rowset/row[1]/@characterName")


But in Google docs it says: "Error: imported content is empty"

I am trying to use the Xpath syntax described here:
http://www.w3schools.com/xpath/xpath_syntax.asp


Thanks!
663
#4 - 2014-11-27 17:30:18 UTC

Ok, I found the solution. I guess Google Docs does not like to be case sensitive when referring to attributes.


So the solution would look like this:
=IMPORTXML("https://api.eveonline.com/account/APIKeyInfo.xml.aspx?keyID=1111&&vCode=2222","//rowset/row[1]/@charactername")



Note how "charactername" is all lowercase instead of the attribute name "characterName".
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#5 - 2014-11-27 18:55:07 UTC
If you have an up to date copy of excel: https://support.office.com/client/FILTERXML-function-4df72efc-11ec-4951-86f5-c1374812f5b7

In general, I'd warn you away from using importxml, as it's become somewhat flaky. I'd suggest using a custom function, like some of the ones below.

https://github.com/fuzzysteve/eve-googledocs-script

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
salacious necrosis
Garoun Investment Bank
Gallente Federation
#6 - 2014-11-28 13:52:10 UTC
If you want to go the full featured API approach in Google Docs, you can try this:

https://www.eve-kit.org/#/main/doc/1416194988795

This is a Google App Script library that converts the XML result into a javascript object, which are generally easier to work with. You can also just cut/paste the library into your script project if you want. It's not much more than an expanded version of what Steve does in his scripts. I believe the only API call I'm missing at this point is for Customs Offices (which I missed because they weren't documented on NED :)

Post here if you need help using the library.

Use EveKit ! - Tools for EVE Online 3rd party development
Hel O'Ween
Men On A Mission
#7 - 2014-11-28 15:43:21 UTC  |  Edited by: Hel O'Ween
Steve Ronuken wrote:
If you have an up to date copy of excel: https://support.office.com/client/FILTERXML-function-4df72efc-11ec-4951-86f5-c1374812f5b7


It seems I finally can put my (up until now empty) Github account to some good use: https://github.com/HelOWeen/eveExcel

To the OP: That's a very primitive Excel sample sheet. Look at the module ewaXML for a generic XML retrieval method (ewaGetXML, heavily commented) and how's that used to pull EVE's TQ server status from the EVE API.

EVEWalletAware - an offline wallet manager.
663
#8 - 2014-11-29 01:52:12 UTC

Wow! Thank you Steve, salacious, and Hel. I am now looking through your links.

I was starting to pull in data from SkillQueue.xml.aspx and I'm beginning to notice how flaky importXML() really is. It's really strange too because the function seems to choke on certain attributes like "typeID" or "startSP" but consistently does not seem to have issues with "endTime". It makes sense that a programmatic approach is probably better than hammering an XML hundreds of times.

Besides the single programming class I had to take in college, this is my first venture back into looking at code. It is kind of exciting and scary at the same time.
Hel O'Ween
Men On A Mission
#9 - 2014-11-30 16:28:33 UTC
I can't help you much with Excel's specific object model. But I'd be happy to help you with any generic VBA questions.

EVEWalletAware - an offline wallet manager.