XL: Fields May Save Incorrectly in dBASE Formats from Excel

ID: Q68914

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel 97 for Windows
Microsoft Excel for the Macintosh, version 5.0
Microsoft Excel 98 Macintosh Edition

SUMMARY

When you save a Microsoft Excel worksheet in a dBASE format (*.DBF), field values may convert incorrectly, depending on the column width and the value of the first record in a field.

MORE INFORMATION

If the First Record in a Field Is a Text Value

Text values in these fields are truncated to a number of characters corresponding to the column width of the field. For example, if the column width is set to 5, and a record field contains the text "abcdefgh," the text will be truncated to "abcde" when saved as a .DBF file.

If numbers are formatted as Text, they will revert back to the Number format and be right justified. Numbers entered with an apostrophe before it will revert to numbers and will be left justified.

If the first record of a field is blank, Microsoft Excel treats it as a text field. This is true even when all other records (blank or not) in that field are formatted as numeric.

Numeric entries in these fields will convert differently, depending on the column width:

If the column is wide enough to display the number, the number will be converted to text in the DBF format. If the number is formatted as "General" or "0," it will also be rounded to an integer before being converted to text.

If the column is not wide enough to display the number, it will display "#" symbols instead. In the DBF format, this value will be saved as a string of "#" symbols. For example, if a field record has a value of 1234567, and the column width is set to 4, Microsoft Excel displays "####". In the DBF format, this value will be saved as "####".

Note that field names are NOT truncated unless they are more than 10 characters long. dBase imposes a limit of 10 characters on a field name.

If the First Record in a Field Is a Numeric Value

All other records in these fields are assumed to be numeric also. Any records in these fields that contain text values will be lost when saved in a DBF format. These records will be blank when the file is reopened.

Numeric values in these fields will convert as follows:

If the column width is wide enough to display the number, it will be saved as a number. If the number format is "General" or "0," the number will be rounded to an integer first.

If the column is not wide enough to display the number, it will be lost when saved in the DBF format and the record will be blank when the file is reopened. This may also result in the error message "Unable to read file" when trying to reopen the DBF file.

In addition, if the records are not contiguous, you may lose data when saving the data in a DBF format. Any changes made to a DBF file after the initial save, may not be saved.

Additional query words:


Keywords          : kbfasttip xllist 
Version           : WINDOWS:3.0,4.0,5.0,7.0,97; MACINTOSH:5.0,98
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: March 22, 1999