Kim Gentes

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:

• Generate the CSV/Excel data
• Download the CSV or Excel Data to your local computer
• Delete the old data from your main spreadsheet
• Import the Data into your main Excel spreadsheet
• Reformat the data to fit your spreadsheet formulas and layout

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