Forms & Reports:
Import Text File

 Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

Working with text database files directly is a pain, but they are very useful for transferring data between programs, such as when Access cannot read the original file's format.

Diagram: MS Works data must be exported to a text file and then imported into MS Access

Problem: MS Access cannot import a database from MS Works.
Solution: MS Works saves the database as Text
MS Access imports the text file.

A text database uses a TAB or comma or other special character as the delimiter which separates the fields and usually uses a line break to separate records. The Import Text Wizard can usually guess what the delimiter is. If it is wrong, you can pick the correct character yourself.


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries

Project 4: Forms & Reports Arrow: subtopic open
    Import/Export/Link Arrow: subtopic open
    Icon: StepImport Spreadsheet
    Icon: StepImport Text File
    Icon: StepImport Access Objects
    Icon: StepLink
    Icon: StepExport from Access
    Designing FormsTo subtopics
    Designing ReportsTo subtopics
    Special Forms & ReportsTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics

Search  
Glossary  
Appendix

Methods of Importing Text Database File

A text database is a single table. You can import such data once you know how the fields and records are delimited (that is, separated).

You import from inside the database where you want the new data.  You can append text data to an existing table.

  • Copy and Paste:
    Copy cells from a table or rows from a text file and paste into a Access datasheet. If you are appending (adding new records) to an existing table, the data must match the existing field order and the data types. If you are creating a new table, you can name and configure the fields after pasting.
     
  • File types for importing text fileMenu:
      File | Get External Data | Import  - The Import dialog appears, which looks like an Open dialog.
    Choose a text data type - Text Files (*.txt, *.csv, *.tab, *.asc)
     
    Select a file.
     

The problems are the same as with spreadsheet data:

Import Errors: If some or all of the records fail to import properly, they will be saved in a new table called Paste Errors. You should look at the records to see which ones caused a problem. You may be able to tell what the problem was just by inspecting the records.

Common Causes of Paste Errors:

  • Different table structure: Missing or extra fields
    Example: Source might have a person's name in 1 field while the destination table use 3 fields
  • Mis-matched data types:
    Example: Source may have zip codes in a Number field while the destination table uses a text field.
  • Different field names:
    Example: Source may use FirstName, without a space, while destination uses First Name, with a space.
  • Field order: If the first row of imported data does not contain the field names, then the fields must be in the same order as in the Access table.
  • Duplicates in primary key: Imported data may have duplicate values in the field that is the destination table's primary key.
Icon: Step-by-Step 

Step-by-Step: Import Text File

  Icon: Step-by-Step

What you will learn:

 to import data from text file to a new table
to append data from a text file to an existing table

Start with:  Class disk, resource files, worldtravel.mdb

You will add a table to the database worldtravel.mdb that you created in the previous lesson.

Import Text Data: New Table

Access has a wizard to help you import text data. A plain text database file must be constructed in a way that makes it easy to tell where one field stops and another begins and where a record stops. Tabs and commas are most often used to create the separations between fields. Line breaks most often indicate the end of a record.

The table that you will import was constructed from a template that included some columns that you do not want. The fields are similar to those in the Staff table, but are not quite the same. The Import Text Wizard lets you omit those. So helpful!

  1. From the menu select  File | Get External Data | Import 
    The Import dialog appears.
     
  2. Dialog: Import - text databaseChange the Files of type box to Text Files (*.txt;*.csv;*.tab;*.asc).
     
  3. Navigate, if necessary, to where you stored the resource files and select the file Clients.txt file.
     
    Icon: TroubleProblem: You do not see the file Clients.txt
             Either you did not change the file type to Text Files or you are not looking in the folder where you put the resource files. If necessary, download the resource files again.
     
    TipImporting Online File: You can import directly from an online ONLY IF you have the correct username and password for the web site. (You do not have that for this file!)
     
  4. Click on the Import button.
    The Import Text Wizard opens. This wizard is similar to the one for spreadsheets.
     
  5. Dialog: Import Text Wizard - step 1: delimited or fixed widthIn step 1, choose Delimited. In this text file the fields are separated by a comma.
     
  6. Click on the Next button.
     
  7. Dialog: Import Text Wizard - step 2: delimiter, first rowIn step 2 of the wizard, check the box for First Row Contains Field Names. Leave the delimiter as comma and the Text Qualifier as the double quote (").
     TipAt this point you should scroll through the fields horizontally to make sure that all of the data falls into columns correctly.
    If it does not, change the delimiter or back up and try "Fixed Width" instead of "Delimited".
     
  8. Click on the Next button.
     
  9. Dialog: Import Text Wizard - step 3: In a New TableIn step 3 of the wizard, choose to store the data "In a New Table".
     
  10. Click on the Next button.
     
  11. Dialog: Import Text Wizard - step 4: fields to importIn step 4 of the wizard, there are several fields that you do not want to import.
    Select     each of the following columns and check the box "Do not import field (Skip)"
     
    ClientID
    EmrgcyContact
    EmrgcyContactPhone
    MembershipStatus
    DateJoined
     
  12. Click on the Next button.
     
  13. Dialog: Import Text Wizard - step 5: primary keyIn step 5 select "Let Access add primary key".
     
    Just as in the case with a spreadsheet, to get an AutoNumber field we must let Access handle this transaction.
     
  14. Click on the Next button.
     
  15. Dialog: Import Text Wizard - step 6: table nameIn step 6 accept the default table name "Clients".
     
  16. Click on the Finish button.
    Icon: Trouble Error creating primary key: .
    Message: Error creating primary key on field 'Prefix' (Index or primary key cannot contain a Null value.).
    You did not let Access create the primary key but selected "Choose my own primary key" by mistake. The default field is the first one that was imported, Prefix. Since several records do not have a value in this field and other records have duplicate values, this field will not work as a primary key.
    Solution: Create a new field in the Clients table, ClientID and make it an AutoNumber field and the primary key.
     
  17. Click on OK and let the import proceed.

Modify Table Design

  1. Table Design View: Clients - ClientID as primary key, AutoNumberRename the field ID as ClientID. It is an AutoNumber field and the primary key.
     
  2. Inspect the properties of the other fields. Eek! Text fields with field size of 255 again!
    You have another set of changes to make. So many fields!
     
  3. Change the following field sizes and/or data types:
    Prefix, Suffix:  5
    FirstName, MiddleName, Nickname:  20
    LastName: 30
    Title: 20
    OrganizationName: 60
    Address:  50
    City: 30
    State: 20
    Region: 20
    PostalCode: 15
    Country: 30
    HomePhone, WorkPhone, MobilePhone, FaxNumber, AlternativePhone: 20
    EmailAddress: 75
    Birthdate- Date/Time     data type
    Nationality: 50
    DateUpdated - Date/Time     data type
    Hobbies - no change
    HealthIssues: Memo     data type
    Notes: Memo data type
     
  4. Icon: Save Save the table. You will see a warning about data and smaller field size. There should not be a problem if you entered the values above correctly.

Import Text Data: New Records to Append

It is important that the data you are importing matches the fields and field properties of the table to which you want to append it. There are many opportunities for errors here!

When you want to append just the new records, you need a file that contains just those records. You can import the whole file and then copy and paste what you want, or you can create a file that contains only the records that you want. You can see below that the file is not easy to read! Perhaps it will be easier to import the whole thing and then copy/paste.

Text file including new records

  1. Dialog: Import - Clients-updated.txtFrom the menu select  File | Get External Data | Import 
    The Import dialog appears.
     
  2. Select the file Clients-updated.txt.
     
  3. Click on Import.
    The Import Text Wizard starts.
     
  4. In step 1 choose "Delimited".
     
  5. In step 2 check the box for "First Row Contains Field Names".
     
  6. In step 3 choose "In New Table".
     
  7. In step 4 make no changes.
     
  8. In step 5 pick your own primary key, ClientID.
     
  9. In step 6 use the default table name, Clients-updated.
     
  10. Open the new table in Datasheet View and select the last 4 records, which are the new ones.
    Table Datasheet View: Clients-updated
  11. Copy.
     
  12. Open the table Clients.
     
  13. Select the last row and Paste.
    A message appears saying the value is not valid for the field, but it does not tell us which field! <grrrr!>
    Message: The value you entered isn't valid for this field.
     
  14. Click on OK.
    Another message asks if you want to suppress further error messages.
    Message: Do you want to suppress further error messages telling you why records can't be pasted?
     
  15. Click on No. This time lets see how many problems there are.
    Another message like the first one appears.
     
  16. Keep clicking on OK in the error messages.
    Finally a message says the you are about to "paste 1 record(s)." Finally!
    Message: You are about to paste 1 record(s).
     
  17. Click on Yes.
    Yet another message tells you that the records that failed to paste are in the Paste Errors table.
    Message: Records that Microsoft Office Access was unable to paste have been inserted into a new table called 'Paste Errors'.
     
  18. Click on OK.
     
  19. Open both the Paste Errors and Clients tables in Design View. Compare the fields.

    Paste Errors table

    Ah ha! The  Paste Errors table has 4 more fields than the Clients table has. The paste failed when Access tried to paste data from the Text field EmrgcyContact into the Date/Time field DateUpdated in the Clients table.  Records that had something in that field were blocked from pasting. That was a good thing to happen, but it certainly is annoying.

    Warning Paste Errors: Pasted into the Wrong Field
    You can have an apparently successful paste and still have paste problems. Access will allow some pastes that you really don't want. For example, a State value (which is text) will be accepted in a Postal Code field that is a Text type field. Access cannot read the actual values. You must carefully inspect the fields before you paste!
     

  20. In the Paste Errors table, delete the fields EmrgcyContact, EmrgcyPhone, MembershipStatus, and DateJoined.
     
  21. Icon: Save Save the table and switch to Table Datasheet View.
     
  22. Select the 3 records in the table Paste Errors and Copy.
         Table Datasheet View: Paste Errors - records selected
  23. Switch to the table Clients, select the bottom row, and paste again.
    A message appears that you are about to paste 3 records. Hurrah!
    Message: You are about to paste 3 record(s).
  24. Click on Yes. All four new records are in place. Hurrah!

    Datasheet View: with 4 new records
     

  25. Close the tables.
     
  26. Delete the tables Paste Errors and Clients-updated.
    You are left with 2 tables, Clients and Staff.

Alternate Methods to Append New Records from Text File:
You may need to edit the data that you are importing first, to make sure that the fields match the fields in the existing Access table.

  • Import selected records: Outside of Access, create a text file containing just the field names and the records that you want to append. Most programs can save their files in Text format. Use the Import Text Wizard to append records to an existing table.
  • Copy/Paste selected records (Text to Excel to Access): Open the text file in Excel, using Excel's Import wizard to create the correct columns. Copy the records that you want in Excel. In Access, paste into bottom row of the datasheet view of the table to which you want to append the records.