Microsoft KB Archive/214131

= XL2000: Excel Fields May Save Incorrectly in dBASE Formats (.*dbf) =

Article ID: 214131

Article Last Modified on 10/8/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q214131





SUMMARY
When you save a Microsoft Excel 2000 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.



Case 1: The First Record in a Field Is a Text Value

 * Text values in these fields are truncated to a number of characters that correspond 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 is truncated to "abcde" when saved as a .dbf file.
 * If numbers are formatted as Text, they revert back to the Number format and are right-justified. A number entered with an apostrophe in front of it reverts to numbers and is left-justified.
 * If the first record of a field is blank, 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 are converted differently, depending on the column width:
 * If the column is wide enough to display the number, the number is converted to text in the DBF format. If the number is formatted as General or 0, it is also rounded to an integer before being converted to text.
 * If the column is not wide enough to display the number, it displays # symbols instead. In the DBF format, this value is 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, Excel displays ####. In the DBF format, this value is saved as ####.

NOTE: Field names are not truncated unless they are more than 10 characters long; dBase imposes a limit of 10 characters on a field name.

Case 2: 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 are lost when saved in a DBF format. These records are blank when the file is reopened.

Numeric values in these fields are converted as follows:  If the column width is wide enough to display the number, it is saved as a number. If the number format is General or 0, the number is rounded to an integer first. If the column is not wide enough to display the number, it is lost when saved in the DBF format, and the record is blank when the file is reopened. This may also result in the error message

Unable to read file

when you try to reopen the DBF file.

Applicable to Both Cases

 * If the records are not contiguous, you may lose data when you save the data in a DBF format. Any changes made to a DBF file after the initial save may not be saved.
 * If the column header is invalid or too small, the headers are saved as N1, N2, and so on.

Additional query words: XL2000

Keywords: kbhowto kbinfo KB214131

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.