Importing a csv file to an existing excel spreadsheet

[wotsit]

Hi all.

I'm after a bit of help please as i'm a bit stuck.

Our server monitoring software kindly produces a csv file containing every server’s name, the size of their disk drives and the amount of disk space that is free.
I have created a working spreadsheet which will generate broken down statistics and graphs showing trends, once the main worksheet is updated with the latest figures.

I work for a very large company with a fairly big datacentre and it will take a large amount of my time for me to manually go through the csv file and update every server's free disk space every week on the spreadsheet. The management require this weekly report, so it’s not something I can get out of.

The data in the csv file will change every week, but the column headings will remain the same. The data and also it’s order is also likely to change if additional servers are placed in our domain or if any servers have been removed. Is there a way for me to automate this process somehow?

Here's an example of the Spreadsheet:

A B C D E F
1 server name Description week1 week2 week3 week4
2 server a C:\NTFS 80% 60% 60%
3 server a D:\NTFS 50% 50% 50%
4 server b C:\NTFS 40% 50% 60%
5 server b D:\NTFS 80% 60% 60%
6 server c C:\NTFS 50% 50% 50%
7 server c D:\NTFS 60% 60% 60%
8 server d C:\NTFS 80% 60% 60%
9 server d D:\NTFS 60% 60% 60%


Here’s an example of the csv file:

A B C
1 server name Description % free
2 server a C:\NTFS 80%
3 server a D:\NTFS 50%
4 server b C:\NTFS 40%
5 server b D:\NTFS 80%
6 server c C:\NTFS 50%
7 server c D:\NTFS 60%
8 server d C:\NTFS 80%
9 server d D:\NTFS 60%


Is it possible to get excel to reference the csv file, match relevant criteria and write the data back to the spreadsheet somehow? Eg. Look in the csv file for the row that contains both server a AND c:\ntfs and write the value in the % free column to the F2 cell in the spreadsheet? The row in the csv file that contains both server a AND d:\ntfs will write the value to F3, both server b AND d:\ntfs in F5 etc.


Thank you,
H

 

[combo]

To import data into existing excel file you can (up to excel 2003 menu):
1. either use data>import external data>import data, define what and where import,
2. or data>external data>new database query. May require adding new text-type data source.

Looking at the report it seems that if your csv file contained weeks (week1, week2,...) you could easily prepare report using pivot table and external data source.

combo

 

[wotsit]

Thank you for your help Combo. I've tried importing the data that way, but it will still just import the data in the order that it is in the csv file, which may not be in the same order within the spreadsheet.

Is there any way of getting excel to look in the csv file for the contents of Column A and Column B in the spreadsheet and then write the data from Column C in the csv file to the appropriate cell in the spreadsheet, e.g. F2 onwards for this week, G2 onwards for next week and H2 the week after etc.

Does this make sense or am i not explaining myself properly?

Thanks again.
H.

 

[SkipVought]

How many rows of data in the import sheet?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

wotsit,

Have you considered (if you can) moving this from Excel to Access?

From what you're saying, you could do this altogether differently and without as much headache.

Something like:

[ol][li]Create a "master" table that includes the original identification info for each record - a unique ID field, whatever you're wanting to compare against.[/li]
[li]Create another table that contains all the values you'd normally have in columns C, F, G, H, etc, as you mentioned in the last post.[/li]
[li]Then each week when you've got the new data, you could have an import routine setup that imports the necessary data into an "import" table, and then query your existing "historical imports" table against the "import" table to update the historical table in such a way that your correct records match up.[/li]
[li]Then for any reports you need - Excel, Snapshots, etc, you could build/sort/group the data any way you needed.[/li]
[/ol]

This is just a rough outline and not detailed at all at what you could do. If this is an ongoing thing in Excel, then it may very well get cumbersome to handle within Excel, and you'll be more limited than an all out Access database.

Anyway, those are my thoughts on the subject.

--

"If to err is human, then I must be some kind of human!" -Me

 

[lowbk]

if i am reading this correctly, it means you may need to write a macro or something, so that the spreadsheet knows which column to write the "% free" each week.

i have an idea, not sure if it works.

1) for the various csv files, give them fixed name eg wk1.csv, wk2.csv, wk3.csv etc
2) in your spreadsheet, create column A & B as you did in your example
3) for column C, use the formula ='[wk1.csv]Sheet1'!$C$2 for wk1 %free, it should get 80%; use wk2.csv for columnD, and so on.
4) copy the formula to the rows and columns you need

good luck

 

[wotsit]

SkipVought, at the moment there is 470 rows of data but this will grow a fair bit in the future.

kjv1611, thank you for your suggestion. It probably does make more sense for it to be held in a database as the spreadsheet will get quite large in size. I will give that a go and see how I get on.

Thank you all for your suggestions, I really appreciate your help.

Thanks
H

 

[SkipVought]

I would set up a sheet just for IMPORT. The IMPORT querytable would just need to be refreshed as you get new .csv files.

I would set up another sheet for ALL THREE COLUMNS of ALL DATA, along with a new column for ImportDate.

I would then APPEND (you could simple COPY 'n' PASTE) the import data to the bottom of ALL DATA, along with the ImportDate.

Now you can do an easy PivotTable to DISPLAY the data in the format you originally posted.

BTW, having ALL your data in ONE TABLE, will greatly enhance the kinds of things that is possible to do with ALL the data -- historical statistics, for instance.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]