The only way to get the data of many older programs into Access is to export the data to a fixed-width text file and then import that file into Access. In a fixed-width text file, the same field in every record contains exactly the same number of characters. If the actual data doesn’t fill a field, the field is padded with spaces so that the starting point of the data in the next field is the same number of characters from the beginning of every record. For example, if the first field contains 12 characters, the second field always starts 13 characters from the beginning of the record, even if the actual data in the first field is only 4 characters.
Fixed-width text files used to be difficult to import into databases, because you had to carefully count the number of characters in each field and then specify the field sizes in the database layout or the import program. If the text in any field was even one character off, all records from that point on would be jumbled. That is no longer a problem with Access because the Import Text Wizard makes importing a fixed-width text file simple.
In this exercise, you will import a fixed-width text file into the Suppliers table in the GardenCo database.
USE the GardenCo database and the Suppliers text file in the practice file folder for this topic. These practice files are located in the My DocumentsMicrosoft PressAccess 2003 SBSImportingImportFText folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
On the File menu, point to Get External Data, and then click Import.
In the Files of type list, click Text Files.
Navigate to the My DocumentsMicrosoft PressAccess 2003 SBSImportingImportFText folder, click Suppliers, and then click Import to display the first page of the Import Text Wizard.
The text is displayed in Fixed Width format.
Click Next to display the second page of the wizard.
The wizard breaks the file into fields based on the assumption that a column of one or more spaces extending through all records marks the end of a field.
Use the horizontal scroll bar to scroll through the fields until you get to the two fields that contain phone numbers.
The wizard broke each phone number into two fields because a column of spaces separates the area code from the number. Breaking the numbers this way would be fine if you wanted to store the area codes in separate fields, but you don’t want to do that in this database.
Double-click the dividing line at column 337 to remove it. Then repeat this step for the dividing line at column 367, and click Next.
Select the In an Existing Table option, click Suppliers in the drop-down list, and then click Next.
Click Finish to import the text file into the Suppliers table.
A message appears informing you that the file was imported.
Click OK to close the message box, and then double-click Suppliers to open the table and confirm that 20 records were imported from the suppliers list.
Close the Suppliers table.
CLOSE the GardenCo database.