Part Three of our Business Software series (Import Service, October 2000) described how to export data from shop management pro-grams with built-in export utilities. This month’s column describes how to retrieve data when your shop management program lacks an export utility, as well as how to import data into Microsoft Excel and Microsoft Access.

When your shop management system has no built-in export utility, you have to hunt down and load the data from the management system files themselves. This requires the answers to a couple sets of What?, Where? and How? questions. The first set is: “What is it you hope to retrieve? Where is it stored? And how is it stored?” The second set is: “What do you import the data with? Where do you import it? And how do you import it?”

For purposes of this article, what we hope to retrieve is a customer list. In my experience, the most ‘retrievable’ lists are your customer, vehicle and inventory lists. Retrieving repair history lists is usually not feasible due to the complexity of the database structures.

Discovering where the list is stored depends upon having some idea of how it’s stored. In turn, discov-ering how it’s stored depends upon knowing what to look for. Here’s where Microsoft Office and the second set of What?, Where? and How? questions come into play. The import utilities in Microsoft Excel and Microsoft Access tell you what to look for, what to do with the list once you’ve found it, where to import it, and how to import it.

Excel’s import utility is less powerful than the one provided by Access. Excel can import ASCII text files only. (If you missed last month’s article, ASCII text files are files that include the standard charac-ters of the alphabet and the Arabic numerals).

Despite this limitation, Excel is an excellent importation tool because ASCII files are the most common form of database file exchange.

Access can import a number of different file for-mats in addition to ASCII text. File formats are gen-erally, but not always, identifiable by the “filename extension,” the set of characters to the right of the period in a filename. For example, file “CUS-TOMER.DAT” has “DAT” as the filename extension. Filename extensions are usually three characters long, but sometimes they’re shorter.

A list of filename extensions to look for is shown in Figure 1. Although the extension usually identi-fies the type of file, it doesn’t have to. The exten-sions listed exist according to common usage only, not according to a standard imposed by the operat-ing system. Files can have virtually any extension, even no extension at all.

Now that you’re armed with likely filename extensions, you’re ready to start hunting data. If your hunt is successful, you gain the ability to use your data however you wish. If your hunt is unsuccessful, you’ll have lost nothing other than time and equanimity — if you follow two simple precautions:

• Backup your original data! All database systems include some instructions for backing up your data. Find them and follow them first.

• Always close a file without saving it after you’ve opened it for viewing. The facts and figures in a shop management database are stored as files on the hard drive. If your shop management system has an export utility, these facts and figures can be copied to separate files that are safe to experiment with. If, however, the management system does not have an export utility, the database files themselves must be examined. If you inadvertently change one of these files in the process of examining it, the database may no longer be usable. If you’re unsure of how to safely examine a file, please review the instructions for using Microsoft WordPad in last month’s “Front Counter Mechanics.”

Let’s get started. You know the data you wish to retrieve is stored somewhere on your computer, the trick is to find it. Fortunately, your shop manage-ment system’s Windows Desktop icon can reveal where to start looking. Place the mouse pointer on the management system’s Desktop icon, then right-click once. (Note: Whenever the single word click is part of the instructions in the figures that follow, it always refers to a single click of the left mouse but-ton. Similarly, double-click refers to a double click of the left mouse button.) This pop-up menu displays whenever a Windows Desktop icon is right-clicked once. Click the Properties button to view a menu similar to that shown in Figure 3. Note: If your program is DOS-based, select the “Program” tab instead of the “Shortcut” tab in the Properties menu.

These are the Shortcut Properties of the Microsoft Calculator icon. The “Target” field (DOS-based programs: “Cmd line”) shows the program’s location. The backslash characters (“\”) designate folder levels. The lowest level is on the right. Thus, “C:\WINDOWS\CALC.EXE” means the program CALC.EXE is in the Windows folder on the C: drive.

For the purposes of this example, I’m saying the files of interest are in the Rogue Data folder on the C: drive. The screen shown above was reached by first double-clicking the “My Computer” icon on the Windows Desktop, then double-clicking the C: drive icon. Double-clicking the Rogue Data folder revealed the screen in Figure 5.

The Rogue Data folder contains several files with extensions that match the ones listed in Figure 1. It also contains two other folders. If I don’t find what I’m looking for in this folder, I’ll want to open and explore the other two folders. The file “Cstmrs.dat” looks promising. Double-clicking it revealed the screen in Figure 6.

Windows is asking how to open the “Cstmrs.dat” file. I’ve scrolled down the list and clicked WordPad. Note that I’ve also clicked (un-checked) the “Always use this program to open this file” box. Otherwise, Windows would always try to open a “DAT” file with WordPad — something I may not want. Clicking the “OK” button revealed the screen in Figure 7.

This is the easiest of files. It’s an exported list of the names I used in last month’s “Front Counter Mechanics,” but it’s also a useful example of how to import an ASCII text file into Excel. This file is “fixed-width,” meaning everything is neatly arranged in columns. The next four figures show how to import it into Excel.

Now that I know which file to import, I open a new Workbook in Excel, then select “Data” from the menu bar, then “Get External Data,” then click “Import Text File.” The Windows Browse window pops open. I navigate to the Rogue Data folder using the down-arrow button in the “Look in” field (Figure 9).

At first, no files displayed when I opened the Rogue Data folder because the “Files of type” field was set to “Text Files (*.txt).” I clicked the down-arrow but-ton in the “Files of type” field, then clicked “All Files (*.*).” Double-clicking “Cstmrs.dat” revealed the screen in Figure 10.

This is the first of four Excel Import Wizard screens. It lets you preview the data you wish to import and change how it’s imported. In this case, the Wizard calculated everything correctly. I simply clicked through the four screens with “Next,” “Next,” “Finish,” then “OK” to reveal the screen shown in Figure 11.

Here is the finished customer list, accomplished with no typing, just clicking. All that remains is to click the floppy disk icon on the toolbar to save the list to a file. Data import is rarely this easy, though. So let’s try something more challenging next.

Here is the WordPad display of the “Cust.lst” file, which is an actual database file from a formerly popular but now defunct management system. It’s a “fixed width” file like the one shown in 6.

In this screen, I’ve placed the cursor between the “2” and “HAFFNER” and clicked to insert a col-umn break. This enables me to tell the Wizard to ignore the first column in the next step, shown in Figure 14.

Here I’ve clicked inside the first column to highlight it, then clicked the “Do not import column (skip)” button. I’ll repeat this process for the columns that show telephone numbers, then click the “Finish” button to reveal the screen shown in Figure 15.

Excel is asking where to put the data. I’ll click the “OK” button to put it in the current spreadsheet, starting at column A, row 1. Once I’ve done that, the “Cstmr.lst” file will be imported and displayed. As in Figure 11, the only remaining step will be to click the floppy disk icon to save the list.

That’s all there is to importing ASCII files. Next month’s “Front Counter Mechanics” will describe how to import non-ASCII files.