Microsoft KB Archive/308686

= ACC2000: Special Characters in Field Names Are Changed When You Export a Table to a Different File Format =

Article ID: 308686

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q308686



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SYMPTOMS
When you try to export a table in a Microsoft Access database (MDB) or a Microsoft Access Project (ADP) to an Excel, an HTML, or a text format, and if one or more of the field names have a number sign (#), the number signs may be converted to periods.

In an ADP, a period in a field name may also be converted to an underscore character (_).



RESOLUTION
There are five possible resolutions.

Method 1:
Do not use special characters such as the number sign and the period (ADP) in table and field names.

Method 2:
Before you export the data, change the field names in Access so that they do not have number signs.

Method 3:
Modify the resulting exported file so that the field names have the characters that you want.

Method 4:
Save the file as formatted. For an example of how to do so, follow these steps:
 * 1) Click the table that you want to export.
 * 2) On the File menu, point to Export.
 * 3) In the Save as type box, click Text Files or one of the Excel file formats, click to select the Save formatted check box, and then click Save.NOTE: For HTML files, use an HTML template to save the file as formatted. You can create a blank HTML template by creating an empty text file and then changing its extension from .txt to .html.

Method 5:
Use a custom export procedure.

The following example demonstrates how to export a table that has field names that have number signs to a Text File.  Add a reference to the Microsoft Scripting Runtime library (scrrun.dll).  Add the following code to a module in the database or project. NOTE: The routine accepts two arguments: the name of the table (or query) that you want to export, and the full path and name of the final text file that is exported: Public Function FixExportedFieldNames(strTableName As String, strFileName As String)

Dim fso As New FileSystemObject Dim ts1, ts2 As TextStream Dim strTxtLine, strTempFileName As String strTempFileName = &quot;c:\FixFieldNamesTemp.txt&quot; DoCmd.TransferText acExportDelim,, strTableName, strFileName, True

' Open files. Set ts1 = fso.OpenTextFile(strFileName) Set ts2 = fso.OpenTextFile(strTempFileName, ForWriting, True, TristateFalse)

' Replace the period on the first line with a number sign. strTxtLine = ts1.ReadLine strTxtLine = Replace(strTxtLine, &quot;.&quot;, &quot;#&quot;) ts2.WriteLine strTxtLine

' Loop while not at the end of the file. Do While Not ts1.AtEndOfStream strTxtLine = ts1.ReadLine ts2.WriteLine strTxtLine Loop

' Close the files. ts1.Close ts2.Close DoEvents

' Overwrite original file with new file fso.CopyFile strTempFileName, strFileName, True fso.DeleteFile strTempFileName

End Function





MORE INFORMATION
You see this behavior when you export as follows:
 * 1) To Excel in the user interface, and you do not click to select the Save Formatted check box in the Export Table dialog box.
 * 2) To text in the user interface, and you do not click to select the Save Formatted check box in the Export Table dialog box, and you include field names in the Export Text Wizard.
 * 3) To Excel by using the TransferSpreadsheet action or method.
 * 4) To text by using the TransferText action or method, and you have the HasFieldNames property set to True.


 * 1) To HTML by using the TransferText action or method, and you have the HasFieldNames property set to True.

Steps to Reproduce the Behavior
  Create the following table in any database, and name it Table1 :   Table: Table1 Field Name: ID  Data Type: AutoNumber Indexed: Yes (No Duplicates)

Field Name: Account# Data Type: Number

Table Properties: Table1 ---  PrimaryKey: ID                     Close the table.</li> In the Database window, click the Table1 table, and then click Export on the File menu.</li> In the Export Table dialog box, click Microsoft Excel 97-2000 in the Save as type box.</li> Note the location where the file will be saved, and then click Save.</li> Open Microsoft Excel.</li> Open Table1.xls.</li> Note that the number sign in the Account# field has been changed to a period.</li></ol>

Additional query words: prb field name, pound converted to period numbersign number sign hash mark

Keywords: kbprb KB308686

-

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

© Microsoft Corporation. All rights reserved.