If you're obtaining data from a mainframe computer and importing it into Access, you are probably dealing with fixed length fields with no field delimiters, and fixed length records. DISC normally appends a CR-LF (carriage-return and line-feed) to the end of the record during the conversion process, as Access requires it. This TechTalk paper suggests two ways to import fixed field data into a new Access table. However, Access has many ways to import fixed field data, and you should use which ever method you are most familiar with. This article was written in 2001 for Access 2000 and Access 97, and has not be verified for later versions of Access.
The two methods below differ only slightly, in the method of defining field positions. Using the first method, you visually place vertical lines between fields. This method is simple and allows you to visualize the data. But, if your record is large, or has many numeric fields in a row, you may get lost in the record. Method 2 specifies the starting position and length of every field, in an import specification table. While you can't see the data as you are defining the fields, this method is less prone to position errors in large layouts. These methods work in Access 97 and Access 2000; earlier versions of Access use different import methods.
| 1. | Make sure your fixed-width file has a .TXT or .ASC extension, and that records are terminated with CR, LF, or CR-LF. |
| 2. | Start Access. |
| 3. | Open (or create) the database you want to create the table in. |
| 4. | Click File, Get external data, Import...
The "Import" box should open. |
| 5. | At the bottom of the Import box, under "Files of type:" select "Text Files" |
| 6. | Locate the file you want to import and click on it. |
| 7. | Click the "Import" button.
The "Import Text Wizard" window should open. |
| 8. | Make sure "Fixed Width" is checked. |
| 9. | Press "Next>"
The Wizard will attempt to determine your field breaks, and will likely get many wrong. |
| 10. | Using your layout for the fixed field file, put a field break (a line) between fields.
To ADD a field break line, click between the fields. To MOVE a field break line, click on it and drag it. To DELETE a field break line, double click it. |
| 11. | Double check to make sure you have defined all the fields, then click "Next>" |
| 12. | For the prompt "Where would you like to store your data?" check "In a new table." |
| 13. | Click "Next>" |
| 14. | Repeat the following for every field in the table:
Click on the heading (Field1, Field2, etc.) at the top of each field to select that field. In the "Field Options" area, type the field name and select the field type (see the notes below). If you do NOT want to import this field, check the "Do not import field" box |
| 15. | Click "Next>" |
| 16. | Choose the field that is your primary key. Or let Access assign a new field as a primary key. If you don't need to relate this to another file, select "No primary key" |
| 17. | Click the "Advanced..." button. |
| 18. | Select the proper "Date order"
Check or un-check "Four digit years" as appropriate. Check or un-check "Leading zeros in dates" as appropriate. Type the "Date delimiter". If your data has no delimiters, delete the / so the box is blank. This is a good place to double check your definition against your layout, and mark fields to skip. |
| 19. | When you are sure the import specification is correct, select "Save as..." and save the import specification for later use.
(If you need to import this file again, you can come to this point and click on "Specs..." to load the import specification.) When you are done, click OK to return to the "Import text Wizard" box. |
| 20. | Type the name of the new table you want to create. |
| 21. | Click "Finish" and the file will start importing. |
| 22. | Read the message that appears when the import is done. If there were import errors it will tell you where they are listed.
Open and inspect that file for import errors. |
| Tip: You may want to perform steps 10 and 14 at the same time to avoid making two passes on the file.
If you find you keep losing your position in the record, try method 2, below. |
| 1. | Make sure your fixed-width file has a .TXT or .ASC extension, and that records are terminated with CR, LF, or CR-LF. |
| 2. | Start Access. |
| 3. | Open (or create) the database you want to create the table in. |
| 4. | Click File, Get external data, Import...
The "Import" box should open. |
| 5. | At the bottom of the Import box, under "Files of type:" select "Text Files" |
| 6. | Locate the file you want to import and click on it. |
| 7. | Click the "Import" button.
The "Import Text Wizard" window should open. |
| 8. | Make sure "Fixed Width" is checked. |
| 9. | Click the "Advanced..." button. |
| 10. | Select the proper "Date order"
Check or un-check "Four digit years" as appropriate. Check or un-check "Leading zeros in dates" as appropriate. Type the "Date delimiter". If your data has no delimiters, delete the / so the box is blank. |
| 11. | Complete the layout for your file by typing the field name, data type, starting position
and length of every field. (See the notes below on data types.)
If you don't want to import a field, check the "Skip" box on the right. (You still must define the field, to account for it's space in the record.) When you are done, click OK to return to the "Import text Wizard" box. |
| 12. | Click "Next>" |
| 13. | At this point you may inspect your field breaks visually, if you wish. |
| 14. | Click "Next>" |
| 15. | For the prompt "Where would you like to store your data?" check "In a new table." |
| 16. | Click "Next>" twice. |
| 17. | Choose the field that is your primary key. Or let Access assign a new field as a primary key. If you don't need to relate this to another file, select "No primary key" |
| 18. | Click the "Advanced..." button. |
| 19. | Select "Save as..." and save the import specification for later use.
(If you need to import this file again, you can come to this point and click on "Specs..." to load the import specification.) When you are done, click "OK". |
| 20. | Click "Next>" |
| 21. | Type the name of the new table you want to create. |
| 22. | Click "Finish" and the file will start importing. |
| 23. | Read the message that appears when the import is done. If there were import errors it will tell you where they are listed.
Open and inspect that file for import errors. |
One of the most common errors in converting data to Access is to use greater precision than required. Using a double precision floating point data type for a small integer number increases storage space and slows processing. Try to use integer data types whenever possible, and use single precision for most other numbers to save space and calculation time. Use the Currency type for currency, as it's less prone to rounding errors.
Use the following table along with your record layout to determine appropriate data types. For example, if you have a four digit integer numeric field, then you know the value in that field can never be more than 9999, and an Access Short Integer, which will hold up to 32767, is large enough. For a general discussion of data types available in Access, see "Field Data Types Available in Microsoft Access" and "FieldSize Property", both in the Access help.
Access has three integer data types:
| Data type | Size in bytes | Range and use |
|---|---|---|
| Byte | 1 byte | Byte contains values from 0-255 and can be used when you have one or two digits in the field, and the value is not negative. However, Access cannot use the byte data type in some functions, so you may want to use the Integer data type. See the Access documentation for more information. |
| Integer (short integer) | 2 bytes | Integer contains values from -32,768 to +32,767 and should be used when the field is an integer four digits wide or less. |
| Long Integer | 4 bytes | Long integer contains values from -2,147,483,648 to +2,147,483,647 and should be used for integers over 4 digits, but not over 9. |
Access offers three Real data types:
The choice of which to use depends on both the range of values in the data and the precision you need. Most values will be satisfied by a single precision number, which has a range af approximately plus-and-minus 1*E-45 to 3*E+38, and a precision of 7 digits. If you have really huge numbers, over 1*E38, or perform complex calculations (such as scientific) you may need to use double precision floating point, which has a range af greater than 1*E308, and a precision of 15 digits, but most of the time it's unnecessary. The decimal data type has a more limited range of about 1*E28, but has a greater precision of 28 digits. See the "FieldSize Property" in the Access manual for more details on these data types.
Access' currency type uses fixed-point (integer-like) calculations to avoid the rounding errors caused by floating point data types. You should use the currency type for all currency calculations within its range of 2, 3, or 4 decimal places. The currency type holds up to 15 digits to the left, and 4 digits to the right of the decimal.
Try to convert dates to the
Access Date/Time data type whenever possible, as this will permit date
calculations and range tests later on. Access can import several
date formats, including two and four digit years, and several delimiters,
or no delimiters. Be aware that Microsoft claims Access 2000 cannot
import dates in the YYMMDD format (see knowledge base article Q209716),
although we have not experienced this problem ourselves.
The following limits are important to keep in mind when importing large files into Access:
| Parameter | Access 97 | Access 2000 |
|---|---|---|
| Number of fields in a table | 255 | 255 |
| Number of characters in a text field | 255 | 255 |
| Number of characters in a record
(excluding memo fields & OLE objects) |
2000 | 2000 |
| Maximum table size | 1 GB | 1 GB |
| Maximum MDB size | 1 GB | 2 GB |
If the file you need to import is larger than some Access limit, you should first consider removing unneeded fields to reduce the size. If the file is still too large, then you'll need to split it into multiple tables, generate a key, and relate the tables via the key. In Access 2000 the total of all the tables can be larger than the 1 GB table limit, but the MDB cannot be over 2 GB.
The size of the MDB includes
all the indexes, queries, forms, reports, and macros, so the amount of
actual data you can store in tables in Access 97 is less than 1 GB.
Be sure to leave some room for future queries, reports, etc. In Access
2000 the MDB size has been increased to 2 GB, while the maximum table size
is still 1 GB.
Please review the following four issues before you start your import.
The text file you are going to import must have record delimiters, or the import will fail. Access will accept CR (carriage return), LF (line feed), or the standard MSDOS CR-LF pair.
Microsoft states that when importing a text file, "...all the records must contain the same number of characters". (See their Knowledge Base article Q209698). This will normally be the case, but there are some programs that "trim" trailing spaces from records (see our Tech Talk article Importing Variable Data Files Into Access ). Despite Microsoft's requirement, we have not found this to be a problem, as long as it's handled as described in our article.
When converting from a foreign computer system, especially from an EBCDIC mainframe file, it's common to find control codes in the file. Control codes are any non-printable character. Some control codes, such as CR-LF at the end of the record, are both proper and required, but control codes in the middle of records usually cause problems. (See our article Dealing With Binary Junk in a File.) If DISC has converted your file, and you have told us it's going into Access, we will fix any such codes, so that your file imports correctly.
Control codes can cause many different problems. Most of them will simply import into the field of the record where they occur, and usually show up as a square box, and the rest of the record and the file will continue to import correctly. However, some control codes are more serious. A CR, LF, or CR-LF pair in the middle of a record will cause Access to stop importing that record. The next character after the CR or LF will then become the first character of the next record, causing the original record in the text file to become two records in Access. Obviously this screws up both Access records, and may cause import errors to be reported.
The most serious control code is a control-Z (1A hex), which denotes the end of the file to some versions of Access. Access 97 (and prior) will stop importing the file when it encounters a control-Z, and all remaining records will be lost. Access 2000 now treats a control-Z the same as all other control characters and will continue importing the file. However, this may cause problems at the end of the file -- see below.
If you assign a primary key, or sort the file, control codes may cause the file to sort differently than you expected. For example, a control code in a key field in the middle of the file may cause that record to sort to the top of the table. This is because control codes typically have low values, lower than all other characters.
A related issue is bytes with the high bit set. These are less common than control codes, but are frequently found in files converted from EBCDIC to ASCII. They generally display as graphics characters and may cause similar problems. These values are higher than all printable characters, so often sort to the end of the table.
Some fixed-field data files may end with one or more control-Z bytes (1A hex) after the last record in the file. Control-Z is an end-of-file marker, and is used by many programs. This is a carry-over from the days of CP/M, which required a control-Z to mark the exact end of the file. Most early MSDOS programs were adapted from CP/M programs, and continued to use, and require, the control-Z marker.
Access 97 properly recognized
that this was the end-of-file mark, not data, and stopped importing when
it saw a control-Z. Access 2000 behaves differently, and the control-Z(s)
will end up creating another record(s) at the end of the Access table.
Depending on the type of field(s) you try to import the control-Z into,
this may create import errors. If you have assigned a field as a
primary key and a control-Z is imported into that field, that record will
probably sort to the beginning of the table, causing the first record
you see, not the last, to appear to contain bad data.
We believe we have discovered an import bug in Access 2000 which causes records to be imported out-of-sequence. We have reported this to Microsoft. The following paragraph describes our experiences.
In one instance we imported a fixed-field file which contained about 300,000 records into Access 2000. The text file was checked for control codes and other problems, and was clean. We did not assign a primary key. When the import had finished, records 175,304 through 175,308 of the text (imported) file appeared as the first five records in the Access table. Records 1 through 175,303 of the text file appeared as records 6 through 175,308 in the Access table, and the remaining records, 175,309 through 300,000 were in the right place. In other words, the import process moved five records from the middle of the text file to the front of the table. Subsequent passes on the same file, using the same import 'spec caused different records of the text file to be shifted. The out-of-place records always appeared as the first few records (typically 1 to 40) in the Access table. Sometimes the file imported correctly. In no case were any records lost or duplicated, just moved.
We have never seen this problem occur in Access 97, and the problem file above imported correctly into Access 97.
Update August 2002: We have further discovered that the shifted records do not always appear at the front of the Access table. We have recently seen some shifted records part way into the table. This makes it harder to identify a bad import. If maintaining the original order of the records is important to you, we suggest you add a record number to the data before importing to Access, then sort the table on the record number.
This import bug also applies
to importing an Access table, so you cannot solve the problem by importing
to, say, Access 97, then importing the 97 table into 2000.
For more articles on data conversion, see our TechTalk Index.
Disc Interchange Service Company, Inc.
15 Stony Brook Road
Westford, MA 01886