Can you pull Excel data from an external (and dynamic) .csv file?
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?
Solution 1:
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.
Solution 2:
Windows
You can also use Power Query which is a bit more flexible way to achieve the same as in @andy-lynch's answer.
- Get it here
- Install it
- Go to new "Power Query" ribbon tab.
- Either click "From File", or "From Web" and follow the instructions on the screen.
See below animated gif for preview:
MacOS
Power Query is not available for MacOS; nor is the Excel "From Text" open file dialog on MacOS accepting a URL (at least at version 15.33).
To create such a connection one would first have to construct a web query file (.iqy), as explained in this guide, and then import it with the "Data -> Get external data -> Run saved query" menu command.
Solution 3:
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.
Solution 4:
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.