Information Technology Services

• AccountID / Z-ID & Password
• Blackboard
• Copy Services
• Document Production
• E-Mail
• Forms & Rates for Services
• Helpdesk
• Information Security
• Internet Connections
• Labs / Smart Classrooms
• MVS Services
• Phones
• Software
• Available Software A-Z
• Order Software
• Learn to use software
• Downloads
• Virus Protection
• Web Services
• ITS A - Z
• Contact Us
• ITS Home

Date Last Reviewed:
03/30/2007

Tip Archive

Importing Text Files into MS Access Tables

Sometimes it is necessary to import text files into Access databases. If the text file contains consistent delimiters such as commas, tabs, spaces, or semicolons between text areas that are to become the Access fields and carriage returns to indicate an end to each record, the importation process is fairly straightforward.

1. Importing delimited text
   1. Open a Blank or Existing Access database
   2. Click on File in the dropdown menus
   3. Move the cursor over Get External Data and click on Import
   4. Select the appropriate Files of type at the bottom of the Import window (presumably Text Files with a .txt file extension)
   5. Select or enter the location and File name of the file to be imported
   6. Click OK
   7. Check the Delimited button on the first window of the Import Text Wizard
   8. Click Next
   9. Choose the delimiter that separates your fields
   10. If the First Row Contains Field Names check the box asking for that information, if not, leave it unchecked
   11. If an "or“ is used as a Text Qualifier click on it in the Text Qualifier box, otherwise leave it as {none}
   12. Click Next
   13. In the next Import Text Wizard window tell Access if you want to put the imported material In a New Table or an Existing Table (identify existing table) then click Next
   14. The next Import Text Wizard window allows you to name fields, declare field types, check for duplicates, and skip selected fields in the import process. Select any desired options, or just click on Next
   15. The next step in the Wizard allows you to set a primary key, let Access create a primary key, or import without a primary key. Select one and click Next
   16. The final window of the Wizard allows you to name the table you are importing into or accept the default name Access provides. Select one and click Finish

If the text file is not delimited, but the fields in the text strings are of a constant length or fixed width, and carriage returns indicate an end to each record, then it is still possible to import the text data into an Access table.

2. Importing undelimited text (requires fixed field lengths in the text file)
   1. Carry out steps I. A through F above
   2. Check the Fixed Width button on the first window of the Import Text Wizard
   3. Click Advanced
   4. Type in a Field Name, set Data Type, position of starting digit of the field in the text string (in Start), number of digits in the length of the field (in Width), whether or not duplicate checking is to be done, and whether or not the field is to be skipped in the import
   5. Repeat Step II.D for each Field
   6. Click OK
   7. Click Next
   8. Check the accuracy of field breaks in the next Import Text Wizard window, correct as directed in the text box then click Next
   9. Follow steps I. M through P to complete the import of the text file into Access

Tip Archive