VBA Access: Import CSV Files Reliably

VBA & macros · Updated November 18, 2026

To import a CSV file into Microsoft Access with VBA, call DoCmd.TransferText using the acImportDelim transfer type, the destination table, the file path, and whether the first row contains field names. For a dependable import, create the destination table first and use a saved import specification when the file has particular delimiter, encoding, or field-format requirements.

What an Access CSV import does

A CSV file stores tabular data as text: each record is usually a line, and a delimiter separates the fields. Despite the name “comma-separated values,” files may use semicolons or tabs instead of commas, and individual values may be enclosed in quotation marks. CSV does not reliably carry database information such as field types, indexes, or relationships. Access therefore has to interpret the text as it imports it.

In Access VBA, DoCmd.TransferText is the built-in method for importing or exporting text files. The acImportDelim option tells Access to treat the file as delimited text. If you specify an existing table, imported records are added to it; an import is not automatically a replacement of the table’s existing contents. That distinction matters when a macro is run more than once.

For recurring work, a useful pattern is to import into a staging table, check the results, and then move valid records into the permanent tables. This keeps a source file’s unexpected values from immediately affecting reporting or related data.

Prepare the table and file

Before writing the macro, inspect a representative CSV and decide how each column should be stored. For example, an order number that can contain leading zeroes is often better stored as Short Text than as a number. A date should be imported as a Date/Time value only when its format is clear and consistent. If Access guesses a type from early rows, later values of another type may fail or be converted in an unintended way.

  • Create the destination or staging table with deliberate field names and data types.
  • Check whether the CSV has a header row. Set HasFieldNames to True only when the first row contains column names.
  • Confirm the delimiter, text qualifier, and character encoding. A text qualifier is commonly a double quote.
  • Choose whether each run should append, replace, or otherwise process existing data. TransferText does not make that business decision for you.

Using a pre-created table also makes schema changes visible: if the supplier adds or renames a column, the import can be reviewed rather than silently becoming a different table design.

A basic VBA import macro

This example imports a header-row CSV into an existing table named stgOrders. Change the path and table name to match your database. The code checks that the file exists and reports an error if Access cannot complete the import.

Option Explicit

Public Sub ImportOrdersCsv()
    Const DESTINATION_TABLE As String = "stgOrders"
    Dim csvPath As String

    On Error GoTo ImportError

    csvPath = "C:\Data\orders.csv"

    If Len(Dir$(csvPath)) = 0 Then
        Err.Raise vbObjectError + 1000, "ImportOrdersCsv", _
                  "The CSV file was not found: " & csvPath
    End If

    DoCmd.TransferText TransferType:=acImportDelim, _
                       TableName:=DESTINATION_TABLE, _
                       FileName:=csvPath, _
                       HasFieldNames:=True

    MsgBox "CSV import completed.", vbInformation
    Exit Sub

ImportError:
    MsgBox "Import failed (" & Err.Number & "): " & _
           Err.Description, vbExclamation
End Sub

In the VBA editor, put this in a standard module, then run ImportOrdersCsv from the editor or call it from a form button. The example relies on Access’s default text-import interpretation because it does not name a specification. That may be adequate for a simple, consistent file; for a production feed, a saved specification gives you more control. The code imports rows but does not clear the destination table first.

Save a copy of the database before running a new or changed macro. VBA actions generally cannot be reversed with Ctrl+Z, and an append import can create duplicate records if repeated. Build in a deliberate duplicate-check or cleanup process if reruns are possible.

Use an import specification for repeatable files

An Access import specification stores settings for a text import, such as the delimiter, text qualifier, field names, and field-level import choices. To create one, use Access’s text import wizard on a representative CSV, set the options, and save the import steps when the wizard offers that choice. The saved specification name must match the name used in VBA.

To use a saved specification, add its name to the call. For example, with an existing table called stgOrders and a saved specification named Orders CSV, the call can be written as:

DoCmd.TransferText TransferType:=acImportDelim, _
                   SpecificationName:="Orders CSV", _
                   TableName:="stgOrders", _
                   FileName:=csvPath, _
                   HasFieldNames:=True

Use the saved name exactly as it appears in Access. A specification is particularly helpful when the file uses a semicolon delimiter, has a known text qualifier, or needs consistent field handling. It does not make a changed source layout safe by itself: if columns move or headers change, verify that the imported fields still land in the intended destinations.

Headers, delimiters, dates, and encoding

Setting HasFieldNames correctly is essential. With True, Access treats the first row as column names rather than data. With False, it treats that row as a record. A file can have a header but still use names that do not match the destination table; check the specification and resulting field mapping rather than assuming matching order is sufficient.

Delimiter and quoting rules matter when values contain punctuation. A properly quoted value such as "Seattle, WA" should be read as one field, even though it contains a comma. Files with inconsistent quoting or a different delimiter can shift values into the wrong columns. Test with rows containing commas, quotes, and blank values—not only with the simplest row.

Dates and numbers can also be interpreted differently depending on their text representation and regional settings. A date such as 03/04/2026 is ambiguous without knowing whether the source means March 4 or April 3. If the source can provide an unambiguous format, such as year-month-day, use it and verify the imported result. Do not assume that a CSV’s displayed format guarantees the data type Access will assign.

Encoding affects accented letters and other non-ASCII characters. If names appear as garbled characters, check how the file was encoded and whether the import settings match it. A saved specification can help standardize import choices, but confirm the result with the actual file source and Access version in use.

Make the import safe to repeat

A reliable import needs more than a successful method call. Decide what should happen when the macro runs twice. If the destination is a staging table, one approach is to clear that staging table before importing, then validate the new batch. Do not clear a permanent production table casually: a failed import after deletion could leave it empty. A transaction, backup, or separate staging workflow can reduce that risk, but the right approach depends on the database design.

  • Keep a source filename, import timestamp, or batch identifier if users need to trace records to a file.
  • Check the number of imported rows against an expected range or a count from the source process.
  • Review required fields, duplicate keys, and values that do not fit the destination field types.
  • Only append validated data to permanent tables, and make the append step repeat-safe where possible.

For example, an order feed might first load into stgOrders. A saved query can then identify missing order IDs or invalid dates before a separate append query adds acceptable rows to the main Orders table. This separates file parsing from business rules and makes it easier to diagnose a bad batch.

Common errors and how to diagnose them

If Access reports that it cannot find the file, check the full path, filename extension, permissions, and whether a network location is available to the account running Access. A mapped drive may not be available when the database runs under a different Windows login or scheduled context; a verified UNC path may be more suitable in that situation.

If the import says a table or field is unavailable, verify the destination table name, field names, and saved specification name. If rows are rejected or values are missing, inspect the source for inconsistent data types, unexpected delimiters, invalid dates, or values longer than the destination field permits. Access may create an import-errors table for rejected or problematic rows; inspect it when present rather than treating a completion message as proof that every value was accepted.

If columns appear shifted, first check the delimiter and quoting in the source file. If the data imports but characters are corrupted, investigate encoding. If the macro reports that the database is read-only or the file is in use, check file permissions and whether another process has opened or locked the database or CSV. A useful diagnostic routine records the source path, time, and error details so a failed scheduled import can be investigated later.

When to use another import method

DoCmd.TransferText is a good fit for importing a conventional delimited text file into an Access table. If the source is an Excel workbook, use an Excel-oriented import method instead; a workbook is not simply a CSV with a different extension. If a supplier provides a database connection or an API, a linked table or a purpose-built data integration process may be more suitable than repeatedly downloading files.

For complicated transformations, Access queries can process data after it lands in a staging table. VBA can coordinate the steps, while saved queries express joins, validation, and append logic. This division is often easier to maintain than placing all data-cleaning rules inside a long procedure. Choose the simplest method that preserves the source data and makes failures visible.

Frequently Asked Questions

Can VBA create a new Access table from a CSV?
Access can create a table during a text import in some workflows, but a pre-created table is generally more predictable for a repeatable VBA process. Define field types and constraints yourself when those details matter.

Does importing a CSV overwrite the Access table?
No. Importing into an existing table normally adds records. If you intend to replace data, explicitly design and verify a safe clear-and-reload process rather than assuming the import will overwrite it.

How do I import a CSV with a semicolon delimiter?
Set the delimiter in a saved text import specification, then pass that specification’s exact name to DoCmd.TransferText. Confirm the result with sample rows that include quoted text and semicolons inside values.

Why are my CSV dates or leading zeroes wrong?
Access may interpret values according to the destination field type and import settings. Store identifier codes as text when leading zeroes are significant, and use a consistent, unambiguous date format with a suitable field definition.

Can I run the import from a form button?
Yes. Put the import procedure in a standard module and call it from the button’s On Click event, for example with ImportOrdersCsv. Handle errors in the procedure and consider showing a clear completion or failure message to the user.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.