|
New! Excel User Group Now Open @
www.excelusergroup.org,
UK EXCEL USER CONFERENCE - LONDON, UK. APRIL 1st/2nd 2009 -
TOTALLY FREE
-
DETAILS CAN BE CHECKED OUT HERE!
Check It Out!
External Data Examples
By far the simplest import into Excel and probably
amongst the most common is a text import, either by way
of CSV file (Comma Separated Values), txt files
delimited using some other character (most commonly tab
or space) or txt files where there are no delimiters
separating the fields but the length of each field is of
a pre-defined fixed width.
As you may notice files with a *.csv extension are seen
by Excel as native (the file icon shown is very similar
to a standard Excel icon with a comma shown). This
means that simply double-clicking as you might on a
standard Excel file will open a CSV File directly into
Excel. This can be extremely convenient but can also
throw up certain issues which we will discuss below.
This grid shows ten records set up in Excel which we
will be saving as a CSV File. Of particular note in
this example is that column A are item numbers formatted
within Excel as text, that is they naturally left align
and contain leading zeros.s.
Selecting the menu options ‘File>SaveAs’ as shown below,
allows us to save Excel data into a text file.
In this case we are selecting CSV (Comma delimited) as
shown below
The results of this ‘Save as’ action are shown in
Notepad, you will see most of the formatting gone, the
fields being delimited (broken) by a single comma (,)
and the leading zeros very much there.
If we now open this CSV file by double-clicking on it in
Excel, it opens automatically, but the leading zeros
have gone! (As shown) This may, or may not be
desirable… I suspect the latter.
You could of course, if all the ItemNo’s were the same
length, format them as Format>Cells>Custom>000000. This
would display them correctly, but it doesn’t alter the
fact that Excel is actually now storing them as
numbers. Another CSV ‘Save as’ of this new data, opened
in Notepad, proves this.
We can overcome this ‘deficiency’ by gaining far more
control over the import process by using the Text Data
Import Wizard. This is automatically invoked when the
file has no extension or if it has a *.txt or *.prn one
etc. We will outline this below.
As outlined above, certain text files invoke the Text
Import Wizard, rather than opening automatically in
Excel. If you wish to fool Excel into ‘thinking’ it
needs the Text Import Wizard you may need to re-name
your CSV file without its CSV extension. This can be a
little tricky in Windows XP as by default no extensions
are shown. I usually copy them to the root of C:\ and
use the old DOS commands from Start>Run>Command menus in
Windows XP. You will get a command prompt window as
below.
Type
Rename C:\OldFileName.csv C:\NewFileName
When you now open Excel and take the menu options
‘File>Open’ and change the ‘Files of Type’ drop down to
‘All Files’ your new file minus extension should show in
the list. If you now select this Excel will open the
first screen of three in the Text Import Wizard as shown
below.
This first step allows you predominantly to select
whether the file is delimited or whether each field is
of a fixed width. We will cover the latter later so the
selection on this occasion is delimited. The other
options offer you to start your import at other than the
first row which can be handy if your data as here
contains headers which you do not wish to import into
your data. The other option allows you to change the
expected input language. In general the default
settings shown is perfectly acceptable. The second step
of the wizard is activated by pressing ‘Next>’ and this
dialog is shown below.
This step allows you to set the delimiter, the default
currently in Excel 2003 is ‘Tab’ but as you can see
above we have already selected ‘Comma’ just prior to
de-selecting ‘Tab’. This shows that multiple delimiters
can be used and indeed the last option is ‘Other’ where
if selected you can type your delimiting character in
the text box shown. As soon as ‘Comma’ is selected you
can see that the comma delimiters (seen in Step 1) are
replaced by dividing lines. This should be seen to
accurately segment your data. Occasionally data may end
naturally in a comma for example, address fields. If
this is the case you should check the ‘Treat consecutive
delimiters as one’ check box where Excel will ignore the
one that actually should remain in the data. This alone
sometimes makes commas unsuitable as a data delimiter.
Additionally certain databases wrap string data in
double quotes (“example”), if this is the case this will
be handled by the double quote symbol in the ‘Text
qualifier’ drop down. Pressing ‘Next’ moves you to the
last step of the wizard as shown below.
This step is where we have some limited control over the
data type of the imported data. The choices we have are
‘General’ (default), ‘Text’ (which is the option we will
take to retain our leading zeros), various date formats
and finally an option not to import certain columns.
The ‘Advanced…’ button allows you to make fine settings
on the decimal and thousand separators and recognising
trailing minuses in negative numbers. Pressing ‘Finish’
now results in a CSV File being opened with greater
control and therefore retaining it’s text format and
leading zeros as shown below.
Certain data files have no delimiters but their fields
are of a ‘Fixed Width’. As seen in the Data Import
Wizard earlier Excel can also handle this and below are
the three steps which occur when selecting this option.
The first directly below is obviously identical to the
previous dialog but ‘Fixed Width’ is now selected. You
can see the data is evenly spaced and has no delimiters.
Once we move to Step 2 below the dialog is different and
as you can see Excel has made a guess (albeit in this
instance inaccurately) where the data should be
divided. This can be corrected by dragging and dropping
existing ‘Break’ lines (as shown) or clicking to insert
new ones or remove existing ones.
Once we are happy with our field breaks we can move to
the third step of the wizard as shown below. This once
again is the same as the ‘delimited’ version with
options to import as text, skip columns etc.
The resultant data from this import is shown in the grid
below (note we still retain the leading zeros).
Here we can demonstrate the linking of data in an
external data source with Excel, using the values in
cells to determine the data presented, using
parameterised data queries.
Open a workbook and on the active sheet in cells A1
enter Start Date and in B1 enter the date 01/01/2003. In
A2 enter End Date and in B2 enter the date 02/02/2003.
Take the menu options Data>Import External Data>New
Database Query...
You will fire from here a dialog asking for your
selection of an external datasource. We have chosen 'MS
Access Database'.
Navigate your way to your Access database and select the
table or query you want from the list displayed and add
the fields you require, as below. (Remember, if you have
a parameter query in Access already, this will create an
error if we try to use it in Excel. 'Too few parameters,
expected 1').
Move on three screens making no changes until you arrive
at the final screen (below). Take the second option to
'View data or edit query in Microsoft Query'. This will
launch Microsoft Query. (For those familiar with Access,
this looks very similar to the query designer).
From the image below you can see we have shown the
'criteria grid' by selecting View>Criteria from the MS
Query menus.
In our example we are going to take orders with a ship
date between two dates, (01/01/2003 and 02/02/2003). To
do this we enter the operator 'Between' followed by our
first parameter. These are enclosed in square brackets
and what is in here will, in certain circumstances,
appear as the prompt in the input box, with the entry
being the parameter. 'Between [Enter the start date]'.
The next part is the 'And' operator followed by our
second parameter, completing the parameter thus:
Between [Enter a start date] And [Enter an end date]
In MS Query select File>Return data to Microsoft Excel.
You will be prompted for your two parameters. (start and
end date), but you can ignore them. (Answer OK).
You will now get the dialog below, asking for the
positioning on the sheet.
Click the 'parameters...' button to show the dialog
below. You now have three choices.
1) Prompt for the values. (You can enter any prompt
here).
2) Use the following value. (You can enter a static
value).
3) Get the value from the following cell (Our example).
Remember to set how the value is obtained for all values
and, if you want the data to update each time you change
the value of the cell(s), then select the checkbox.
(against each value again).
Click OK in the 'Parameters' dialog and select $A$4 as
the cell for the start of the data, click 'OK' in the
'import data' dialog and your data should flow in
filtered between the two dates supplied. Each time you
change the dates, the query is refreshed with the new
input.
If you find you wish to change parameters or the way
they action at a later date, this can be done in Excel
via 'Data>Import External Data>Parameters...' or via the
'External data' toolbar. (Above).
Top |
