Chapter 25: Importing and Cleaning Data
2525
Access files: These files have various extensions, including .mdb and .accdb.
dBase files: These files are produced by dBase III and dBase IV. Excel does not support
dBase II files.When you try to open database files using File ➪ Open, Excel doesn’t actually open the file.
Instead, it creates an external data connection to the table in the database that you select.
Excel supports various types of database connections that enable you to access data selec-
tively. For example, instead of “opening” a database and selecting a table, you can perform
a query on a table to retrieve only the records that you need (rather than the entire table).Text file formats
A text file contains raw characters, with no formatting. Excel can open most types of text
files:CSV: This stands for “comma-separated values.” Columns are delimited with a comma,
and rows are delimited with a carriage return.
TXT: Columns are delimited with a tab, and rows are delimited with a carriage return.
PRN: Columns are delimited with multiple space characters, and rows are delimited
with a carriage return. Excel imports this type of file into a single column.
DIF: This file format was originally used by the VisiCalc spreadsheet. This is rarely
used.
SYLK: This file format was originally used by Multiplan. This is rarely used.Most of these text file types have variants. For example, text files produced on a Mac have
different end-of-row characters. Excel can usually handle the variants without a problem.When you attempt to open a text file in Excel, the Text Import Wizard might kick in to help
you specify how you want the data to be retrieved.To bypass the Text Import Wizard, press Shift while you click the Open button in the Open dialog box.
When Excel Can’t Open a File
If Excel doesn’t support a particular file format, don’t be too quick to give up. It’s likely that others have
had the same problem as you. Try searching the Web for the file extension, plus the word Excel. It’s
possible that a file converter is available, or maybe someone has figured out how to use an intermedi-
ary program to open the file and export it into a format that Excel recognizes.