Excel 2007 - Getting External Data

On of the most visited parts of my web site is the part on external data. Most of it was based on Excel 2003 and before, so I thought I should update it for Excel 2007.

INTRODUCTION

The first thing you will notice about 2007 is the ease in which you can find the data 'movement' tools. Now, say what you think about the ribbon in Excel 2007, but finding where to import and export data is not one of them! It's on the Data tab. (as below)

I have only shown you the section above. The tab extends beyond this with groups for sorting and filtering, validation and de-duping tools and grouping stuff. We will only be using these two groups. (Get External Data and Connections)

SIMPLE IMPORTS

The first three buttons from the left are easily discoverable and ready-rolled versions of the old commands available through the Data>Get External Data>New Database Query... (See below)

The 'From Access' and 'From Text' options, simply throw up an 'Open' dialog to allow you to navigate to your selected file. It should be noted that the Access variant is compatible with versions of Access pre-2007, allowing extensions of *.mdb (2003 and before) and *.accdb (2007), as well as their 'project' equivalents (*.mde and *.accde). The text import version allows import of *.prn (space delimited), *.txt (tab, or other delimiter) and *.csv (comma delimited).

IMPORTANT TIP: There is also an option, when selecting text, to select *.* all files. (see below).

This is important when you have columns with data that may begin with a zero, for example part numbers like 000123456. Using any other driver for import will have Excel simply strip these off, leaving you with 123456. This will be a real problem when you come to do any VLOOKUPs or similar as one will not see the other.

So, the trick is, remove whatever extension you have completely. (To do this in later versions of windows you will likely have to go to any Windows Explorer window and select 'Folder Options...' (In Vista this is under the 'Organise' option) and on the 'View' tab, de-select to 'Hide known file extensions'. (In other words to show them), see below).

You will get warned about changing file extensions being dangerous and you can ignore that! You will then end up with the usual icon for a file that windows shows when it has an unknown file extension.

Taking this route will launch the Import Wizard, allowing you to set the part number column (for example) as text, thus keeping your leading zeroes (more on this option later). This is the only successful way I know of doing this as Excel, through any of it's proprietary drivers, does not seem to honour field 'types'.

IMPORTING FROM ACCESS (or most other OLEDB data sources)

So, taking the first option will throw up the dialog to navigate to your database file (as outlined above), once you have done this you will be presented with a dialog, showing the tables and queries (views) in that database file, select the relevant one and click 'OK'.

You will notice from the dialog below that I name my Tables, tblXXXXX and my Queries, qryXXXXX. This helps me to easily see them and also sorts them neatly. However, if you do not do this, you should note the icon for each is different, with the one for Queries or Views looking like two overlaid table icons. (as below)

Select your table and you then get loads of options to place your data into the workbook. (see below)

Most of these will be self-explanatory but listed you can have the data as a new 2007 formatted table (the default), a pivot table, or a pivot chart and pivot table. Equally you can decide whether you want the data placed in the existing worksheet (and where on that sheet) or a new sheet. Don't forget also the 'Properties...' button that will throw up a dialog, as below, to turn on, or off, some features of the resulting connection to your data source. Key on the usage tab is a descriptive name and description, as this will now appear under the 'Connections' button on the 'Connections' group on the 'Data' tab. A feature allowing future manipulation of this connection which was difficult, or near impossible in earlier versions of Excel. Also, you may like to set the 'Enable background refresh' (default) so that you can work with Excel while the data is refreshed in the background. This becomes relevant if you set the data to refresh every nth seconds. Probably the most useful setting to ensure 'one version of the truth', is the 'refresh data when opening file' setting. Even though Excel will fire the security bar to warn that the data may be unsafe (unless you have trusted the location or similar), as soon as you 'release' this the data will update with the live data in the source.

The definition tab (below) allows you to change the connection string, which is handy if you move the file or similar, it also is a good way to get the string if you are using it in VBA or .NET code.

Finishing all these dialogs will return your data to the Excel worksheet as a table or pivot table template, as below

Once you have your data on the sheet you will notice that not only are the 'Refresh All' and 'Connections' button on the Connections group 'lit' (as top), but also the Properties one is also. If specifically you are in a new Excel 2007 table, this shows some nice features to expand column widths, include row numbers from the data source and whether you want you data replacing and deleting the existing data or appending it.

Bear in mind, once you have your external data in Excel, you are only a right click away (in the table or Pivot) from selecting 'refresh' to get an updated view of the data.

Much of what you have learned under this Access heading will stand you in good stead for all other sources as in fact it is using an OLEDB data source 'under the covers', the same as you will use for SQL, Oracle, or most other modern data sources. The only difference will be if you are using a server technology, is you will not navigate to a file, you will use a server name.

IMPORTING FROM THE WEB

Once you use this technology it is surprising how much you will use it in the future. It is great for grabbing for example order screens as receipts and also any kind of list. I suspect however that most people use it to keep up to date with stocks and shares and, providing the site owner doesn't mess with the URLs you need only open Excel and refresh each time to see the latest data, as with any other data source. This is the example we will be using. (Using Microsoft's Stock Symbol on Yahoo (MSFT).

Here is the URL (Today)

Firstly we will go to the 'From Web' button. (See above). This effectively launches a browser window into which you can either enter the URL or paste it as with any other browser. (see below)

Notice above that this control 'senses' the table in the data and places  small yellow boxes with right facing arrows to show them. All you do now is simply click on any table you want to select, which will turn the yellow marker to green with a tick and click 'Import'. Now, when doing this with just these default settings, Excel imports a pretty bland, text representation of the data, (see below)

To alter this you should, before importing press the 'Options...'button, top right of the dialog, (see above). This will give you several choices, (see 1st below), the two most crucial of which are, 'Full HTML formatting' and 'Disable date recognition'. The former ends up with the data looking far prettier and much like it is on the web page itself (see 2nd below), the second will stop data such as 1-1-00 (possibly financial data, from being converted to 01/01/2000 (as a date).

Now, as with any other data on the worksheet, you can simply either press the 'Refresh' button on the 'Data' tab or right click on the data and select 'Refresh'

IMPORTING FROM TEXT

Remember, that a csv file (comma separated values) is a native form to Excel. You will notice this by looking at the file icon. It is like a normal one but with a letter 'a' and a comma. If you double-click directly on a csv file, excel will open it. Sometimes this is undesirable, so I suggest you take the 'Important Tip' route above.

If you have any other text file format than csv, you should open it using the 'From Text' button. Remember too, that even if Excel doesn't open the file format you have (Quicken for example), you will nearly always be able to save as some text format within the source program, so you should do that.

Pressing the button simply presents a navigation dialog to go to the file, as below

Once again, you have the option in the bottom right to show all files (*.*), so if you file does not have a *.prn, *.txt or *.csv extension you will need to use that.

Pressing the 'Open' button presents you with the three step Text Import Wizard allowing you to specify the data steps. The three dialogs are shown below

Step 1 allows you to specify whether the file is delimited (has a character of some type between each column (field), e.g. comma, tab, space (you will notice the commas separating my data) and the other usual option allows you to start the import at a certain row. This is handy when certain systems simply output a paper report to text file and the report has headings as this will allow you to strip those rows out.

Step 2, if you have, as I have selected 'delimited' in step one, will allow you to select the delimiter. In my case this is comma but if you have any other you can select from the choices or select 'other' and enter the character in the box. If you had selected 'fixed width' in step one. (That is field one is say always 10 characters, field 2. 30 characters and so on), then this step allows you to click at the fixed widths of your data to set the delimiting 'bars'.

If you are likely to have consecutive delimiters, e.g. an address file that is comma delimited may have commas at the end of an address line, then you should select to 'Treat consecutive delimiters as one'. Some data systems also output text data, as opposed to numeric data, surrounded by double quotes, if so, select that from the 'Text qualifier' drop down.

The last step allows you to set the columns as certain data types or not import at all. This is where, as I have done in column 5, you can set you data as text and retain you leading zeroes. Equally, as you can see in column 8, I have selected 'Do not import column' and it has placed 'Skip Column' in the heading and it will not be imported.

Clicking finish returns the data to the worksheet and you are done!

IMPORTING FROM OTHER SOURCES

This is where Excel uses it's OLEDB or ODBC drivers that are loaded on the machine

IMPORTANT: It is possible that your driver list will not match mine and you may not be able to import from an AS400 DB2 for example. This is because your machine is not loaded with the driver for that data source, nothing to do with your excel installation. Remember, if you are setting this up for someone else, their machine too must have the relevant driver and connection file in most cases.

For purposes of this demonstration, I will be using SQL Server as my data source, but frankly, if a driver is available, it could be just about anything. Generally you may prefer to use the OLEDB drivers, which will create a transferable connection file, but if all else fails, you can use MS Query, an age old tool in Excel that is a little like Access and uses ODBC as it's connection technology. It's a bit like dropping back to VGA if you have trouble with hi performance video drivers!

Below are your menu choices from the 'From Other Sources' button.

Selecting SQL Server presents us with a Data Connection Wizard which you can use to set the necessary settings for your connection. The three steps are outlined below.

The first step allows you to specify the server name. (my example, (local), connects to the local machine server). I most instances Windows Authentication will be OK. This is a system whereby Windows manages your passwords, meaning your network logon suffices for SQL, email, etc, etc. On occasions, each user will have a logon specific to the data source or even a table. If that is the case,as will often be on remote/shared databases, you would select the other option and enter a user name and password.

The second step offers you the specific database in the named server you want to connect to. Changing this will present you with a list of table and queries (views) in that particular database, on that named server. Equally, if you want to select this each time you use the connection then you can uncheck the 'Connect to a specific table' check box and you will be prompted each time. As before the icons for Tables and views are different to ease identification. (I use a different naming convention in SQL server, uv-xxx-xxxxxxx is a user view (query) and the tables are named in plain English. Don't ask me why, I've always been a little quirky!)

Step three allows you to name the connection file and give it a description to help in search as as an aide memoir on what it does. There are tons of other metadata stuff you could put here, but that's about it.

From here on in returning data to the worksheet follows the same route as under the Access section.

USING PARAMETERS IN QUERIES AND MS QUERY (ODBC)

NOTE: This is a repeat of the same subject in another post on my blog, but as that one was set in XL2003 and is the most hit entry, I thought I would update it for 2007.

If all the above fails, you can drop back to the 'age old' method of MS Query which uses ODBC technology. The operation of this can be seamless, without you even seeing an interface, but what I would like to show is a way you can use dynamic parameters on your worksheet to choose the data shown.

NOTE: A standard install of Excel may not install this feature (can't remember), if you do not have this option, re-insert your installation media and chose to 'add and remove features' and select all the data options you can see!

So, let's start.

When I am intending to work with parameters on a worksheet, I tend to enter the placeholders for these first. this will help when the interface asks for the parameters and tries to retrieve the data. (I am going to pull data between 2 dates, remember I am in the UK locale so our dates are formatted dd/mm/yyyy). So the worksheet would look like below.

I am hoping when we connect to the data (table or query) we will bring through initially all catalogues with a mail date between 1st Jan 07 and 1st Sep 07.

Next we will select the 'From Microsoft Query' option from the 'Other Sources' button, (see above). This will launch a dialog asking us to choose a data source (see below). (Data sources are files stored on you machine (or in the file) that are available to connect to data, you will recognise some in the screen shot, but some are specific source files on my own machine. the location of these files on your machine can be accessed by clicking the Options... button).

We are connecting to a SQL server, so I will select the top option of 'New Data Source...' as I don't currently have a connection I need. This will launch another dialog to  name the data source and select the driver.(see below), again, if you don't see what you need you will need to install the driver for that data source from the relevant program vendor.

I have selected SQL Server from the drop down and called it Local SQL (Any name you will recognise will do)

Next you will need to connect to that data source, so clicking 'Connect...' in the dialog above will present another dialog (below) to do that. (I have pressed the 'Options>>' button 9dimmed in the shot below), which expands the options to connect to the database. this is a good idea if this connection is only needed to connect to one. In my case AS400UK). I have selected the (local) server, (That's the one on my machine) and used my trusted 'single windows sign-on' connection.

Once you clear the dialog above, you will be re-presented with the 'Create New Data Source' dialog (two above), with the option to select a specific table or query (view) in the database. I leave this blank to give me a utility connection that I can select tables from as we need. Equally we will now be returned to the first dialog in this section, but this time we will have our new source of Local SQL in the list. (see below)

If we click OK we will get a wizard taking us through the steps of selecting a table, the fields we require from that table and filtering or sorting them. For the purposes of this we will not be filtering or sorting at this stage (steps 2 and 3), but below is the first screen with the CatHeader table selected and four fields within that table moved across to appear in our data.

On the fourth screen we could simply select to return the data to Excel and that would be done, with any filtering and sorting done earlier. We want to apply parameters to our data though, so we need to select 'View data or edit query in Microsoft Query' at this stage. (As below).

Clicking finish gives us our first view of the MS Query interface with our data in place.

We need now to set our parameters and to do that we go to the add criteria button (above) which adds a grid to the interface (below). In here we need to select the field we wish to add the parameters to and enter the parameters. To enter parameters you simply put the square braces around where you want the parameters values to be placed, so in our case of wanting to get data between two dates we can enter:

Between [Start] And [End]

It matters not what you call the placeholders in the query, we could have replaced 'Start' with 'a' for example. (without the quotes)

The values we enter in the worksheet will replace the Start and End parameters. It ends up looking like below.

Next we go to 'File' and select 'Return data to Microsoft Office Excel'. This will fire a dialog to enter each of the parameter values (See below). As we are getting these from the worksheet you should enter nothing and just click 'OK' to all prompts.

After this you will be asked where in the worksheet you wish to place the data or whether you want to put it on another worksheet, but... WE DON'T WANT TO DO THAT YET!

Before doing that your should press the 'Properties...' button in the dialog (1st below) and on the resulting dialog's 'Definition' tab (2nd below), you will notice the 'Parameters...' button has 'lit up'.

Press this and you get a dialog giving you three options. (See below)

1. Prompt for value using the following string (new in 2007), allows you to have Excel fire a dialog and for you to have a customised message, say... "Enter the Start Date" 
2. Use a 'hard coded' value every time
3. Get the value from a worksheet cell. (This is the option we want). You simply enter a cell reference to the data for each parameter in the list on the left and that's it!

To make the data refresh each time the parameter changes you should check the box at the bottom (I always do this, but if you have a very long running query it could be a problem)

Simply then, you just need to change the values in the cells and the data below will reflect the new values. (see below)

That's about it for data interchange in Excel, remember though that in Excel 2007, you also, unlike previous versions can easily re-use data connections and edit them, right here on the 'Data' tab of the new ribbon.