Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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
Using the Data Connection feature you can disable the filename prompt (there's a checkbox at the end of the wizard). At that point you can also set it to auto-refresh every X minutes. Works well for small datasets. – Martin McNulty Feb 24 '15 at 9:10

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

A simpler way (thanks to this post) is to just import the data as you would for a text file you have already downloaded (ie Data > Import Text File) , but instead of entering a file name, paste the URL of the CSV file instead.

share|improve this answer
This is useful. If the remote file changes, I imagine it's not dynamically updating. – editor Jun 17 '15 at 3:58
That is true - although the usual options to refresh on open / every <x> minutes still apply. – Andy Lynch Jun 17 '15 at 10:41

You can also use Power Query which is a bit more flexible way to achieve the same as in @andy-lynch's answer.

  1. Get it here
  2. Install it
  3. Go to new "Power Query" ribbon tab.
  4. Either click "From File", or "From Web" and follow the instructions on the screen.

See below animated gif for preview:

enter image description here

share|improve this answer

import the csv file directly via File->From Text check the connection properties in Data->Connections

see Import a text file by connecting to it

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .