Analyzing Business Data with Excel

Chapter 10. Importing Data

Data is the raw material for everything Excel does, and the first step in any job is getting the data onto a worksheet. Excel can import data from almost any kind of data source, and in this chapter we look at the most common data import situations with Excel.

The applications in this book start with the data already on a worksheet called Data. This chapter will demonstrate ways to build a backend process to capture the data and update the Data sheet in the applications.

Text Files

Text files are the simplest kind of data to import. These files are common and can come from any kind of system. In some cases older mainframe systems have produced a file for years and an Excel application uses the file as input. When the time comes to rewrite the mainframe application it is not difficult for a modern server to create the same file. It is common to find an old data-sharing relationship based on text files even with today’s technology. The advantages of this data-sharing scheme are simplicity, size, and stability. Excel understands two kinds of text files.

Fixed Length Files

In a fixed length file, every record or row has the same number of characters. Each data element, such as a name or phone number, has a fixed number of characters. If a name field is set up with a length of 25 characters and a name comes in that is too long, the extra characters are lost. If the name is less than 25 characters long, blanks are inserted to make up the difference. This kind ...