How to Load Flat File Data Into Access

Laptop with database

A flat file is a file with data that contains generic formatting. It can be uploaded into various programs including databases such as Access. Access supports flat files that have the following extensions: .txt, .csv, .tab and .asc. In order for a flat file to work properly, the data to be imported must be organized so it can be easily divided during the loading (importing) process. Commas and semicolons are commonly used separators as are the "Space" bar and the "Tab" key.

Things You'll Need

• Access 2007

Instructions

1. Open the Access 2007 database that will be used for importing the flat file. Click the "External Data" tab then click the "Text File" button to open the "Get External Data-Text File" form.

2. Type in the name of the source file (the flat file) in the box labeled "File name." You'll have two options on how to save the data: 1) Import the source data into a new table in the current database; or 2) Append a copy of the records to the table. With option 1, you'll be asked to name the table. With option 2, you'll need to select a table to which the data will be added. In most cases, the source data will be used to create a new table, so select option 1 and then click "OK."

3. Choose the appropriate delimiter to identify how the data is separated, e.g., comma, semicolon, etc. If the file uses the "Tab" or "Space" to separate data, choose "Fixed Width." Click the "Next" button.

4. Review the information that is being displayed and verify the data is being separated correctly. If the data is separated incorrectly, click "Back" and choose again. Click "Next."

5. Click one of the columns on the bottom half of the import wizard page to display the column's properties. You have the option to change the name and the data type of the column at this point. If you want to index a field, click the column and place a check mark in the box labeled "Yes" next to the word "Indexed." To prevent one of the columns from being imported, click a column and place a check mark in the box next to "Do not import field (Skip)." Click "Next" when done.

6. Select an primary key if you want to. A primary key is a unique identifier for a row of data, e.g., employee ID number. A column that holds primary key data cannot have any repeating values. Click "Next."

7. Type a name for the new table to be created in the "Import to Table" box. Click "Finish" to import the data.