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

Article translations Article translations
Article ID: 308686 - View products that this article applies to.
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).

Expand all | Collapse all

On This Page

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 FileSystemObject
    Dim ts1, ts2 As TextStream
    Dim strTxtLine, strTempFileName As String
    strTempFileName = "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.ReadLine
    strTxtLine = Replace(strTxtLine, ".", "#")
    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.
  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.

Properties

Article ID: 308686 - Last Review: October 26, 2013 - Revision: 3.0
Applies to
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbnosurvey kbarchive kbprb KB308686

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com