DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog
Tables
Queries
Forms
Reports
Modules
Miscellaneous
Subscribe
Free Samples
Videos
Services
Links
About Us

 

 

Using Text Files - Part 2

In Part 1 of this series I briefly covered the importing of text files using the built in Import facility in MS Access. This article, part 2, will do the same for the export function.

Under the File menu, there is an entry named "Export...". The extra "..." indicates that when you choose that entry, a dialogue box will appear on the screen to ask you further details.

The next step is to change the "Files of type:" entry to show "Text Files (*.txt;*.csv;*.tab;*.asc)". It is my habit to use the .txt extension for all my text files. Thus, the resulting export file will appear under that choice. Many import routines (you are creating an export file so someone else can import it, correct?) expect the txt extension.

There is also a small check box named "Save formatted". If you click that on, then when you click the Save All button the file is saved immediately without any other options. I have never used this method except to see what it did. The output will look like this:

-------------------------------------------------------------
|   lngPrimaryKey   |       Name        |       City        |
-------------------------------------------------------------
|               397 | Richard           | Hamilton          |
-------------------------------------------------------------
|               463 | Sandra            | Vancouver         |
-------------------------------------------------------------
|               583 | Roberta           | Winnipeg          |
-------------------------------------------------------------

This format is rarely useful. We normally want some form of delimited or fixed width output style. Most of my clients have opted for the comma delimited type, although I have had some requests for fixed width. Let's start with comma delimited.

(article continues after sponsor spot)



  • leave the "Save formatted" check box off
  • click the Save All button
  • on the next dialogue box note the delimiter type choice, the text qualifier choice, and the Include Field Names on First Row check box
  • experiment with all 3 choices and watch the sample change
  • I will leave mine at the defaults (comma, quotes, off)
  • click the Next button
  • indicate a file name (or stay with default)
  • click the Finish button

Use your favourite File Manager to find and open the resulting file. Mine looked like this:

397,"Richard","Hamilton"
463,"Sandra","Vancouver"
583,"Roberta","Winnipeg"

This format is probably universally recognized by programs that allow importing. Some of them may require field names, which is a simple option as mentioned above.

Here are the results of the same data in fixed format.

397        Richard   Hamilton  
463        Sandra    Vancouver 
583        Roberta   Winnipeg  

Notice that all the fields always take the same space. The spacing is determined by the registered size of the fields, not the size of the data. My 2 text fields were each defined as 10 characters maximum. Auto-number fields are long integers, which require 10 characters maximum. Access allows for size 11 for them.

Part 3 of this series will examine the use of Import/Export Specifications.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy