Wednesday, August 18, 2010 at 11:01PM 
If you use generated reports from websites that contain important operational or reporting data, it is often important to bring that data into an Excel Spreadsheet. If you do that regularly, you might find yourself bringing that data into a spreadsheet on a regular basis. This can be tedious, because you have to do a repeated number of steps that require regular maintenance at all points:
If you find yourself doing this kind of thing on a regular basis, you might consider an alternate approach- connecting to your CSV/excel spreadsheet reports by a refresh-capable data connection. This means you don't have to download the data manually, you don't have to delete the old data version from your spreadsheet, and you don't have to import the new data and reformat your spreadsheet once again.
All this can be automated, once you setup your CSV/excel data to be an external data source. Below are some easy steps to doing this. I have included a lot of screen shots, but this process is really quite quick and simple. After you have done it once, you won't have repeat it on that data source again, and you should be able to remember it quite easily for other uses.
The following example below is done with Microsoft Excel 2010 on a Windows 7 OS. Most Windows OSes should work the same way, but I cannot get this same functionality using the Microsoft Excel product for the Mac.
Most importantly, MAKE sure you read STEP 1 carefully. Do NOT select "from Web", but use "From Text".
STEP 1 - Open up your main Excel Spreadsheet you use as the location where you import your data (this might be a final summary spreadsheet or such). Once your are there, on the sheet you wish to import the data select the Data Tab. Select "From Text" (strangely, do not select "from web") as shown in the graphic here:
STEP 2 - A file selection dialog will appear. Instead of entering a file path or searching for a local computer file, enter the website URL of your CSV or Excel spreadsheet file, as shown in the graphic here:
STEP 3 - Select "Open" file and the file will begin requesting to be imported via the regular text import wizard, as is normal for Excel. Enter the configuration of the wizard as you would if the file was local, configuring it to the fields and formats you wish. Shown in the graphic here:
STEP 4 - As per usual, choose the location of the data import placement on the current sheet. Shown in the graphic here:
STEP 5 - Your data will import on the current sheet. Once it does, select the "Properties" button in the "Connections" area of the menu. This will bring up the "External Data Range Properties" sheet. Shown in the graphic here:
STEP 6 - You can configure it to your liking, but I prefer the following settings that seem to work well in most settings- Unselect "Prompt for file name on refresh". I leave the refresh control buttons off as well, so that I must click the Refresh button to ask for updates. I also unselect "Adjust column width" so that the spreadsheet is not altered in format/layout by the new data each time. Look at the graphic below for the other settings I recommend in most situations:
The goal of all this is to provide you with a instantly refreshable CSV data source that is web based. No more downloading, deleting old data, importing new, and reformatting.
I hope that helps some of you who have projects that this applies to.
Happy spreadsheets all!
Kim Gentes
CSV, data, excel, external data, import, spreadsheet, web in CSV, Programming, Software, Web, Web Applications
Reader Comments (29)
informative post! thanks for sharing!
Thanks. Just what I need.
very nice....gr8 job by explaining it in detailed manner
Really great post and loads of help. Thanks
Works perfectly. Thank you
If I select the option to refresh every 60 minutes, does the file have to be open in order for the data to be refreshed?
If you select refresh at all, it will only continue to refresh WHILE you have the file open. Of course, once you open up the file (after closing it), it will refresh again when you open it up.
Thank you.
Its read only and will not wright back to webserver
Justin,
that is true.. this is a read only function for FILES that are being loaded into Excel. If you want read/write you must connect with a DSN / SQL source ,not a raw file import..
Kim
Anyway to generate a dynamic filename pulled from another sheet?
I have a date range which is part of the file name...
<webpage/startdate-to-enddate-filename.csv>
That date range is dynamically generated on another sheet of the same spreadsheet.
Thanks!
kim,
very very clever - never would have thought to put the urls into the text function.
been beating my head against the wall to figure this out.
ok, so I used your idea and it works great. now a problem, ok?
in order to refresh the csv download once a week I tried 3 things:
- use the Data/RefreshAll function on the menu
= works perfectly, but forces a refresh of everything, and I only want to refresh the 1 worksheet I have assigned this macro to
- link the webquery macro to a control button on the QuickAccess Toolbar
= this fails with the msg: invalid procedure call or argument
- link the refreshall macro to a control button on the Quick Access Toolbar
= I recorded/modified the refreshall macro to ActiveWorkbook.ActiveSheet.RefreshAll
= this fails with the msg: object doesn't support this property or method
clearly I am corrupting something here.
can you point me in the right direction?
thanks,
ron
What about secure urls? How can you add a user and password for url?
fricking awewome. THANK YOU
Hello Kim,
Thank you very much for your nice post. It is clear and helpful. However, I met some problem when I try to import a web-connecting csv file from Yahoo finance (http://real-chart.finance.yahoo.com/table.csv?s=GLD&d=7&e=4&f=2014&g=d&a=10&b=18&c=2004&ignore=.csv). The problem is the following. As trying to import the csv file from above link, I can do that successfully. However, if I try to refresh the imported data sheet, the "Open" file window keeps popping out (looks like your figure Step 2).
Is there any way to resolve this problem? Thank you in advance!
SH,
normally you get that Open file dialog when you don't get all the proper settings in STEP 6. Can you create the link again and tell me exactly what you have selected on the dialog in STEP 6? Better yet post a screen capture of the dialog showing its settings. They should match what I put in the STEP 6..
Does that help?
Kim
Martonx,
You can add linked files with userid/passwords. It is a little tricky but basically it is involves creating a place on your local computer that sees the file as a "SAFE" zone in Internet explorer. Enter the passwords when you are prompted and be sure that you choose to remember and save the passwords when they are presented.
Kim
Hi Kim,
Thanks for this. Hope you'll see this as I know the post was a while back.
I have a xls sheet connected to a csv file, which I update with new contacts every week. I've altered some of the columns, and deleted others after my initial import. Now, every time I refresh the date, all the work I have been done is deleted and the xls is refreshed to it's orginal state (as when I imported the csv the first time).
Any idea on how to refresh and keep the work I have done in the columns?
Many thanks
Adam
Adam,
From what I know about these connections, you can't redefine the columns and maintain the same connection. This means you have to create your data and connection, but incorporate the data you have. The way I would do that, if I were you is:
[important: BEFORE doing any of this, BE sure to save backups of ALL your files. The info below is only a recommendation and I cannot give you advice without really knowing your situation and specific details. So please, be careful to back up and keep your solution information before trying anything I list]
1. download your CSV file to your local machine and save it
2. update your spreadsheet to what you want (column wise)
3. use the CSV data manually in your spreadsheet and then update the missing data in the columns as needed
4. save your spreadsheet to the CSV on your local machine
5. Check the local CSV and make sure that the new columns and data are shown
6. upload the CSV file to replace the online file you have (it should maintain the same name)
7. ensure that the online version now has your updated info (download a copy of it and check)
8. remove the original connection reference from your spreadsheet,
9. Re-add the connection reference and it will ask you to redefine the column formats etc.
Kim
Thank you very much !
PERFECT. Exactly what I was looking for. I learned something new!
Absolutely marvelous.
Thanks very much !!
This is very frustrating, as it doesn't work as listed...
When I do this, although the file is a data set (I created) as a comma delimited file (CSV) Excel insists on trying to load HTML text, not the data.
I am using Excel 2013, but think you used earlier (maybe 2007). Is this no longer an option to load data set from the web.
Kevin,
This does work, I reset it every few months, since I use this technique in my own work. I do not know what you mean by "Data Set" . In Excel, these are called "Workbook Connections". Specifically, from the Data menu, you can see them as "Connections". You define them and can edit them from there. Again, make sure you read STEP 1 very carefully and do not choose "from web". This seems counter-intuitive, but I didn't design Excel.
Hope that helps.
Kim
Thanks so much for this article, Kim! I found it very helpful. You might anticipate a new surge in interest in your method with Yahoo Finance's recent site redesign. Apparently they've cut out one of the alternative means of importing stock data into Excel from their site,
Thanks. How can this be used to import multiple csvs from the same folder on a website? Do they each need to be imported separately? Or can the whole folder of csvs be imported? When I try to import from folder, it won;t accept the URL as an option
This approach can only be done on a per file basis. If you have multiple files, I've found it best to use a different sheet for each file being imported into your spreadsheet.
Even after some years this is still helpful and I thank you VERY much for these instructions.
Greetings from Germany where I haven´t found an explanation like yours.
I don't think this applies to the macs, does it? I tried, but got stuck in the first step itself. :(