Excel Macro to Import CSV Files Automatically

VBA & macros · Updated November 19, 2026

To import a CSV file with an Excel macro, use VBA to ask you to select a file and load its contents into a new worksheet. In Microsoft Excel for Microsoft 365 on Windows, add the code below to a standard VBA module, run it, and choose a CSV; the macro leaves the original file unchanged.

What the macro does

The example uses Excel’s QueryTable feature to read a text file and put the parsed values in cells. It creates a fresh worksheet for each import, starts the data in A1, and treats commas as field separators. Quoted values are handled as CSV text qualifiers, so a comma inside a quoted field is not mistakenly treated as the start of another column. The imported range remains associated with the source file as a query table, which can be useful if you later want to refresh the data.

This is a practical pattern for recurring files with a consistent comma-separated layout. It is not a universal CSV-cleaning tool: Excel may interpret values as dates or numbers, and regional settings or unusual file encodings can affect how some files appear. Review important data after importing, especially identifiers, dates, and amounts.

VBA macro to select and import a CSV

Paste this code into a standard module in the workbook that should receive the imported data. It prompts for one CSV file, creates a new worksheet, and imports the data into that sheet.

Option Explicit

Sub ImportCSVToNewSheet()
    Dim filePath As Variant
    Dim ws As Worksheet
    Dim qt As QueryTable

    ' Ask the user to choose a CSV file.
    filePath = Application.GetOpenFilename( _
        FileFilter:="CSV files (*.csv),*.csv", _
        Title:="Select a CSV file")

    ' GetOpenFilename returns False when the user cancels.
    If VarType(filePath) = vbBoolean Then Exit Sub

    On Error GoTo ImportError
    Application.ScreenUpdating = False

    ' Keep the source data separate from existing worksheets.
    Set ws = ThisWorkbook.Worksheets.Add( _
        After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))

    Set qt = ws.QueryTables.Add( _
        Connection:="TEXT;" & CStr(filePath), _
        Destination:=ws.Range("A1"))

    With qt
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .AdjustColumnWidth = True
        .Refresh BackgroundQuery:=False
    End With

    Application.ScreenUpdating = True
    MsgBox "CSV imported to " & ws.Name & ".", vbInformation
    Exit Sub

ImportError:
    Application.ScreenUpdating = True
    MsgBox "The CSV could not be imported: " & Err.Description, vbExclamation
End Sub

The code uses declared variables and includes a basic error message. If an import fails after Excel has added the worksheet, an empty or partly populated tab may remain; check the workbook and remove that tab if it is not needed. The macro does not delete, edit, or move the selected CSV.

Add the macro to a workbook

  1. Open the workbook that will contain the macro. Save a copy before running it: VBA actions cannot be undone with Ctrl+Z.
  2. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm). A regular .xlsx file cannot store VBA code.
  3. In Windows, press Alt+F11 to open the Visual Basic Editor. On some keyboards, you may need Fn+Alt+F11. In the editor, choose Insert > Module, then paste the code into the module window.
  4. Return to Excel and run the macro from Developer > Macros: select ImportCSVToNewSheet and choose Run. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.
  5. Select a CSV in the file picker. When the import completes, look for the newly created worksheet at the end of the workbook’s sheet tabs.

On a Mac, Excel also supports VBA macros in desktop workbooks, but ribbon and keyboard details differ. Option+F11, sometimes with the Fn key, opens the Visual Basic Editor on many Mac keyboards; you can also use the Mac Excel menu’s Visual Basic Editor command. The Developer tab can be enabled in Excel’s ribbon settings. Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros; use the desktop app to execute this code.

How the import works

GetOpenFilename displays a file-selection dialog and returns either the chosen path or a Boolean False if the user cancels. The macro exits on cancellation, so it does not create a blank worksheet just because the dialog was closed. After a path is selected, QueryTables.Add connects the new query table to the text file and sets its destination to cell A1 on the new sheet.

The delimiter settings tell Excel to split fields at commas, not tabs, semicolons, or spaces. The double-quote text qualifier tells Excel that a field enclosed in quotes may contain a comma as ordinary text. For example, a row such as 1042,"North, West",Open should be divided into three fields, with “North, West” kept together in one cell. The synchronous refresh setting makes Excel finish loading the data before the macro displays its completion message.

The connection also means the imported table is not simply a static paste. Depending on how it is retained and configured in the workbook, its query table can be refreshed from the file path. If the source file is moved or renamed, a refresh may fail because the saved connection still points to its original location.

Adjust the delimiter and destination

CSV technically refers to comma-separated values, but files exported by some systems use a semicolon or tab between fields. If the file opens as one long column, confirm its delimiter in a plain-text editor or ask the system that produced it. For a semicolon-separated file, change the delimiter settings in the code so that semicolon is True and comma is False. For a tab-delimited text file, enable the tab delimiter instead. Do not enable several delimiters at once unless the file genuinely uses all of them.

To import into a particular existing worksheet, change the destination setup rather than adding a worksheet. For example, set the destination to a named sheet’s cell A1 and decide what should happen to its old contents before running the import. The sample deliberately creates a new sheet because it avoids overwriting data a user may already have. If you run it repeatedly, it creates another worksheet each time; add a deliberate naming, clearing, or replacement rule if repeat runs should update one designated sheet.

Prevent common data conversion problems

Excel may interpret text that looks numeric as a number, or a string resembling a date as a date. That can be helpful for ordinary quantities but harmful for product codes, postal codes, account identifiers, or values with leading zeros. If a source field contains 00127, a numeric conversion can display it as 127. The original characters may no longer be represented as intended in the worksheet.

For identifiers or other fields where every character matters, use Excel’s text-import controls or Power Query to set those columns to Text before loading. The macro above does not assign a text data type to every column, because doing so indiscriminately would also turn useful numbers and dates into text. For critical files, test a copy and compare row counts, column counts, and representative values against the source. A visible leading zero format is not the same thing as preserving the original text value.

Encoding is another possible source of surprises. A CSV containing accented letters or non-Latin characters may display incorrectly if Excel reads it using an incompatible character encoding. If that happens, import the file through Excel’s text or Power Query import interface and select the appropriate file origin or encoding when offered, then confirm the result before relying on the macro for repeated imports. A plain-text editor that lets you inspect encoding can help identify what the exporting system produced.

Fix frequent import errors

  • Everything appears in column A: The file may use semicolons or tabs rather than commas. Set the matching delimiter property and turn off comma splitting if it is not used.
  • Columns split at commas inside a name: Check that the CSV uses standard double quotes around fields containing commas and that the macro’s text qualifier is set to double quotes. Malformed quoting in the source can still cause incorrect parsing.
  • Leading zeros disappear or dates change: Excel has inferred a data type. Import the affected column as text using an import workflow that lets you assign column types, then verify the values.
  • Characters are garbled: The file encoding may not match Excel’s interpretation. Try an import interface that lets you select the encoding, and verify sample characters against the source.
  • The macro is blocked or missing: Confirm the workbook is saved as .xlsm and opened in desktop Excel. Review the organization’s macro policy; do not weaken security settings or enable unknown macros just to run a file.
  • The import reports an error: Confirm that the selected path points to a readable file and that the workbook is not protected in a way that prevents adding sheets. The error message describes the failure, but does not repair a partial import automatically.

Macro or Power Query?

Use VBA when the task must be launched by a button or macro, must follow a custom sequence, or is part of a larger workbook process. A macro is also useful when a user needs to choose a different file each time. Its trade-offs are that it requires a macro-enabled workbook, desktop Excel, and permission to run VBA; its behavior also depends on how the code handles changing file layouts.

Power Query is often a better fit for repeatable data preparation without writing VBA. In Microsoft 365 for Windows, use Data > Get Data > From File > From Text/CSV to preview a file, inspect the delimiter and data types, and load the result. Once set up, a query can usually be refreshed when the source is updated. The exact refresh workflow depends on whether the file stays at the same path and whether the incoming columns remain consistent. Excel for Mac has Power Query support with version-dependent capabilities; Excel for the web does not provide the same full authoring experience as desktop Excel.

For one-off imports, the built-in text or CSV import route may be simpler than creating a macro. For a folder of similarly structured files, Power Query’s folder-combine workflow can be more maintainable than repeatedly selecting files by hand. Choose VBA for workflow automation, and choose an import or query tool when previewing, transforming, and controlling column types are the main needs.

Safe use and repeatable imports

Only run macros from workbooks and sources you trust. VBA can make broad changes to files and workbook contents, so a macro-enabled extension does not prove that a file is safe. Keep macro security at the level required by your organization, and do not choose “enable all macros” as a general fix for a blocked file. If the workbook will be shared, explain what the macro does and where imported data will appear.

Before making a recurring process, define how the source file is selected, whether a prior import is replaced or retained, and what happens if the column order changes. Decide whether formulas, formatting, or other workbook content must be preserved. Keep an untouched copy of important source data, and test unusual cases such as blank fields, quoted commas, large files, and identifiers with zeros at the start. A small test file is safer than discovering a layout mismatch after a large import.

Frequently Asked Questions

Can a macro import a CSV without opening it first?
Yes. The sample connects a query table directly to the selected text file and loads the parsed values into a worksheet. It does not open the CSV as a separate workbook window.

Can I make the macro import to the same worksheet every time?
Yes, but change the code to target a specific worksheet and decide whether to clear or replace its existing data first. The sample creates a new worksheet on every run to avoid accidental overwrites.

Why does Excel put my CSV data into one column?
The file may use a delimiter other than a comma, or its format may not match the macro’s settings. Check the source and configure the macro for the actual separator, such as a semicolon or tab.

Will the macro preserve leading zeros?
Not reliably in every case. Excel can infer that a field is numeric and remove leading zeros. Import sensitive columns as Text and check the resulting values rather than relying on cell formatting alone.

Can I run this macro in Excel for the web?
No. Excel for the web can store and open macro-enabled workbooks but does not execute VBA. Run the macro in desktop Excel for Windows or Mac.

Can I import several CSV files at once?
This example selects one file per run. A multi-file workflow needs additional code to loop through a selected set of files and define whether their rows should be combined or placed on separate sheets. For combining a folder of similar files, Power Query may be easier to maintain.

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.