1. I have corrected the e-mail settings so that outgoing e-mails from these forums should be sent now. If you tried to Register or Reset your Password, please try again!
    Dismiss Notice

Excel Integration

Discussion in 'Census: General Discussion' started by Lucutious, May 28, 2021.

  1. Lucutious

    Lucutious New Member

    Has anyone successfully used Excel Power Query or other data tools to access census?

    If no, is any census expert willing to work with me to develop an Excel based solution? I can imagine a littany of different uses and implementations that would be possible if I can simply make the data connector work.

    Thanks in advance,
     
  2. Lucutious

    Lucutious New Member

    I should add, if I can make this work I'll release all tools to the community for their use. I have no desire to keep it to myself.
     
  3. Alarra

    Alarra Active Member

    I had a look at it and was able to grab census data into excel but it was messy.
     
  4. Lucutious

    Lucutious New Member

    Actually, Feldon, Power Query and Power Pivot can handle multi dimensional just fine. You don't even have to load the data into sheets, just get the datasources loaded then load up power pivot to start displaying in meaningful ways.

    The Proof of Concept I was thinking of would be basically a personal replication of tulavvy, but for the current expac. Perhaps looking at who's missing what for shinies this expac, or who's missing what flawless kills, etc. Once I get the basics working the possibilities are, as I'm sure you can imagine, endless.

    Alarra, which data tool did you use to import? Messy is fine, I can clean it up with Power Query.
     
  5. Feldon

    Feldon Administrator Staff Member

    I'm happy to help write any queries you need, combining data from different collections into one using c:join. It's tricky but efficient.
     
  6. Alarra

    Alarra Active Member

    I just used the Data Tool in Excel.
    You click the Data tab, then click "From Web" then input your census URL.
    Such as
    http://census.daybreakgames.com/s:YOURSERVICEID/get/eq2/character_misc?id=450972114911

    Feldon could probably give you a query which would resolve the collection item names as well, but for now, I will just do with the ids.

    My Office is in Japanese so you will see some different language stuff just match the icons with your interface.

    Would give me my character info with a collections list.
    From there you can click the links on your "list"
    upload_2021-6-2_21-25-27.png

    Then click on Record
    upload_2021-6-2_21-26-3.png

    Maybe you want Collection List
    upload_2021-6-2_21-27-12.png

    Now you have your Collection List
    upload_2021-6-2_21-28-6.png

    Click on your Change to Table Button
    upload_2021-6-2_21-28-37.png

    Leave as the Defaults and Click OK
    upload_2021-6-2_21-29-14.png

    Then you will get your column with the initial records.
    Click on the expansion button
    upload_2021-6-2_21-33-15.png

    Select all the fields and click OK
    upload_2021-6-2_21-33-36.png


    On the Left you will have the Collection Name, on the right you will have the Collection Members
    upload_2021-6-2_21-34-25.png

    Click on the Column1.Item_List expander and click expand to new rows(I think that is how you would say it in English)
    upload_2021-6-2_21-36-21.png
     
    • Appreciation Appreciation x 2
  7. Alarra

    Alarra Active Member

    Then you will have the records
    upload_2021-6-2_21-37-59.png

    Now, you can click the expander and select all and go OK
    upload_2021-6-2_21-38-35.png

    There you will have your collections on the left and the collectables on the right
    upload_2021-6-2_21-39-22.png

    Then double click on the column headers and give them easier to understand names
    upload_2021-6-2_21-41-48.png

    Then click Close and Import
    upload_2021-6-2_21-42-43.png


    And you now have Census Data in Excel
    upload_2021-6-2_21-43-17.png
     
    Last edited: Jun 2, 2021
    • Appreciation Appreciation x 2
  8. Feldon

    Feldon Administrator Staff Member

    This is amazing!
     
    • Agree Agree x 1
  9. Wulfgyr / Erytheal

    Wulfgyr / Erytheal Active Member

    This has me wondering if you could pull data from Census into PowerBI. I need to learn more about that kinda stuff though, not even close to being competent with that sort of data manipulation. @Alarra, that Excel import is freakin' amazing. Well done!
     
  10. Alarra

    Alarra Active Member

    The data is JSON or XML so you could pull it from any program that accepts those formats.
     
  11. Lucutious

    Lucutious New Member

    Yes, you can. That is what I'll be doing. I'll have a prototype soonish. (Well I'm starting in PowerPivot, but may need to roll to PowerBI.)
     
    • Informative Informative x 1

Share This Page