databasedev.co.uk - database solutions and downloads for microsoft access

Importing and Linking Data from a Text File in Access 2007:

One of the most powerful features of Microsoft Access is the fact that it seamlessly integrates with a number of other file formats, and that the program allows for the easy importing and exporting of data. Among the most powerful features of Access is the ability to easily bring in text files, allowing seamless integration with everything from mainframe programs to word processing programs.

In Access 2007, users have two ways to bring data in from text files. Users who want to copy the data in the text file can edit the data within Access or import the file into a new or existing table through the use of the Import Text Wizard. In addition, it is possible to link to the text file using the Link Text Wizard, providing users with the ability to easily query and report on the data contained in that text file.

It is important for Access users to understand text files and how they work in order to make the most of these powerful programs. A text file contains unformatted characters, including letters, numbers and special characters like tabs, line feeds, carriage returns, etc. Access 2007 provides support for .txt, .csv, .asc and .tab file extensions.

When using a text file as a source file when importing or linking, the contents of the file need to be organized in such a way that the included linking and importing wizards can divide the contents of the file into a logical set of records, and each of those records into a collection of fields, or columns. Access can handle both delimited and fixed length text files.

Delimited files are records in which each record appears on a separate line, and each field within the record is separated by a specific character, known as a delimiter. This delimiter can be any character that does not appear within the field values, such as a tab, comma, semicolon, colon, space, etc. An example of a delimited record would be.

Company A,Joe Smith,President,Jim Smith,Owner
Company B,Bob Jones,CEO,James Brown,President

A fixed width file, on the other hand, is one in which the width of each field remains constant from record to record. For example, the company name may always be 15 characters long, the owner field 20 characters long, each address field 25 characters long and so on. An example of a fixed length record would be the following:

Company A Joe Smith President Jim Smith Owner
Company B Bob Jones CEO James Brown President

One of the most important decisions database designers must make is whether to import the data contained in a text file or simply link to it. If the desire is to have the data from the text file contained in the database itself, those contents should be imported. If the data should remain in the text file and be updated there, then linking is the right option.

After that decision has been made, part of the import operation will be to determine how the data is organized and whether the data is in fixed length or delimited format. It is generally easy to tell the difference between the two during the import operation, and if not opening the text file in a data editor can be helpful.

One of the most useful features of Access is the ability to create import specifications for fixed width files. These import specifications make it easy to repeatedly import text files with the same file layout, saving database administrators precious time in the process.