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

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

Science & Industry

 
  • Topic is locked indefinitely.
 

Importing Blueprint Mineral Data into Spreadsheet! *Help*

First post
Author
Previous Topic Next Topic
CJ Alland
CB Trading
#1 - 2015-03-13 13:57:00 UTC  |  Edited by: CJ Alland
I've been struggling for a while to find an efficient way to gather all the t1 blueprint mineral requirements into a datasheet I can reference from in google docs.

I've downloaded countless spreadsheets and other tools trying to tear them apart and see what I can find but near giving up. I never find anything relevant to what I need.
I don't doubt that the 3rd party tools have a database or something with all the blueprint requirements on it reference from, I need to be able to create something similar but to work in google docs.

I downloaded the Tiamat folder from CCP with my knowledge I cant reference anything from it on google docs.
http://gyazo.com/f4de19538bdea47cd9da5a826aa2e426


Thank for any help
Charlie Nonoke
Blue Republic
RvB - BLUE Republic
#2 - 2015-03-13 16:13:49 UTC  |  Edited by: Charlie Nonoke
https://www.fuzzwork.co.uk/dump/latest/ is all you need.
get the industryActivityMaterials.xls.bz2, unzip it on your PC, and import it into Excel/Google Sheets.

It has the comprehensive list of all blueprints, and the materials associated with each activity, be it manufacturing=1, invention=8, etc...
TYPEID column is the blueprint, and MATERIALTYPEID is the id of the material required for that activity.
CJ Alland
CB Trading
#3 - 2015-03-13 18:11:34 UTC  |  Edited by: CJ Alland
Thanks! <3

1 final question though, the data is currently a little unorganised for me atm.

are there any easier way to make this
http://i.gyazo.com/36f55c5031548797be6ea9f4a13697f0.png

look like this?
http://i.gyazo.com/d444b6015ee6ecb23b0a4d22c7cff4fa.png


This is mostly so I can use vlookup to pulling pieces of the data for use in other parts of the spreadsheets.


Failing that just a way to correctly reference from the original would be fine :D

I also only need the manufacturing activity.
CJ Alland
CB Trading
#4 - 2015-03-13 19:36:47 UTC
")
Paranoid Loyd
#5 - 2015-03-13 21:19:04 UTC  |  Edited by: Paranoid Loyd
http://eve-files.com/chribba/typeid.txt

"There is only one authority in this game, and that my friend is violence. The supreme authority upon which all other authority is derived." ISD Max Trix

Fix the Prospect!
Charlie Nonoke
Blue Republic
RvB - BLUE Republic
#6 - 2015-03-13 23:40:17 UTC
CJ Alland wrote:
Thanks! <3

1 final question though, the data is currently a little unorganised for me atm.

are there any easier way to make this
http://i.gyazo.com/36f55c5031548797be6ea9f4a13697f0.png

look like this?
http://i.gyazo.com/d444b6015ee6ecb23b0a4d22c7cff4fa.png


This is mostly so I can use vlookup to pulling pieces of the data for use in other parts of the spreadsheets.


Failing that just a way to correctly reference from the original would be fine :D

I also only need the manufacturing activity.

While the Chribba typeid one is a good source, it hasn't been updated to Tiamat yet. You won't find the Svipul in there.

Use https://www.fuzzwork.co.uk/dump/latest/ and download the invTypes.xls file.

As for your problem in the screenshots.
I would vlookup everything in the industryActivityMaterals where it has a typeid number, and get the string names next to it.
Then on your actual sheet, do a =filter and it'll list every material which is relevant.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#7 - 2015-03-14 16:11:40 UTC  |  Edited by: Steve Ronuken
http://www.fuzzwork.co.uk/resources/typeids.csv is a cut down version of invtypes, with
typeid, typename, typeid

to make bidirectional lookups easy Blink

(updating it is part of my conversion process, so it'll be up to date with the latest conversion)

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
CJ Alland
CB Trading
#8 - 2015-03-14 23:34:14 UTC
Still having some trouble.

I've sorted everything out now except the referencing of the bp data.
http://i.gyazo.com/b7883daa3cf95bcb21f29548efbebce1.png

I'm struggling to reference from it.

To provide a little perspective. I have another sheet that I select an item from a drop down menu, the vlookup from the item idea finds the ItemID, and then uses the item id to search the industry activity materials sheet to find out what materials are needed for that bp.
CJ Alland
CB Trading
#9 - 2015-03-15 00:10:38 UTC
I'm trying to reference from the spreadsheet into another so I know the mineral requirements for that item.

I need to know how to reference from that sheet so I can pull the data from it into another one.

Currently I use things like =vlookup

but that obviously wont work in this case because of the multiple same itemid's on the left.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#10 - 2015-03-15 04:40:43 UTC
=query(materials!A:F,"select C,D where B=1 and A=684",1)

May be of interest :)

(where materials is the name of the sheet you've imported industryActivityMaterials)

https://docs.google.com/spreadsheets/d/1N_uvIWgaYz-6eXLhGQXh7ZNwgS3IRSqOt0eMOuUah6s/edit?usp=sharing

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Tau Cabalander
Retirement Retreat
Working Stiffs
#11 - 2015-03-15 06:17:24 UTC  |  Edited by: Tau Cabalander
I seem to recall the database not being very friendly when something was built from more than just minerals, like components or base items.

This is outdated, but it might be of some help:
Getting needed materials for manufacturing from data dump

Seems like a topic for the EVE Technology Lab forum.
CJ Alland
CB Trading
#12 - 2015-03-15 11:46:26 UTC
Steve Ronuken wrote:
=query(materials!A:F,"select C,D where B=1 and A=684",1)

May be of interest :)

(where materials is the name of the sheet you've imported industryActivityMaterials)

https://docs.google.com/spreadsheets/d/1N_uvIWgaYz-6eXLhGQXh7ZNwgS3IRSqOt0eMOuUah6s/edit?usp=sharing



Thanks this works perfectly.
CJ Alland
CB Trading
#13 - 2015-03-15 12:14:35 UTC
Is it possible to use that formula but add in a different 684, so reference another column such as "F1" for example.#

I've been trying to add it in but with little success.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#14 - 2015-03-15 16:45:01 UTC
=query(materials!A:F,"select C,D where B=1 and A="&A1,1)

I've updated the example.

The query is just a string, so you just need to add bits together for it.

You /might/ want to add an additional column to the material sheet, to take care of the typeid to typename lookup with vlookup, and just retrieve that column as well.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
CJ Alland
CB Trading
#15 - 2015-03-15 16:51:31 UTC
Steve Ronuken wrote:
=query(materials!A:F,"select C,D where B=1 and A="&A1,1)

I've updated the example.

The query is just a string, so you just need to add bits together for it.

You /might/ want to add an additional column to the material sheet, to take care of the typeid to typename lookup with vlookup, and just retrieve that column as well.


Thank you.

Yeah i've added something in.
http://i.gyazo.com/cfba7a86462bcb01e11e9cd78965156f.png