EVE Technology Lab

Hi,

So as the title says I'm trying to come up with a spreadsheet to help manage industrial and production related tasks as well as keep an accurate inventory of assets needed to complete builds. I did DL the crucible 1.6 database and related zip files as well as MS SQL server 2012, hell if I know how to use that.

So my question is what would be the best way to going about getting data into a workable spreadsheet form without having to input everything manually.

I'm assuming MSSQL is the best way to go?

I agree with above poster. Based on your strengths you will determine how you write it. I guess the real question is what your good at? SQL? Excel Macros? What? :P

CSV is a good idea, cause most of the time it works pretty well between different programs.

Ok, Excel can do what you're looking for, but you're going to end up getting a crash-course in some decently advanced Excel techniques. As long as that doesn't scare you too much, and you're willing to read the Help files and ask for help if still stuck, you should do ok.

The basic structure you're going to want is a big workbook with multiple pages. You'll want to have one or more main page(s) which you pick what you want to do and set the skills/prices/etc and then one or more data page(s) with the actual database information on it.

For the dropdowns you will want to use "Data Validation" to create the dropdown list (by using a Named Range consisting of the BPO names) and then VLOOKUP or HLOOKUP (depending on your chosen layout) to pull in the values from the database sheet(s) by having Named Ranges that the lookup functions run on.

I would suggest looking up and reading about all of the terms I capitalized in that last paragraph, they're the main tools you will use in Excel to do this.

To get the data from the database, I would suggest somehow getting a text dump of the database (I don't have MSSQL, so I'm not much help with that specifically) and then opening a good text editor (I recommend Notepad++) and formatting it into CSV format

Example CSV:
valueA1,valueB1,valueC1
valueA2,valueB2,valueC2
valueA3,valueB3,valueC3
(the commas between cells and line returns between rows are the important thing)

Hint: stuff like 'Replace all: "], [" with ","' is good for changing stuff into CSV format.

Once you have stuff in CSV format, you can then import it into your Excel workbook as your database page(s).

Yes, Excel can do all of that stuff. It's not always the most efficient of things, but it will do all of that if you want to enough. It will get a bit bigger and bloated, but not too too much.

Personally I would probably do something like writing a Python script to check assets and pull down API info and dump them into a CSV file when you get to the point of needing API calls, but that's a bit more advanced than you want right now.

For now I would suggest working in Excel to get the basic functionality working and then expand from that when you want another feature added in. It might be better in the long run to make a fully standalone program, but it's also a much more advanced project. Excel will do the job just fine though, and is much easier to work with if you're not an experienced programmer.

Interesting, could I ask what your audience is? Or is this just a script your writing for yourself?

Then it just depends on how lofty of goals you have. Excel will go a long ways and do quite well, but it tends to be a bit more about quick and dirty system that works than a highly polished system. It just depends on how polished of a final system you're looking for and how much work you want to put into it.

I would say just stick with Excel, it'll do the job just fine and is much easier to learn.

That sounds like a good plan. And you don't seem to be having any issues asking for help on here

Typically, this forum will be a better resource for your questions about the Eve-specific things and help files/google will be your best non-Eve resources, but most of us are fairly knowledgeable and willing to answer questions for someone trying to figure stuff out (especially because Excel is a pretty well known program around here, lol).

Personally I use Access to create the queries in order to obtain the data I need to write my worksheets. Although I haven't made the Access database available (yet) the worksheet is fully open and editable so you can see all the formulas and links.

I will be making the access db available but will wait till the new data dump is out to do that.

Good luck, have fun and if you're not planning on releasing your work, keep it simple.