ACC2000: How to Export Right- or Left-Aligned Fields to a Text File (210052)

MORE INFORMATION

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

To create a fixed-width text file, use one of the following methods.

Method 1: Use a Query

The following example demonstrates a query that creates a fixed-width text file. This example includes the Orders table in the sample database Northwind.mdb. As a result of this sample query, the CustomerID field is left-aligned, and the OrderDate and Freight fields are right-aligned.

1. Open the sample database Northwind.mdb, and then create a new query based on the Orders table.
2. Create the following three fields in the query:

   Field: CustomerID
   
   Field: Expr1: Space(12-Len(Format([OrderDate],"Short Date"))) &
                                    Format([OrderDate],"Short Date")
   
   Field: Expr2: Space(15-Len(Format([Freight],"Currency"))) &
                                     Format([Freight],"Currency")
   						

   NOTE: The Space() function pads spaces at the beginning of the field. The numbers 12 and 15 at the start of the Space() function indicate the intended total width of that field in the text file. These numbers will be used again in step 7.
3. Run the query. Note that the fields may not appear right-aligned unless you are using a fixed-width font such as Courier. You can ignore this appearance because it has no effect on the text file. Save the query as Right Justify Orders.
4. On the File menu, click Export.
5. In the Save as type box, click Text Files (*txt;*.csv;*.tab;*.asc). Type Orders.txt in the File name box, and then click Save. This starts the Text Export Wizard.
6. Click the Fixed Width - Fields....each field option, and then click Advanced. This displays the Export Specification dialog box.
7. In the Field Information table, type the following Start and Width data:

      Field Name   Start   Width
      ----------   -----   -----
      CustomerID     1       10
      Expr1         11       12
      Expr2         23       15
   					

8. Click Save As. In the Specification Name box, type Right Justify Orders, and then click OK.
9. Click OK to return to the Text Export Wizard, and then click Finish to create the text file. When the message appears, click OK.
10. Open the Right Justify Orders.txt file in Microsoft NotePad. Note that the CustomerID field is left-aligned, and the OrderDate and Freight fields are right-aligned.

Method 2: Use a VBA Function

The following sample function, CreateTextFile(), creates a fixed-width text file from the Orders table in Northwind.mdb. The CustomerID field is left-aligned, and the OrderDate and Freight fields are right-aligned.

1. Start Microsoft Access, and then open the sample database Northwind.mdb.
2. In the Database dialog box under Objects, click Modules, and then click New.
3. Type the following function in the module sheet:

   Public Function CreateTextFile()
   
   'This function creates a fixed-width text file using the Orders table
   'in Northwind.mdb. The CustomerID field will be left-aligned
   'while the OrderDate and Freight fields will be right-aligned.
   
    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
    Dim mydb As DAO.Database, myset As DAO.Recordset
    Dim intFile As Integer
   
    Set mydb = CurrentDb()
    Set myset = mydb.OpenRecordset("Orders", dbOpenTable)
   
    myset.Index = "PrimaryKey" 'Orders table must have primary key.
    intFile = FreeFile
   
    Open "C:\My Documents\Orders.txt" For Output As intFile
   
    'The following section is optional. Remove the comment (') 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.
     myset.MoveFirst
     Do Until myset.EOF
         LSet strCustomerId = myset![CustomerID] 'Field name in brackets
         RSet strOrderDate = Format(myset![OrderDate], "Short Date")
         RSet strFreight = Format(myset![Freight], "Currency")
         'Concatenate all of the variables together as in the following:
         Print #intFile, strCustomerId & strOrderDate & strFreight
         myset.MoveNext
     Loop
   
     Close intFile
     myset.Close
     mydb.Close
   
     MsgBox "Text file has been created!"
   
   End Function
   
   					

4. Press CTRL+G to open the Immediate window, type the following line, and then press ENTER to run the function:

   ?CreateTextFile()
   					

5. Open the Orders.txt file in NotePad. Note that the CustomerID field is left-aligned, and the OrderDate and Freight fields are right-aligned.