Excel Import Multiple Text CSV XML Files

Updated 6 November 2026 · Standard formula, shown below

Excel can import multiple text, CSV, or XML files at once, but the method depends on your Excel version and whether you need the data combined or kept separate. This article explains the built-in tools, the Power Query workflow, and the limitations you will hit, so you can pick the right approach for your files.

Why Importing Multiple Files Is Different in Excel

Opening a single CSV or XML file in Excel is straightforward: use File > Open or drag the file onto the Excel window. Multiple files are a different problem because Excel’s basic Open command only loads one file at a time. If you select several files in the Open dialog, Excel still opens only the first one. To bring in many files at once, you need a feature that can loop through a folder, read each file, and either combine the data or load each file into its own sheet.

Power Query (called “Get & Transform� in modern Excel) is the built-in tool designed for exactly this job. It can read all files of a chosen type from a folder, preview the data, and combine them into a single table. For XML files, Power Query can also parse nested elements, which the plain text import wizard cannot do. If you are on Excel 2016 or later, Power Query is included; in Excel 2010 and 2013 it was a free add-in from Microsoft.

How to Import Multiple CSV or Text Files with Power Query

The folder-based import is the most reliable way to combine many CSV or text files. Here are the steps for Excel 2016 and later (including Microsoft 365):

Put all your CSV or text files in one folder. Make sure they have the same structure (same columns in the same order) if you want to combine them cleanly.
In Excel, go to the Data tab and click Get Data > From File > From Folder.
Browse to your folder and click OK. Power Query shows a list of files in that folder.
Click Combine > Combine and Load. If your files have different structures, choose Combine and Transform to edit the query before loading.
In the Combine Files dialog, pick the sample file (the first one that Power Query uses to guess the structure) and confirm the delimiter (comma, tab, semicolon, etc.).
Click OK. Power Query loads a new worksheet with all rows from all files, plus a column that shows the source file name.

If your files have a header row in each file, Power Query will automatically use the first file’s headers and skip the headers in subsequent files. If your files do not have headers, you can set “First row as headers� to off in the Combine Files dialog.

Importing Multiple XML Files: The Power Query Approach

XML files are hierarchical, so they cannot be combined like flat CSV rows. Power Query can still import them, but you need to understand the structure. When you use From Folder and choose XML as the file type, Power Query will try to parse each XML file into a table. If the XML has nested elements, you may need to expand columns by clicking the expand icon (a double arrow) in the column header.

For example, an XML file like <order><item>...</item><item>...</item></order> will produce a column called “item� that contains a table. Click the expand button to bring the item fields into separate columns. If all your XML files have the same schema, the combined table will line up correctly.

If you only need to open a single XML file, you can also use File > Open and choose XML, but Excel will show a dialog asking whether to open it as an XML table, a read-only workbook, or use the XML Source task pane. For multiple files, the folder method is the only practical built-in option.

What About the Legacy Text Import Wizard?

Older Excel versions (and some users who prefer the classic dialog) use the Text Import Wizard, which is available through Data > From Text/CSV. This wizard lets you specify the delimiter, text qualifier, and column data types, but it only works on one file at a time. You cannot select multiple files in that dialog.

If you are on Excel 2016 or later, the legacy wizard is hidden by default. You can re-enable it via File > Options > Data > “Show legacy data import wizards,� but even then it will not import multiple files at once. For multiple files, you would need to run the wizard repeatedly and then copy-paste the results, which is error-prone and time-consuming.

How to Choose Between Combine and Load vs. Load to Separate Sheets

Power Query’s Combine action always merges all files into a single table. If you want each file to appear in its own worksheet, you have a few options:

Use Power Query to create separate queries: In the From Folder preview, you can filter the file list, then load each file individually by right-clicking the file name and choosing “Load to.� This is tedious for many files.
Use a VBA macro: A simple macro can loop through files in a folder, open each one, and copy the data to a new sheet. This is the most direct way to get separate sheets, but it requires enabling macros and writing or pasting code.
Use the “Combine and Transform� option and then split the table: Load all data into one table, then use a PivotTable or Power Query’s “Group By� to view each source file separately. This does not create separate sheets, but it lets you filter by the source file name column.

For most users who want a single combined dataset, the default Combine and Load is the best choice. If you need separate sheets, a VBA macro is the most flexible, but it is not a beginner-friendly option.

Common Problems and How to Fix Them

Importing multiple files rarely goes perfectly on the first try. Here are the most frequent issues and their solutions:

Files have different column orders or names: Power Query will still combine them, but columns that do not match will appear as separate columns, and rows will have blanks. Fix the source files to have identical headers, or use the “Combine and Transform� option to manually align columns.
CSV files use semicolons instead of commas: This is common in locales where the decimal separator is a comma. In the Combine Files dialog, change the delimiter to “Semicolon.� If the files have inconsistent delimiters, you may need to normalize them first.
Number columns come in as text: Power Query often guesses data types from the first file. If the first file has numbers stored as text, subsequent files may also be treated as text. In the Combine and Transform editor, select the column and change the data type to “Whole Number� or “Decimal Number.�
XML files with different schemas: If your XML files have different element names, the combined table will have many columns with nulls. You may need to use the “Transform� step to rename columns or filter out irrelevant elements.
Too many files cause slow loading: Power Query loads all files into memory. If you have thousands of large files, the import can be slow. Consider filtering the file list in the From Folder preview to only the files you need, or use a sample of files for testing.

Alternatives: VBA Macros and Third-Party Tools

If Power Query does not meet your needs, a VBA macro is the most common alternative. A basic macro that loops through a folder and imports each CSV file into a new worksheet looks like this (in the Visual Basic Editor):

Sub ImportAllCSV()
    Dim folder As String, file As String
    Dim ws As Worksheet
    folder = "C:\YourFolder\"
    file = Dir(folder & "*.csv")
    Do While file <> ""
        Set ws = ThisWorkbook.Sheets.Add
        With ws.QueryTables.Add(Connection:="TEXT;" & folder & file, Destination:=ws.Range("A1"))
            .TextFileParseType = xlDelimited
            .TextFileCommaDelimiter = True
            .Refresh
        End With
        file = Dir
    Loop
End Sub

This macro creates a new sheet for each CSV file and imports the data. You can adapt it for text files by changing the file pattern to *.txt and setting the delimiter accordingly. For XML files, VBA is more complex because you would need to parse the XML using the MSXML2.DOMDocument object, which is not trivial for beginners.

Third-party tools like Power BI (which uses the same Power Query engine) can also import multiple files and are free for desktop use. If you are already using Power BI, you can import the folder there and then export the combined data to Excel. However, for a one-off Excel task, Power Query is usually simpler.

Frequently Asked Questions

Can I import multiple CSV files without Power Query?

Yes, but only with a VBA macro or by manually opening each file and copying data. Excel’s built-in Open and Text Import Wizard only handle one file at a time. If you do not want to use Power Query or macros, you can use a free tool like Power BI Desktop to combine the files and then export to Excel.

Why does my combined CSV import show an extra column with the file name?

Power Query adds a column called “Source.Name� (or similar) when you combine files from a folder. This is useful for tracking which row came from which file. If you do not want it, you can remove the column in the Power Query editor by right-clicking the column header and choosing “Remove.�

How do I import multiple XML files into separate sheets?

Power Query does not directly load each file into its own sheet. You can write a VBA macro that uses the XML import method for each file, or you can load all XML files into one table with a source column and then use a PivotTable to view each file separately. For separate sheets, the macro is the most straightforward.

What is the maximum number of files I can import at once?

There is no hard limit in Power Query, but Excel has a row limit of 1,048,576 rows per worksheet. If your combined data exceeds that, you will get an error. You can either split the import into multiple queries or load the data to the Excel Data Model (Power Pivot), which can handle millions of rows.

Can I import files from subfolders?

Yes. When you use the From Folder option, Power Query shows a column called “Folder Path.� You can filter to include only files from certain subfolders, or you can use the “Transform� step to add a custom column that extracts the subfolder name. By default, Power Query includes files from all subfolders.

Why does my CSV file with dates import as text?

Power Query guesses data types based on the first 200 rows of the first file. If the dates are in a non-standard format (like DD/MM/YYYY vs. MM/DD/YYYY), it may treat them as text. In the Combine and Transform editor, select the date column and change the data type to “Date� or “Date/Time,� then choose the correct format from the locale settings.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.