Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 36 ยท Data Import, Export and Linking

Importing Data into Access

Bring data into Microsoft Access from Excel, CSV and text files. Learn the Import wizard, field types, saved imports, cleaning with append queries and fixing import errors.

Most Access databases start life as a spreadsheet. Importing copies the data from an outside file into an Access table; after the import the two are independent, so changes in the source no longer appear in Access (if you want a live connection, see linked tables). A careful import is not just a click-through: the data usually needs cleaning, typing and de-duplicating, and a good workflow does that in a staging table rather than in your real tables.

Where the Import wizard lives

On the ribbon choose External Data > New Data Source and pick the format:

  • Excel workbook (.xlsx, .xls): choose a worksheet or a named range.
  • Text File (.txt, .csv, .tab): delimited (comma, tab, semicolon) or fixed width.
  • XML, HTML, SharePoint list, Outlook folder, ODBC Database, and another Access database.

The wizard offers three options for the destination: Import the source data into a new table, Append a copy of the records to an existing table or Link. For a first load pick new table; for recurring loads of the same shape, append.

Walking through a CSV import

  1. External Data > New Data Source > From File > Text File, choose the file, select Import the source data into a new table.
  2. In the first step choose Delimited. Pick the delimiter (comma, tab or semicolon: in many European regions CSV files use semicolons), tick First Row Contains Field Names and set the Text Qualifier to the double quote so that values like "Smith, John" stay in one field.
  3. For each column set Field Name, Data Type, Indexed and Do not import field (Skip). This is the most important screen: Access guesses types from the first rows, so a ZIP code column that starts with numbers becomes Number and loses its leading zeros. Choose Short Text for codes, phone numbers and IDs you will never add together.
  4. Choose a primary key: let Access add an AutoNumber, pick a field or none.
  5. Name the table. Tick Save import steps to keep a reusable saved import.

Date formats are the other classic trap. A date like 03/04/2025 is March 4 in the United States and April 3 in most of the world. Use the Advanced button to set the Date Order and the date delimiter, or ask for ISO dates (2025-04-03) in the source file, which every system reads the same way.

Cleaning in a staging table

Import into a table called tmpCustomers (all fields Short Text), then move the data with a query that cleans and converts it. That way a single bad value does not make the whole import fail, and the real table only ever holds valid data.

TEXT
INSERT INTO Customers (Name, Email, Country, Joined)
SELECT Trim([Name]), LCase(Trim([Email])), UCase(Trim([Country])), CDate([Joined])
FROM tmpCustomers
WHERE [Email] Is Not Null
  AND [Email] Not In (SELECT Email FROM Customers);

The Access functions Trim, LCase, UCase, CDate and Nz do the cleaning, and the NOT IN subquery skips rows whose email already exists. Because Access SQL differs a little from other dialects, here is the same clean-and-load query using SQLite functions. It runs on this site, so you can see the staging pattern work:

SQL Stand-in: staging table, then a cleaning INSERT

Press Run (or Ctrl + Enter) to see the result here.

Notice that the staging table keeps the raw data, so you can always inspect what was rejected.

Import from Excel

The Excel import has the same wizard plus a choice of worksheet and a Show Named Ranges option. For clean results:

  • Make the sheet a flat list: one header row, one record per row, no merged cells, blank rows or subtotals.
  • Keep one data type per column; a single text value in a numeric column can turn the whole column to text.
  • Remove formulas that return errors (#N/A) before importing, as they import as errors or Nulls.

If the sheet contains several tables, import one range at a time using named ranges.

Saved imports and automation

If you tick Save import steps, the import appears under External Data > Saved Imports. Run it again with one click, or schedule it as an Outlook task. From VBA or a macro, the equivalent calls are:

VBA
DoCmd.TransferText acImportDelim, "CustomersImportSpec", "tmpCustomers", _
                   "C:\Data\customers.csv", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, _
                   "tmpSales", "C:\Data\sales.xlsx", True, "Sheet1!A1:F500"

In the wizard, Advanced > Save As... stores an import specification (delimiter, types, date order). Reference it by name so scheduled imports behave exactly like your first manual one. TransferSpreadsheet's final argument is the range, which is how you import just part of a sheet.

Import errors

When some values cannot be converted, Access creates a table named ImportErrors (or Sheet1$_ImportErrors) in the database and tells you. Typical rows:

  • Type Conversion Failure: the text could not become a number or date. Fix the data or import as text.
  • Key Violation: a duplicate in a primary key or unique index.
  • Field Truncation: the value was longer than the field size.
  • Validation Rule Failure: the table's rule rejected the row.

Open that table, find the row numbers, fix the source and re-import. Another pattern is importing the file as all text first and using queries such as SELECT * FROM tmp WHERE Not IsNumeric([Qty]) to find the culprits before the real load; the SQLite version is WHERE qty GLOB '*[^0-9.]*':

SQL Find rows that would fail a number conversion

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. After you import an Excel file into a table, what happens when the worksheet changes?

    Import copies the data once. A link keeps a live connection to the source.
  2. 2. Which data type should hold a ZIP code like 01234?

    Codes you never calculate with should be text, otherwise the leading zero is lost.
  3. 3. Why import into a staging table first?

    A staging table lets a cleaning query validate every row, so one bad value does not break the load.
  4. 4. Where do rows that failed to import end up?

    Access creates an ImportErrors table listing the field, error type and row number.

Summary

  • Import copies data once; choose new table, append, or link.
  • Set field types in the wizard (text for codes), mind delimiters and date order, and save the steps or an import specification.
  • Import into a staging table, then use cleaning append queries (Trim, LCase, CDate, Nz) to fill the real table.
  • Read ImportErrors and pre-check with queries to fix source data.

Next: Exporting data from Access