Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've got a script that outputs a number of performance metrics for a computer into a .csv. The Excel does a basic stats check for standard deviation, etc.

I realize you can import .csv files into Excel, but what I'm trying to do is read that data dynamically into my Excel model without having to re-import and re-model each time.

Is there a way to have a cell/table reference an external .csv without reimporting each time?

share|improve this question
    
When the file is updated, is it being appended to, or overwritten? –  Benjamin Anderson Jan 19 '11 at 23:23
    
Completely overwritten. But I guess I could modify it to work either way. –  editor Jan 20 '11 at 0:03

2 Answers 2

up vote 4 down vote accepted

Easiest way of doing it is to import the Data into Access or SQL Express, and then use the Data Connection features and functions in Excel. After the initial setup, all you would have to do is click Refresh All and it will pull the the data and recalculate the values.

You can directly import the CSV with the Data connection feature, but it is going to want to pull the data in and add it to a Worksheet. This will work, so long as you import it to a secondary Worksheet and leave your formulas and calculations on another sheet. When you click Refresh using this method, it's going to ask for the file name, and then re-import it using the same settings as originally used. This method is also usually slower, or unusable, with large datasets.

share|improve this answer

The question is "How do I reference data in an .csv file?

My way would be to cut out the .csv file and use a Macro containing VBA and ADO that receives the data and puts it directly into a worksheet in Excel. Any ODBC compliant database can be your source.

Excel 2010 has Data>>Get External Data - from various data sources. However, I prefer to write my own code because I have absolute control and can format the results in the macro. Refresh is at the click of a button and you can do anything with it.

I could let you know some specifics if you tell me what database the metrics are coming from.

PS Look at the list of drivers in Data>>Get External Data>> Existing Connections>> Browse for More >> New Source >> Other Advanced >> Next which leads you to the Data Link Properties list. You then have to know what source database and what driver to use. You can email me at cliffbeacham at hotmail with more details.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.