server inventory in Excel

[dwcasey]

We have a script that retrieves server information, places it into a CSV file, then we bring it into Excel.

Since we have various model servers, I want to count how many of this type or that type, then translate a machine model to a common model name. So a 887766 might be a D15 model server and I've 345 of them and put that on a "main" worksheet that gives a high-level view ( management view ) of our inventory.

 

[AnotherHiggins]

Do you have Access or another database program? That would really be more appropriate for what you're after.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[dwcasey]

So if I could get access, what are you thinking? Just use Access to pull in all the data, then use SQL against it?

I'll work to get MSAccess installed on my machine here.

 

[AnotherHiggins]

You can pull the .CSVs into access.

Then just set up a table that associates "machine model" to "common model name".

It would look something like this:
[tt]
machine model common model name
887766 D15
887767 D15
887768 D15
999990 A101
999991 A101
999992 A101[/tt]

Then it's a snap to link the tables (on the "machine model" which they have in common) and filter your imported data by common model name.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[dwcasey]

Well, I got shot down on getting msaccess...mgr said i could use his client which has access, but I'm no access expert and it might take me some time.

So as anotherhiggins said above, if we import CSV, what would be the best way to bring it in? The first column is most important, so could it be our key or index? Then everything else would hang off of it, including model, serial, firmware, slot location, etc.

First, how to tell MSAccess that column 1 is key and then i can build associations after that??

 

[pbrodsky]

Pivot table then, with a translation table and an extra column with a VLOOKUP added to the data each time the .csv is imported. It's more awkward than using Access but will do the job.