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

This article was previously published under Q308686
This article has been archived. It is offered "as is" and will no longer be updated.
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.
  1. Add a reference to the Microsoft Scripting Runtime library (scrrun.dll).
  2. 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 FileSystemObjectDim ts1, ts2 As TextStreamDim strTxtLine, strTempFileName As StringstrTempFileName = "c:\FixFieldNamesTemp.txt"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.ReadLinestrTxtLine = Replace(strTxtLine, ".", "#")ts2.WriteLine strTxtLine' Loop while not at the end of the file.Do While Not ts1.AtEndOfStreamstrTxtLine = ts1.ReadLinets2.WriteLine strTxtLineLoop' Close the files.ts1.Closets2.CloseDoEvents' Overwrite original file with new filefso.CopyFile strTempFileName, strFileName, Truefso.DeleteFile strTempFileNameEnd 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.
  5. To HTML by using the TransferText action or method, and you have the HasFieldNames property set to True.

Steps to Reproduce the Behavior

  1. 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   					
  2. Close the table.
  3. In the Database window, click the Table1 table, and then click Export on the File menu.
  4. In the Export Table dialog box, click Microsoft Excel 97-2000 in the Save as type box.
  5. Note the location where the file will be saved, and then click Save.
  6. Open Microsoft Excel.
  7. Open Table1.xls.
  8. Note that the number sign in the Account# field has been changed to a period.
prb field name, pound converted to period numbersign number sign hash mark
Properties

Article ID: 308686 - Last Review: 10/26/2013 08:02:00 - Revision: 3.0

  • Microsoft Access 2000 Standard Edition
  • kbnosurvey kbarchive kbprb KB308686
Feedback