Importing Data Files

0) Introduction:
1) Importing Fixed Width Text Files:
2) Importing Delimited Text Files:
3) Importing Data Directly from the Web:
4) Summary:


0) Introduction:

In this lecture we see some examples of importing data into Excel from external
sources: from text files or from the web (either indirectly, by putting web
contents into a text file and then importing this text file, or directly from
the web to Excel).

I am assuming that everyone has some fluency with using a word/text processing
software, such as Microsoft word or notepad. Note that a text file (unlike a
Word document) contains all text but no formatting information. Typically a
text file has the extension .txt. We will see  below how to easily save a
Micrsoft Word document as a text file.

Note that when moving data from a text file to Excel, sometimes we have to
edit the information to remove irregularities. Sometimes the editing is easier
to do on the text editor side, before importing the file into Excel; sometimes
it is easier to do in Excel, after importing the file. Remember that there are
two places that we might do the editing, and think about which place would be
easier. Sometimes we might have to go back and forth a few times, editing
information in both places, before we discover how to import the data
correctly (it can be an iterative process).

We will start by examining how to import text files into Excel. There is a
"wizard" that allows us to specify information about the format of the file
so that its data will be placed into the proper cells (rows and columns).
Generally, each line in the file maps to a different row in Excel, so the
interesting specification is how to map the content of each line into Excel
columns. We will explore the two fundamental ways to specify this mapping:
using Fixed Width files and Delimited files. In both cases we specify
additional information about how to break lines into columns. The "wizard" will
supply feedback, making it obvious whether we are importing the data correctly.


1) Importing Fixed Width Text Files:

For example, the "files" folder (which we can downloaded for this lecture)
contains a file named lineup.doc, which is a Word document that contains some
basketball statistics. It starts with the names of five players on the court
at the same time; then their rate of scoring against the average NBA team
(a value of -19.79 means they would score 19.79 points less than the average
NBA team if they played together for and entire game: 48 minutes); then the
game number (uniquely identifying NBA games); then how many minutes these
5 players played together (9.05m is 9.05 minutes); then the team they played;
and finally the team they are on.

-->Open the file lineup.doc in Microsoft word

Notice that because each line is so long, it wraps onto the next line. Also
notice that the data seems aligned (there are tabs placed for where each
data item should go). First, we must save this file as a .txt file.

-->Click the Office Button (on the top left of the Word window) and then click
-->  the Save As icon
-->In the "Save as type" list, choose "Plain Text" (you might have to scroll
-->  down: the options are alphabetical
-->Then click save;
-->In the "File Conversion" pop-up window, just click OK
-->Terminate Microsoft word
-->The file lineup.doc will stay the same, and we will also see the file
-->  lineup.txt; double click this file to see what editor it brings up
-->  to edit the file (probably notepad) and then terminate the editor

Now let's see how to import that data from this file into Excel, treating it as
as a Fixed Width file. In a fixed width file, each column of data in Excel
starts at the same indentation. The information in the file is lined up in
columns already.

-->Start Excel (either create a new workbook or edit the workbook for this 
-->  lecture, which is empty.
-->Click the Office Button (on the top left of the Excel window) and then click
-->  the Open icon
-->Specify in the "Files of type" list "Text Files"
-->Navigate to the folder containing the lineup.txt file and double-click
-->  that file

We will see the "Text Import Wizard - Step 1 of 3" pop-up window. Excel
believes this text file is fixed width file (the Fixed Width radio button
is pressed at the top). At the bottom is the contents of the file with the
lines numbered on the left (we can use the sliders to see more lines or more
of each line). In the middle is a control to select which row to start the
import at: some files have header information at the top that (a) we don't
want in the file and (b) would cause problems importing it. Using this control
we can tell Excel to skip these rows. This file has no header, so we start at
row 1.

-->Click "Next>"

We will see the "Text Import Wizard - Step 2 of 3" pop-up window. For fixed
width files, Excel tries to determine where is the data for each new column.
Here is shows the first names starting at indentation 0, the second names
starting at indentation 12, the third names starting at indentation 24, etc.
If the file really is a fixed width file, Excel should infer the indentation of
each column of data, as it does here.

If Excel doesn't quite get it right, we can manually alter the specification.
We can

  (1) Create a new indentation where data in a column starts
  (2) Delete and existing indentation where data in a column does not start
  (3) Move an existing indentation to better show where a column starts

The way to perform each of these options is specified in the window itself. In
the case of the lineup.txt file, we need to make no alterations.

-->Click "Next>"

We will see the "Text Import Wizard - Step 3 of 3" pop-up window. Now that
Excel knows where the columns are, we can specify what kind of data exists in
each column. Our choices here are General, Text, Date (with various options),
and skip (which says to ignore that column: do not import it). After we import
the data into Excel, we can format it in more detail: the option here just gets
us off to a good start.

Although we can import everything as general, let's tell Excel the types of
data for each column.

-->The first column should be highlighted and labeled "General": Click the
-->  Text radio button and the label should change to "Text".
-->Click any data in the second column: the column will be highlighted;
-->  click the "Text" radio button and the label should change to "Text"
-->Repeat this operation for the other three columns that contain names.
-->Scroll to the 7th column (whose top few lines contain 695#) and click
-->  any data in that column; click the "Do not import column(skip)" radio
-->  button so that this data will not be imported.
-->Also specify to not import information in the last two columsn

The "Advanced..." option allows us to specify a bit how numbers are interpreted
in that column, for example allowing commas or periods as decimal separators.

-->Click "Finish"

At this point Excel creates a workbook, named lineup, which contains a new
worksheet, named lineup, containing the imported information. If we want, we
can immediately save the new workbook (using the "Save as" command; Excel will
ask us where to save the file), or copy the worksheet from the new workbook
into the original workbook that we have opened (which is what we do below).

The easiest way to do this is to right-click on the tab for the "lineup"
worksheet and specify "Move or Copy...". In the "To book:" list we can select
the original workbook we opened. Then we click "OK" and Excel will move this
worksheet into the workbook we selected: all the data will move and the tab
will remain the same.

As a final change, we want all the values in column G to be pure numbers: we
want to remove the "m" after each number. This would have been hard to do in
the text editor, because we don't want to remove every "m" (some players' names
have "m"s in them). But in Excel it is easy to do: we select the entire G
column (click on the G that identifies this column) and then click the Find &
Select icon (in the Editing group on the Home tab) and then select Replace...
Fill in the "Find what:" text field with m and ensure the "Replace with:" text
field is empty; then click "Replace All". The "m" characters will disappear,
replaced by pure numbers.


2) Importing Delimited Text Files:

Now we will go through these same steps with a file whose different data values
are separated by commas. Such a file is called a delimited file, because the
commas delimit the end of one data value and the beginning of the next one.
We will use a file that has the same data as the one illustrated above.

We will use a slightly different sequence of commands to get this data (from
the ribbon, not from the Office Button

-->Click the Data tab on the Excel ribbon
-->In the "Get External Data" group, click the From Text icon
-->Specify in the "Files of type" list "Text Files"
-->Navigate to the folder containing the lineupalt.txt file and double-click
-->  that file

We will see the "Text Import Wizard - Step 1 of 3" pop-up window. Excel
correctly believes this text file is delimited. Note that the bottom of this
window is the contents of the file with the lines numbered on the left. Note
the values are not lined up, but instead separated by commas.

-->Click "Next>"

We will see the "Text Import Wizard - Step 2 of 3" pop-up window. Note that
although Excel believes this file to be delimited, it has no idea what the
delimiter is. In fact, Excel really just knows that this file doesn't match a
fixed width format.

The pop-up window gives us standard choices for delimiters (tab -automatically
selected; semicolon, comma, space, and other - where we can enter our own
delimiters. Note that the Data Preview section at the bottom doesn't show
any columns, because there are no tabs in this file to separate columns.

-->Click the check in the Tab box to uncheck it; click the comma box to check
-->  it

Notice that now the Data Preview shows the data correctly aligned in columns.

We are allowed to specify multiple delimiters: Excel uses each one we check as
a delimiter that separates data values. Here we use only comma as a delimiter.

We can also specify to "Treat consective delimiters as one" which we discus
further below.

-->Click "Next>"

We will see the "Text Import Wizard - Step 3 of 3" pop-up window. This window
is used for delimited files exactly as it was used for fixed width files above.

-->Click "Finish"

At this point we are exactly where we were when we clicked "Finish" for a
fixed width file.

Let's take one more look at a delimited file, but one with repeated delimiters.

-->Click the Data tab on the Excel ribbon
-->In the "Get External Data" group, click the From Text icon
-->Specify in the "Files of type" list "Text Files"
-->Navigate to the folder containing the lineupalt2.txt file and double-click
-->  that file

We will see the "Text Import Wizard - Step 1 of 3" pop-up window. Excel
correclty believes this text file is delimited. Note that the bottom of this
window is the contents of the file with the lines numbered on the left. Note
the values are not lined up, but instead separated by three spaces each.

In fact, examine the 4th name on line 2. I had to change the name Van Exel to
Van_Excel, otherwise Excel would have interpreted the space between Van and
Exel to mean that these are two separate data values. This is an example of
a common type of problem that arises when importing data. Often importing fails
and we have to go back and slightly edit the input file so that it fan succeed.

-->Click "Next>"

We will see the "Text Import Wizard - Step 2 of 3" pop-up window. Note that
Excel believes this file is delimited by spaces. But not every single space
separates data values: we have three spaces separating each pair of data
values. Uncheck the Tab box and check the Space box. Notice that the checkbox
"Treat consective delimiters as one" is checked. Thus, Excel treats any number
of consecutive spaces as if it were a single space, which is what is needed to
import this data file.

-->Click "Next>"

From here on, we finish as we did with the example above.

So, there are three different kinds of text files: those that fixed width,
those that are delimited by a single delimiter, and those that have delimiters
that might be multiple consecutive occurrences. We should now be able to
instruct Excel to read any such files.

-->Try importing data from the cpiri2006.txt file (it make take you a bit of
-->  time to figure out how to do this; reread the material above)
-->Get rid of all the blank lines by using an autofilter to select
-->  non blank lines; copying and pasting these non-blank lines beneath
-->  the orignal data; turn off filtering; delete the original data, leaving
-->  only the non-blank data.

We can often import information from a web page by first copy and pasting it
into a txt file.

-->Creat a new Microsoft word file named gasoline.doc
-->Google "labor statistics cpi"
-->Click on the first returend value: Consumer Price Index (CPI)
-->Click on CPI Databases
-->In the Averate Price Data click on Top Picks
-->Click Gasoline, All Types
-->Click Retrieve Data
-->On the Change Output Options line, sleect 1990 as the From: year
-->Click the GO to the right of the To: year
-->Select every row in the table  (from Series ID to 2009)
-->Copy this data and in Microsoft Word Paste Special |Unformatted text
-->  (if you just paste, you will get the table with all its formatting
       information)
-->Save this file as a text document
-->Import the data in this file into Excel

When you import this data into Excel, Excel thinks it is a fixed width file,
but press the Delimited radio button (the data is delimited by a single space).
Ignore the top row (before the real data starts. Skip the last column (which
is headed by "Annual" but contains no data).

You might want to try to import this file as fixed width: it almost is, but
there is no way in Excel to fix the problem that the months at the top don't
align with the numbers in their columns. We could instead use a text editor to
insert extra spaces between the months, but it is easier to use Excel to read
this as a space delimited file.

In fact, there is an icon to download this file as a .xls file, but I want you
to get practice copying data from the web into a file, and then importing that
file into Excel.


3) Importing Data Directly from the Web:

We have now discussed how to import data from files, and how to copy
information from the web to files. So, we can use this two step process to
copy data from the web to a file and import that file into Excel. This
technique works quite well.

There is another way to use Excel to import information from the web directly.
It is more direct, avoiding the step of copying information to a file, but my
brief experience is that it sometimes fails to work correctly. It attempts to
import the data automatically, so we don't get to specify whether it is a fixed
width or delimited fil, nor do we get to choose to other options available when
we import text files.

Here is a task for which this method works.

-->Click the Data tab on the Excel ribbon
-->In the "Get External Data" group, click the From Web icon
-->Notife the pop-up window named New Web Query

We will use Google to find some data. Note the black arrows on a yellow
background, which will eventually refer to table of information that we
find on the web.

-->In Google, use the query: irvine census
-->Click the first link: to Irvine (City) QuickFacts...
-->Click "Browse data sets sets for Irvine (city)" (on the right)
-->Click the link "2004 Income and Poverty" (the 7th bullet down)
-->Click the black arrow on the yellow background at the top of this page
-->Click "Import" at the bottom right of the browser

The "Import Data" pop-up window asks where to put the data on the current
worksheet or where to put it on a new worksheet. We will put it at the default:
at A1 on the current worksheet.

-->Click "OK"

It may take a bit of time (some number of seconds) to download the data and
put it into Excel format. For this data it correctly finds 6 columns of data.
Note that unlike importating data files, this information is place inside a
worksheet in the currently open workbook, along with lots of other information
that really isn't part of the database.

Note that if we followed the directions above but instead of choosing "2004
Income and Poverty" we chose the prior bullet, "Historical population counts",
and clicked either black arrow and clicked import, our result would not be so
good: it puts all the data in every line in column A.

But, we could use our less direct technique and copy/paste this data into a
text file, and then import this text file into Excel. Try following this
technique, which requires adding some columns in the fixed width representation
of the file (if you choose all the information in this table; if you choose
just the body of the table, Excel automatically gets all the columns correct).