ACC: Exporting Right-Aligned Fields to a Text File (1.x/2.0)

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article demonstrates two methods you can use to create a fixed-width text file that enables fields to be either left- or right-aligned.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x, or the "Building Applications" manual, Chapter 3, "Introducing Access Basic" in version 2.0.

MORE INFORMATION

Method 1

1. Open the sample database NWIND.MDB, and create the following new query based on the Orders Table.
   
   NOTE: In the following sample expressions, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating these expressions.
   

   
         Query: Right Justify Orders
         ------------------------------------------------------------------
   
         Field: Customer ID
         Field: Expr1: Space(12-Len(Format([Order Date],"Short Date"))) & _
                       Format([Order Date],"Short Date")
   
         Field: Expr2: Space(15-Len(Format([Freight],"Currency"))) & _
                       Format([Freight],"Currency")
   
         NOTE: The Space() function is used to pad spaces at the beginning of
         the field. The numbers 12 and 15 at the start of the Space()
         function indicate the desired total width of that field in the text
         file. These numbers are used again in step 5 below. 




2. Save the query as Right Justify Orders, and then run the query.
   
   Note that the fields may not appear to be right-aligned unless you are using a fixed-width font such as Courier. This can be ignored because it does not affect the text file.



3. Close the query to return to the Database window.



4. On the File menu, click Imp/Exp Setup.



5. In the Field Information table of the Import/Export Specification dialog box, type the Field Name, Data Type , Start, and Width for each field as follows:
   

   
         Field Name   Data Type  Start   Width
         ----------   ---------  -----   -----
         Customer ID   Text        1       10
         Expr1         Text        11      12
         Expr2         Text        23      15 




6. Click the Save As button and save the specification as Orders Spec . Click OK to close the Import/Export Specification dialog box.



7. Create a new macro as follows:
   

   
         Macro Action
         ------------
         TransferText
   
         TransferText Actions
         --------------------
            Transfer Type: Export Fixed Width
            Specification Name: Orders Spec
            Table Name: Right Justify Orders
            File Name: C:\Orders.txt
            Has Field Names: No 

   
   NOTE: You can specify the name of a query in the Table Name argument.



8. Run the macro.



9. Open the C:\Orders.txt file using NotePad. Note that the Customer ID field is left-aligned and the Order Date and Freight fields are right-aligned. The field names will not be included in the first row of the text file. If you set the "Has Field Names" argument in the macro to Yes, the field names will be delimited instead of fixed-width. Use Method 2 if you want to include field names in the first row of a fixed-width text file.

Method 2

1. Open the sample database NWIND.MDB.



2. Create a new module.



3. In the Declarations section of the module, add the following line:
   

   
         Option Explicit 




4. Create the following function:
   

   
         Function CreateTextFile()
           'This function creates a fixed-width text file using the
           'Orders table in NWIND.MDB. The Customer ID field will
           'be left-aligned and the Order Date and Freight fields
           'will be right-aligned.
   
           'Create a Dim statement for each field to export to the text
           'file. For the data type of each field, use "String *" followed
           'by the width of the field.
           Dim strCustomerId As String * 10 'Specifies width of 10 characters.
           Dim strOrderDate As String * 12  'Specifies width of 12 characters.
           Dim strFreight As String * 15    'Specifies width of 15 characters.
   
           'Create a recordset based on the Orders table.
           Dim mydb As Database
           Dim mytable As Table
           Set mydb = CurrentDB()
           Set mytable = mydb.OpenTable("Orders")
   
           'Determine the index to sort the table by.
           mytable.Index = "PrimaryKey" 'A primary key must exist in the
                                        'Orders table.
   
           'Create the text file. Note the use of the RSet statement
           'to right-align a field. You can use the LSet statement if you
           'want to left-align a field (or simply assign the variable
           'without using RSet or LSet.)
   
           Dim intFile As Integer
           intFile = FreeFile
           Open "C:\Orders.txt" For Output As intFile
   
           'The following section is optional. It puts the field names in the
           'first row of the text file. Remove the comment mark (') from these
           'lines if you want to put field names in the first row of the text
           'file.
           'LSet strCustomerId = "CustomerID"
           'RSet strOrderDate = "OrderDate"
           'RSet strFreight = "Freight"
           'Print #intFile, strCustomerId & strOrderDate & strFreight
   
           'This section puts the records from the Orders table in the text
           'file.
           mytable.MoveFirst
           Do Until mytable.EOF
               LSet strCustomerId = mytable![Customer ID]
               RSet strOrderDate = Format(mytable![Order Date], "Short Date")
               RSet strFreight = Format(mytable![Freight], "Currency")
               'Concatenate all of the variables together as in the following:
               Print #intFile, strCustomerId & strOrderDate & strFreight
               'The following optional line can be used to create a blank row
               'after each record. Remove the comment mark (') from the
               'following line if you want to create a blank row after each
               'line.
               'Print #intFile,
               mytable.MoveNext
           Loop
   
           Close intFile
           mytable.Close
           mydb.Close
           MsgBox "Text file has been created!"
   
         End Function 




5. Click Immediate Window on the View menu, type the following line, and then press ENTER to run the function:
   
   ? CreateTextFile()



6. Open the C:\Orders.txt file using NotePad. Note that the Customer ID field is left-aligned and the Order Date and Freight fields are right-justified.

REFERENCES

For an example of this information in Microsoft Access for Windows 95 version 7.0, please see the following article in the Microsoft Knowledge Base:

Q150401 ACC95: Exporting Right-Aligned Fields to a Text File

For more information about using Access Basic to create text files, search for "Print #," and then "Print # Statement" using the Microsoft Access Help menu.

For more information about aligning fields in a fixed-width text file, please see the following article in the Microsoft Knowledge Base:

Q98663 ACC: Exporting to Fixed-Width Text File Left-Aligns Numbers

Additional query words: justify

Keywords : kbinterop kbprg IsmTxtd IsmTxtfx
Version : 1.0 1.1 2.0
Platform : WINDOWS
Issue type : kbhowto