VBA Import CSV in Excel: Methods, Code, and Fixes

VBA & macros · Updated November 18, 2026

To import a CSV file with VBA in Excel for Microsoft 365 on Windows, use Workbooks.OpenText to parse the file, then copy its values into a worksheet in your macro workbook. The example below lets you choose a file, reads it as UTF-8 with comma delimiters, and places the results on a sheet named Data.

What importing a CSV with VBA does

A CSV, or comma-separated values file, stores table-like data as plain text. Each line usually represents a row, while separators divide fields into columns. A CSV does not preserve Excel formatting, formulas, worksheets, or workbook features; it carries text that Excel interprets when opening or importing it.

VBA can automate that interpretation. Instead of manually opening a file and stepping through import options, a macro can open a known file or prompt for one, specify how the text should be split, and transfer the resulting data. This is useful for recurring reports, downloaded system exports, or combining a file with an existing workbook.

It matters that the macro controls the import rather than simply copying text into a cell. Excel may recognize dates and numbers, remove leading zeros, or interpret separators according to regional settings. Choosing import options deliberately makes the result more predictable, though it cannot resolve every ambiguity in the source data.

Choose an import method

For a straightforward CSV, Workbooks.OpenText is often a clear option. It opens the text file as a temporary workbook and lets VBA specify the delimiter and text qualifier. The macro can then copy the parsed cells to the destination workbook and close the temporary one.

  • Use OpenText when a file is being imported as a new table and you want to control separators while working with ordinary worksheet cells.
  • Use a text import query when you need a repeatable connection that can be refreshed, or when users should manage the import through Excel’s data tools.
  • Read the file as raw text only when you need custom parsing rules or must preserve every character before deciding how to interpret fields. A simple line split is not enough for every CSV.

CSV is not always literally comma-delimited. Some programs produce semicolon-delimited files, and regional settings can affect how exported numbers and separators are written. Check a sample file or its exporting system before selecting a delimiter. If you know the source will always use semicolons, set Semicolon:=True and Comma:=False in the OpenText call instead.

Prepare the workbook and macro

On Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and add the procedure below. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. On Mac, the Visual Basic Editor shortcut is commonly Option+F11, sometimes with the Fn key depending on the keyboard; you can also open it from the Tools menu. Ribbon and security settings can differ on Mac.

Create a worksheet named Data in the workbook that will contain the macro. The procedure uses ThisWorkbook, meaning the workbook holding the VBA code, as its destination. Save that file as an Excel Macro-Enabled Workbook (.xlsm) so the code is retained. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z.

Example: select and import a CSV

This macro opens a file picker, imports the selected file, and replaces the cell contents on the Data sheet with the imported values. It clears contents, not formatting, on that sheet. Rename the sheet or change the destination behavior if your workbook uses a different layout.

Sub ImportCsvToData()
    Dim csvPath As Variant
    Dim targetWs As Worksheet
    Dim sourceWb As Workbook
    Dim sourceWs As Worksheet
    Dim oldScreenUpdating As Boolean
    Dim failureText As String

    ' Keep the workbook containing this macro as the destination.
    Set targetWs = ThisWorkbook.Worksheets("Data")
    csvPath = Application.GetOpenFilename( _
        FileFilter:="CSV Files (*.csv),*.csv", _
        Title:="Select a CSV file")
    If VarType(csvPath) = vbBoolean Then Exit Sub

    oldScreenUpdating = Application.ScreenUpdating
    On Error GoTo ImportFailed
    Application.ScreenUpdating = False

    ' Open the text file as a temporary workbook, using UTF-8 and commas.
    Workbooks.OpenText _
        Filename:=CStr(csvPath), _
        Origin:=65001, _
        DataType:=xlDelimited, _
        TextQualifier:=xlTextQualifierDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=True, _
        Space:=False, _
        Other:=False, _
        Local:=True

    Set sourceWb = ActiveWorkbook
    Set sourceWs = sourceWb.Worksheets(1)

    ' Replace existing cell contents and transfer the parsed values.
    targetWs.Cells.ClearContents
    targetWs.Range("A1").Resize( _
        sourceWs.UsedRange.Rows.Count, _
        sourceWs.UsedRange.Columns.Count).Value = sourceWs.UsedRange.Value

    sourceWb.Close SaveChanges:=False
    Application.ScreenUpdating = oldScreenUpdating
    Exit Sub

ImportFailed:
    failureText = Err.Description
    On Error Resume Next
    If Not sourceWb Is Nothing Then sourceWb.Close SaveChanges:=False
    Application.ScreenUpdating = oldScreenUpdating
    On Error GoTo 0
    MsgBox "Import failed: " & failureText, vbExclamation
End Sub

To run it, return to Excel, press Alt+F8 on Windows, select ImportCsvToData, and choose Run. On Mac, open the Macro dialog from the Tools menu if the Windows shortcut is unavailable or mapped differently. If the macro is blocked, check that the file is trusted and that Excel’s macro security settings allow it; do not enable macros in files from an untrusted source.

How delimiters, encoding, and values are handled

In the example, DataType:=xlDelimited tells Excel to split the text into fields, and Comma:=True selects commas as the separator. The double-quote text qualifier allows a CSV field to contain a comma when that field is enclosed in quotes. For example, "Portland, Oregon",97201 should become two fields, not three. Do not split each line at every comma with VBA’s Split function unless you know the source never uses quoted commas; that shortcut breaks valid CSV records.

Origin:=65001 requests UTF-8, which is common for modern exports and supports a broad range of characters. If the file uses a different encoding, text such as accented letters may display incorrectly; use the code page that matches the source rather than assuming every CSV is UTF-8. The Local:=True setting asks Excel to use local settings for aspects of text conversion. Date and decimal interpretation can therefore vary by computer or regional configuration.

Opening a CSV is not the same as preserving every field exactly as text. Excel may convert values that resemble dates or numbers. A product code such as 00127 can become 127, and a long identifier may be displayed in scientific notation or lose precision if treated as a number. If identifiers, postal codes, or account codes must remain unchanged, import those columns as text through a controlled import setup. Do not convert identifiers to numbers just because they contain digits.

Common problems and practical fixes

  • All data lands in one column: the chosen separator probably does not match the file. Inspect a few lines in a plain-text editor and set the correct delimiter. A semicolon-delimited export needs a different OpenText configuration.
  • Characters look garbled: check the source encoding and set the correct Origin value. UTF-8 is common, but not universal.
  • Leading zeros disappear: Excel inferred a numeric value. Import the relevant column as text or arrange the source and import settings so Excel does not coerce that field.
  • Dates change unexpectedly: the file’s date pattern may be ambiguous, such as 03/04/2026. Confirm whether the source means March 4 or April 3 and use an import method that specifies the intended column format.
  • Old rows remain after a smaller import: this example clears all cell contents on Data before writing the new table. If you remove that line, data left below or to the right of the new table can remain and be mistaken for current records.
  • The macro reports that Data is missing: the destination worksheet name must match exactly, or the code must be changed to the actual sheet name.

The example transfers the temporary sheet’s UsedRange. Excel’s used range can include cells that were previously formatted or edited even when they look empty, so an unusually large source range may copy more cells than expected. For tightly controlled files, determine the last occupied row and column or use a defined table structure rather than assuming the used range is minimal.

Replace, append, and automate imports

Replacing the current data is appropriate when each CSV is a complete fresh report. To append records, do not clear the destination. Instead, calculate the next free row on Data and write the imported values starting there. Check whether the CSV includes a header row: if it does, append only the data rows after the header or the destination will accumulate repeated headings.

For recurring imports, a fixed file path can remove the file-picker step, but it also creates a dependency: the file must be present at that location and should have the expected structure. A scheduled or batch process should also account for missing files, duplicate imports, changed column order, and partial files that are still being written by another application. Logging the source filename and import time can make later checks easier.

Excel for the web cannot run VBA macros. The macro must be run in the desktop Excel application. Excel for Mac supports VBA, but file paths, security prompts, and some automation details can differ from Windows; avoid relying on Windows-only components such as ActiveX if a workbook must work across both platforms.

Frequently Asked Questions

Can VBA import a CSV into an existing worksheet?
Yes. Open the CSV with Workbooks.OpenText, then copy its parsed range to the target worksheet. The example sends values to Data starting at A1; change the destination range or append logic to suit the workbook.

How do I import a CSV without losing leading zeros?
Make the affected fields text during import instead of allowing Excel to infer numbers. This is especially important for identifiers, postal codes, and codes with fixed-width digits. Applying a number format after zeros have been discarded does not restore the original text.

Does this VBA work with UTF-8 CSV files?
The example requests UTF-8 with Origin:=65001, which is suitable for UTF-8 files in supported desktop Excel versions. If characters still appear incorrectly, verify the file’s actual encoding and inspect how the source application exported it.

Can I import a semicolon-delimited CSV?
Yes. In the OpenText arguments, turn Semicolon on and Comma off. Select the separator that the file actually uses; a filename ending in .csv does not guarantee that commas separate its columns.

Can I undo a VBA CSV import?
Excel’s normal Undo history does not reverse macro actions, so Ctrl+Z will not restore the previous contents. Save a backup before running the macro, especially when it clears or replaces worksheet data.

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.