TEXT FILES AND NON-TEXT FILES
There are many ways to represent data on a computer.
divide is between text files and non-text files.
Text files can
be directly read using a text editor such as Notepad in Microsoft
These files often have extension .txt but can often have other extensions (a common one we will use is .csv).
Example: For the text file carsdata.txt Notepad
Instead non-text files can only be read using
In this example an Excel file can be meaningfully read using the Excel program.
The most common data formats used to store data in
files that can be
read data into Excel are:
Common ways to input data into Excel are:
1. TYPE IN DATA INTO A NEW EXCEL WORKSHEET
Open Excel, for example from Windows Start Menu |
This immediately puts you into a new Excel worksheet, or prompts you to ask for a new Excel worksheet.
Consider entry of the following data on the number
of cars in a
(first column) and the number of persons in the household (second
Type the data into cells A1:A5 and cells B1:B5. (Click on cell A1 and enter 1 and then enter, then 2 and enter ....)
Then give headers to the two columns.
Put cursor in cell A1 and right-click and select Insert and then select Entire Row.
Then in the new blank cell A1 type CARS and in the new blank cell A2 type HH SIZE.
Now headers (CARS and HH SIZE) are in row 1 and data
are in rows 2
6. The final data are:
Next give the worksheet a name.
Go to the sheet tab at the bottom, which may be called Sheet 1, right-click and select Rename and give the worksheet a name.
Finally save the workbook by using Office Button and
Save As if a new workbook
or Office Button and Save if saving an existing workbook.
If typing in a lot of data it is obviously best to save the workbook a number of times before typing is finished.
2. READ IN DATA FROM AN EXISTING EXCEL WORKBOOK OR WORKSHEET
This is the easiest, if you have approriate
version(s) of Excel.
Open Excel, for example from Start Menu | Programs.
Left-click the Office Button, select Open and give the filename or browse for the file you want to input.
Usually the filename will have extension .xls or .xlsx
Excel 2007 should read all previous versions of Excel files, though not vice-versa.
For example, read in the file carsdata.xls
(which is an Excel 97-2003 workbook).
3. COPY DATA FROM AN EXISTING EXCEL WORKSHEET
Suppose we are already in an existing worksheet
within an existing
Right click on the Worksheet Tab at the bottom.
This yields the Move or Copy dialog box
Give the new worksheet an appropriate name by going
to the sheet tab
at the bottom, right-click and select rename and give the worksheet a
4. READ IN DATA FROM A COMMA-SEPARATED VALUES FILE
A comma-separated values file is a text file where
each row is a spreadsheet row and each entry, separated by a comma, is
a column entry for that row.
The first row provides names for the columns of the spreadsheet.
These files are given extension .csv.
Excel automatically reads in files with extension .csv
Many other programs are able to read in data a .csv
file, but not data in an .xls file.
It is a standard format for transferring data across different programs.
For example the file carsdata.csv
is a text file with the following entries
Related text files include tab-delimited text
and space-delimited formatted text.
5. READ IN DATA FROM AN EXISTING TEXT FILE5a. Obtain text file with data from the internet
Skip this step if you already have the data file carsdata.txt on
In your web browser click on file carsdata.txt
The web browser displays
Choose File / Save As in your browser to save the
data as carsdata.txt in
of your choosing, e.g. in c:/Temp
5b. Enter the text file with data into Excel
Now open Excel and from the Office Button choose Open
At the bottom of the Open dialog box choose the option Files of type | All Files (*.*) and navigate to highlight file c:/Temp/carsdata.txt and click Open.
This yields the Text Import Wizard
The challenge is to places the first and second
numbers into separate columns, rather than
combined into one column.
In text import import wizard
So Step 1: choose Delimited and Start Import at Row
1 and hit Next.
This yields Step 2. The default Delimiter is Tab. Change this to Space.
Then check that the Data preview has correctly split up the data, as is the case below.
This yields step 3. In this case leave the column data format as general.
This should give you an Excel worksheet with 2 columns and 5 rows in cells A1:B5.
It is a good idea then to save the data as an excel
in a workbook, and save the file.
Also add the data headings for CARS and HH SIZE (see 1. above). The final data are
6. READ IN DATA FROM FILES IN OTHER FORMATS
Excel will read in data from a number of formats including
To save a file select Home Button and Save or save As
Excel will save data in a number of formats including
Common choices are
For further information on how to use Excel go to