Part Four of the “Front Counter Mechanics” Business Software series (Import Service, November 2000) described how to retrieve certain facts and figures from shop management pro-grams, then import the data into a Microsoft Excel spreadsheet. This month’s article describes how to import data that Excel doesn’t recognize.
Previous articles described how to use Microsoft WordPad to investigate files and examine their con-tent. If you experimented with viewing files on your own computer, you might have come across some-thing like the screen shown below.
Figure 1 is a WordPad view of the inventory data file exported from a popular shop management system. It’s readable, but it has funny “little box” non-English characters in it. These characters are WordPad’s representation of machine language code. The rest of the characters are readable, so this file can be successfully imported into a Microsoft Excel spreadsheet; but what happens when the file looks like the screen shown in Figure 2?
Figure 2 shows the WordPad view of a customer file from another popular shop management system. With the exception of some ZIP codes, the file appears nearly empty. If you try to import it into a Microsoft Excel spreadsheet, you’ll get nothing but gibberish. The key to unlocking this file lies in its name, “CUSTO.DBF.” The “DBF” extension identifies it as a Data Base File, which imports easily into a Microsoft Access database instead.
Figure 3 is the screen that displays when you double-click the Microsoft Access icon. Unlike Excel, which displays a spreadsheet as soon as you double-click its Desktop icon, Access asks you to open an existing database or create a new one. In this example, first click the “Blank Access database” selection, then click the “OK” button.
Figure 4 shows the next screen display. Access requires naming the new database. The file CUSTO.DBF is a customer information file, so typing the word “Customers” in the “File name” field, and then clicking the “Create” button leads to the screen in Figure 5.
Access stores facts and figures in “tables” the way Excel stores them in a “spreadsheet.” Before you can use the Access Data Import Wizard, you need to open a table. Once you double-click the “Create table by entering data” selection in Figure 5, you’ll see a screen that looks similar to an Excel spreadsheet.
Figure 6 shows the selections once you’ve named and opened a table. Click the “File” menu, then select “Get External Data,” then “Import.” This launches the Access Data Import Wizard. Next tell the Wizard the name of the file you want to import data from.
In Figure 7, click the down-arrow button in the “Look in” field to navigate to the Rogue Data folder, then click the down-arrow button in the “Files of type” field to select files with a DBF extension. Then click the CUSTO.DBF file icon to highlight it. Finally, click the “Import” button. The Access Wizard does the rest. rt Wizard. Next tell the Wizard the name of the file you want to import data from.
Once the Import Wizard finishes, it returns to the screen shown in Figure 6. Nothing looks changed, but when you close the blank table, a new entry, “Custo,” appears in the Objects and Groups window shown in Figure 8. This is the icon for the table you just imported. Double-click the “Custo” icon to view the imported data shown in Figure 9 (page 44).
Importing DBF files is easy because each DBF file comes with a companion file that describes how the DBF file is structured. Access uses this companion file to format the data automatically. DBF files usually include data, such as customer numbers and year-to-date totals, you might not want to include in your Access database. In the example shown in Figure 9, I’ve deleted unwanted columns of data by first right-clicking the column title, then clicking “Delete.”
Microsoft Access is capable of importing many other types of data files. Some of these file types are listed in the “Files of type” window shown in Figure 7. Other importable file types were listed in last month’s installment of ”Front Counter Mechanics.”
Some management systems store data in a file type that isn’t directly importable, yet the database can be translated to a file type that is importable through use of an Application Program Interface (API) such as ODBC (Open Database Connectivity). In this case, you need to ask your software provider for the API and for instructions on how to use it.
Worth the Trouble?
This concludes our series on Business Software. You might ask: “Why bother with this? Why not be content with what I have? I know how to keep in touch with my customers, and they know how to get in touch with me. What’s the big deal with being able to export my data to a standard format?”
The answers to these questions are similar to the answer that might have been given ten years ago to the question “Why should I spend all the trouble and money on a computerized information system when I have everything I need right now in books?”
Your business needs to stay current if it’s going to thrive in the short term or survive in the long term. In 1996, when Import Service published “Caught In the Net,” Brent Black’s “Professional Technician’s Internet Mailing List” had just over 700 members. Today the site has grown to become the International Automotive Technicians’ Network, with over 30,000 members. The ‘computerization’ of automotive repair shops also continued to spread during this period, as a growing number of technicians and shop owners decided to take advantage of the powerful software tools that are available for use in all facets of their businesses.
High-tech gadgets and high-tech business capabilities are here today. Advertisements entice us with offers of receiving e-mail on our cell phones or wireless personal digital assistants, and e-commerce is now an everyday term. The major car manufacturers are spending billions to connect themselves, their customers, and their products via the Internet. It’s now possible for a dealership service department to send individually-tailored messages, reminders and advertisements directly to Internet display screens inside their customers’ cars.
During a software developers’ seminar I recently attended at Microsoft headquarters, a speaker stated: “Everything Microsoft does over the next five years will be .NET-enabled. We’re betting the company on .NET.” If you haven’t seen this acronym before, .NET is what Microsoft is calling its next operating system. The company’s goal is to make information delivery device-independent. Messages will be delivered whenever and wherever they can be: in your car, in your office, at home, on your cell phone, on your pager watch or maybe onto your retinal implant. It’s not a future I find unambiguously appealing, but there’s little I can do to keep it from becoming reality.
No shop management system will deliver all the information you want in the manner you want. The solution will be to provide the information to third-party vendors who will then distribute your messages the way the post office distributes your mail or the way the telephone company distributes your phone calls. If you aren’t able to export your data, that’s a communications solution that won’t be available to you.