Part Two of our “Front Counter Mechanics” series on Business Software (September 2000) described how to create a customer mailing list using Microsoft Excel. If you have built an Excel spreadsheet, you know that project is comparatively easy; the real work comes in filling in the data. If your repair business is like most others, you have the names, addresses and car models of thousands of customers. Getting them all into a spreadsheet is a daunting task that will take even an expert typist many hours at the keyboard to accomplish. There’s hope, though, to avoid this drudgery if you already have the information in your shop management database. You might be able to export and import the data into Excel electronically.

In theory, all electronically-stored data can be exported from one database and imported into another. In reality, depending upon the database software, the task of data export ranges from being absolutely easy to being completely unfeasible. Importing data into Microsoft Excel is close to being ‘absolutely easy.’ Unfortunately, judging from my experience as an applications developer and consultant, the task of exporting the data from most shop management systems is closer to being ‘completely unfeasible.’

How easy is data-export with your shop management system? This month’s “Front Counter Mechanics” will help you find out. It describes what to look for, and how to evaluate the exported files if your shop management software includes an export utility. Next month’s installment will describe several methods to ‘crack the code’ when no export utility is provided. Future articles will describe how to import these files into Microsoft Excel and other Microsoft Office applications.

But first, let’s address an obvious question: “Why bother to export my customer list to Excel if I already have it in my shop management database?”

Here are four reasons:

1) Customer information is the backbone of your business. Storing it in more than one database is additional insurance against its loss.

2) Excel is a universal medium of data exchange. Excel spreadsheets can be exported in over a dozen different file formats. These formats can be used by desktop publishing programs, word processing programs, marketing programs, demographic analysis programs and other business management programs. You can even swap the Excel-exported data files between a PC and a Mac.

3) Your marketing options are improved. Your management program may send reminders, follow-up letters and print address labels; but does it always perform these functions in just the way you want it to? Can it integrate with your web page?

4) Data export is a good test of your shop management system. The system’s End-User License Agreement may give the manufacturer control of the software, but you should have control of the data. If your current management system allows you to export your data successfully, you can’t be held hostage if you decide to switch to a new system sometime later.

Step One in the data-export hunt is discovering whether your current management system has an export utility. If it does, it probably won’t be labeled “Export Utility.” Explore the program and its documentation. Look for selections such as “Backup,” “Create List,” “Print to File” or “Data Utilities.” If you don’t find anything, call the software company’s support line and ask. Export capabilities are frequently undocumented.

Any selection in your program that gives a choice of naming a file or directing output to a disk drive is worth exploring. Once you know the name and location of the output file, you can examine it. How you do this is described in the figures that follow, but first a few words about file types and how to read them.

The most common format for data export is ASCII (pronounced “ask-key”), which is an acronym for American Standard Code for Information Interchange. ASCII files usually end with the file name extension .txt, but no rule says they have to. Other common ASCII file name extensions are .prn, .dat and .bak. If in the following exercises you see no file name extensions list-ed when you’re searching for files, display them by single-left clicking the toolbar “View” selection, then single-left clicking “Details.”

ASCII files are plain-text files. They include the standard characters of the English alphabet and the numerals, and they can be read by text editors. If you own a computer running Microsoft Windows, you already own two text editors: Notepad and WordPad. WordPad is the text editor of choice; it’s friendlier than Notepad, and it can open larger files. Although ASCII files can be opened and read by word processor programs such as Microsoft Word, it is best not to do so. Word processor programs, if allowed to do so, can add formatting instructions to the file, additional information that can impair the usefulness of the data.

You can’t corrupt a file simply by opening it with Notepad or WordPad, but you can corrupt it if you change the file and then save it. To be safe, always cancel out of the text editor window by clicking the “Close” box (X) in the upper right-hand corner of the text editor window. If you’re asked “Do you want to save your changes?” select “No.”

Data exported as ASCII text generally comes in one of three forms: Block, Fixed-Length Field, and Delimited. Only the last two of these can usefully be imported into Microsoft Excel. All three forms are readily examined by Notepad or WordPad. The figures that follow show you how. When the single word “click” is part of the instructions, this always refers to a single-left mouse click.

If you can hear your computer writing the file to your hard disk, but you don’t know the name of the file, or where it’s stored, use the Windows 98 Find utility. Click the Windows Start button. Move the mouse pointer over Find. Click “Files or Folders.”

The first tab is “Name & Location.” Since you don’t know the name or location of the file, we’ll choose the “Date” tab. However, note that “Look in” is set to the C: drive. If your initial search is unsuccessful, you should return to this tab and set “Look in” to a different drive, then repeat the Date search.

Click the “Date” tab. Click “Find all files.” Click the down arrow button to change “Modified” to “Created.” Click the “between” selection. Use the down arrow buttons to select the current date for both the “between” and the “and” fields. Click “Find Now.”

A list of files created on the cur-rent date will be displayed in a window inside the “Find: All Files” window. Double-left click the file icon (arrow in Figure 4) to open the file for viewing. The file in this example is a Block file with a .dat file name extension. Block files are explained in Figure 6.

If you’ve never opened a .dat file before, Windows will ask you how to open it. Use the down arrow button to scroll down the list until you find WordPad, click on it, then click OK.

When you open them, block files look like long lists of address labels. Each customer record is in a separate block of several lines each. Excel can import block files – all you do is Copy and Paste – but it will think each line is a separate customer record. In this example, it would think “Crombie Allen” and “486 Ponderosa Blvd NE” were each customer names.

If you already know where to find the file you want to examine, here’s how to open WordPad: Single-left click the Windows “Start” button. Move the mouse pointer over “Programs.” Move the pointer over “Accessories.” Click “WordPad.”

Click “File” on the tool bar beneath the “Document – WordPad” title bar. Next click “Open.” Then use the “Open” dialog window to find the exported file you wish to examine. Unless you told the program to do otherwise, this file will usually be written in the same folder as your database.

If you don’t see the file you want, click the down arrow button in the “Files of type:” box, then click “All Documents.” For help with the “Open” dialog, click the “?” on the “Open” title bar, move the mouse to the “Look-In:” box, then click.

This is an example of an ASCII file in Fixed-Length Field format. This format is also sometimes referred to as “Tab Delimited.” This file imports into Microsoft Excel very easily.

This is the same set of customers in ASCII Delimited format, which is also known as “Comma Delimited.” Each field within the record is separated by a pair of quotes and a comma. This is a common format for .prn files. It also imports into Excel very easily.

That’s it for this month’s “Front Counter Mechanics.” Next month’s will teach you how to be a data sleuth.