Access 2010: Fixed width text export does not respect the Start and Width settings of Export Specification


Symptoms


In Microsoft Access 2010 when you use an Export Specification in which you select Fixed Width and specify Start and Width settings to export data, the text in the exported file does not display the correct Start and Width settings.

Cause


Microsoft is aware of this problem in Microsoft Access 2010.

Resolution


As a workaround, create a query based on the table you need to export. In that query create an expression using a string function to set the width of the fields. Create a new Export Specification and export the query rather than the table as a Text file.

For instance, using the database created in the Steps to Reproduce in the More Information section below:

  1. Create a query named Query1
  2. Add tblTest to the query.
  3. In the first column in the query grid, add the following expression to display only the first character of the last name:
  4. LastName1: Left([LastName],1)
  5. Add the FirstName field to the second column in the query grid
  6. Save and close the query
  7. Create a new Export Specification:

      a) Right click on Query1, click Export and select Text File
      b) Select the destination of the text file, name the file "Test" and click OK
      c) In the Export Text Wizard, select Fixed Width
      d) Click the Advanced button
         Set the Start for LastName1 as 1 and the Width as 1
         Set the Start for FirstName as 2 and the Width as 20
      e) Click the Save As... button
      f) Enter the following as the Specification Name:

    Query1 Export Specification

      g) Click OK to each dialog
      h) Click Cancel in the Export Text Wizard dialog

  8. Update the ExportText function in Module1 to export Query1:
  9. Public Function ExportText()

    DoCmd.TransferText acExportFixed, "Query1 Export Specification", "Query1", CurrentProject.Path & "\Test.txt", 0

    End Function

More Information


The same problem does not occur in Microsoft Access 2003 or in Microsoft Access 2007.

For more information on how to use string manipulation functions see:

http://office.microsoft.com/en-us/access-help/using-string-manipulation-functions-HA001054727.aspx

Steps to reproduce:

1. Create a new blank database. Close Table1 and do not save.

2. On the Create tab, click Table Design and add the following fields:

       Field Name: LastName; Data Type: Text
       Field Name: FirstName; Data Type: Text

3. Save as tblTest. Do not create a Primary Key. 

4. Add a new record in tblTest:

       LastName: ABCDEFG        FirstName: Test

    Close tblTest.

5. Create an Export Specification:

   a) Right click on tblTest, click Export and select Text File
   b) Select the destination of the text file, name the file "Test" and click OK
   c) In the Export Text Wizard, select Fixed Width
   d) Click the Advanced button

      Set the Start for LastName as 1 and the Width as 1
      Set the Start for FirstName as 2 and the Width as 20

   e) Click the Save As... button
   f) Enter the following as the Specification Name:

        tblTest Export Specification

   g) Click OK
   h) Click Next and click Cancel
    i) Click Close

6. Create a new module, Module1, and paste the following function:

          Public Function ExportText()

                  DoCmd.TransferText acExportFixed, "tblTest Export Specification", "tblTest", CurrentProject.Path & "\Test.txt", 0

          End Function

7. Click in the function and click the Run button to execute the ExportText function, which will create the text file

8. Open the Test.txt text file in the same folder as the database file

Results: The text file displays "ABTest" instead of the expected "ATest"