Batch Import CSV Files into Excel: Methods and Tips

To batch import CSV files into Excel for Microsoft 365 on Windows, put the files in one folder, then select Data > Get Data > From File > From Folder. Choose the folder, select Combine & Transform Data, check the columns in Power Query, and select Home > Close & Load. Excel combines the files into one table, which you can refresh when the folder’s contents change.

What a Batch Import Does

A batch import brings data from several comma-separated values files into Excel in one operation. The common goal is to append records: for example, combine January, February, and March sales exports into a single table with the same columns. Power Query is the built-in tool best suited to this job because it can read a folder of files, apply repeatable cleanup steps, and refresh the combined result later.

It is important to distinguish combining files from placing each file on its own worksheet. The From Folder method normally appends compatible files into one table; it does not create a separate tab for every CSV. It also does not automatically match unrelated files side by side. If one file contains customer details and another contains orders, you need to relate or merge those datasets using a shared key rather than simply append them.

CSV is a text format, not a complete Excel workbook format. It stores values as delimited text, but it does not preserve multiple worksheets, cell formatting, formulas, charts, or Excel table settings. Importing a CSV into an existing workbook gives you a useful analysis environment, but the source CSV itself remains plain text.

Why Power Query Is Usually the Best Method

Power Query records the steps used to combine and transform the data. After the initial setup, you can replace or add files in the source folder and refresh the query instead of repeating the same copy-and-paste process. This is particularly useful for recurring exports where the column layout stays consistent.

Compared with manually opening every file, a folder query reduces repetitive work and makes transformations visible. You can filter rows, rename columns, remove unwanted fields, and set data types before loading the output. It is also easier to audit than a long chain of manual edits because the applied steps appear in the Power Query Editor.

The method works best when the files share a schema: the same column names, broadly the same meaning for each column, and a consistent delimiter and encoding. Files with different layouts can still be handled, but they may need separate queries or additional transformation steps.

Step-by-Step: Combine CSV Files from a Folder

Prepare a source folder. Copy the CSV files you want to include into a dedicated folder. Keep unrelated documents and previous output files elsewhere. If the folder contains subfolders or other file types, plan to filter the query so it reads only the intended CSVs.
Start the folder import. In Excel for Microsoft 365 on Windows, select Data > Get Data > From File > From Folder. Browse to the folder and select Open or OK, depending on the dialog shown.
Review the file list. Excel displays the files it found. Check that the intended files are present and that unrelated files are not included. Choose Combine > Combine & Transform Data to inspect and shape the combined data before loading it.
Check the sample file and columns. Power Query uses a sample file to determine how to interpret the CSVs. Confirm the delimiter, headers, and fields. If the first row contains column names, make sure it is treated as headers rather than as a data record.
Clean and set types. Review the preview for missing or mismatched fields. Set identifiers such as postal codes or account numbers to Text if leading zeros matter. Set dates and numbers deliberately, especially if the files use a different regional format.
Load the result. In the Power Query Editor, select Home > Close & Load to load the output to a worksheet. If you need to choose where it goes or load it as a connection, use Home > Close & Load To when available, then select the destination options.

The result is generally a single Excel table containing rows from the selected files. Depending on the combine setup, Power Query can also retain the source filename, which is valuable for tracing a row back to its original export. If you do not see that field, review the query steps and add or retain the source name before loading.

Organize the Files and Check Their Structure

Folder hygiene matters because the query can include every matching file it finds. A clear structure makes the import easier to maintain. For example, keep incoming monthly exports in a folder called Incoming CSVs, and save the Excel workbook and any final reports outside that folder. This prevents a later export or an unrelated CSV from silently becoming part of the combined result.

Before importing, compare the headers across several files. A file with a renamed field such as Customer ID instead of CustomerID may create an additional column, leaving blanks in the other records. A file with extra title rows, subtotals, or a different header position can also disrupt the combine process. Decide whether to standardize the exports first or build transformation steps that account for those differences.

Use one folder for the intended batch and avoid mixing archived and current exports.
Use consistent column names and a consistent order when you control the export process.
Check whether the source system uses commas, semicolons, or another delimiter.
Keep a copy of the original CSVs so you can re-import them if a transformation needs correction.

A file’s name can carry useful context, such as a reporting period or department. If that information is not already a column in the CSV, preserve the source filename in the query or add an appropriate date or category column during transformation. Do not assume that the file order shown in the folder is a dependable way to identify each row’s origin.

Handle Headers, Delimiters, Encoding, and Data Types

CSV files are deceptively simple. A comma inside a quoted field is part of the field, not a separator, and text may contain line breaks or quotation marks. A well-formed CSV importer understands these conventions, but inconsistent exports can still produce unexpected columns or broken rows. Use the preview to verify that a sample value such as a full address remains in one column.

If values appear in one column instead of several, the file may use a semicolon, tab, or another delimiter rather than a comma. In Power Query, select the relevant source or sample-file step and inspect its delimiter settings. Do not split a column by commas as a quick fix until you know the CSV’s actual structure; that can break addresses, descriptions, and other fields that legitimately contain commas.

Encoding affects how non-English characters appear. If accented letters or symbols turn into garbled text, revisit the file’s source settings and choose the appropriate file origin or encoding. UTF-8 is common, but the correct choice depends on how the CSV was saved. Similarly, dates such as 04/05/2026 are ambiguous without knowing whether the source uses month/day/year or day/month/year conventions.

Power Query may infer types automatically. Check those decisions rather than accepting them blindly. Product codes like 00127 are identifiers, not numbers; treating them as numeric strips the leading zeros. Set such columns to Text. For dates and decimal values from a different locale, use the relevant locale-aware type conversion in Power Query so values are interpreted as intended, not merely displayed in a familiar format.

Refresh the Batch Import When Files Change

Once the folder query is loaded, its connection remembers the folder and transformation steps. To update the table, add the new CSVs to that source folder and select Data > Refresh All. You can also right-click the loaded query table and choose Refresh. Excel reruns the query against the folder, then updates the output based on the current files and saved steps.

Refreshing does not mean Excel will correct inconsistent source data automatically. If a new export changes a header, adds a column, changes the delimiter, or alters the date format, the query may show errors or produce unexpected values. Check the refreshed result, especially the row count, column names, and a few representative records. For a recurring process, keep a short record of the expected schema and compare new exports against it.

Do not edit the query output as if it were a permanent input table. A refresh can overwrite manual changes in the loaded result. Make corrections in the source files or add them as explicit Power Query steps, so they are repeatable. If you need hand-entered notes, keep them in a separate table and connect them to the imported data using a stable identifier.

Choose the Right Output and Know the Limits

For ordinary analysis, loading the combined result to a worksheet as an Excel table is convenient: filters, structured references, and familiar formatting are available. However, an Excel worksheet can contain at most 1,048,576 rows and 16,384 columns. If the combined CSVs exceed the worksheet row limit, they cannot all be loaded to a single sheet. Loading to the Data Model may support analysis beyond the grid, but it is still subject to available memory and model constraints.

Consider whether every source file belongs in the same table. Monthly transactions with the same fields are natural append candidates. Different categories of files, files with incompatible schemas, or files representing different kinds of entities should usually be kept in separate queries and combined only through an intentional merge or relationship. Appending unlike data can create a wide table full of blank cells and misleading results.

For modest one-off batches, manual import and copy-and-paste may be quicker. For a repeatable workflow, Power Query is generally more maintainable. If the requirement is one worksheet per CSV, importing each file separately or using a carefully designed automation process is more appropriate than the standard folder-combine operation. Always retain the originals until you have checked the output.

Common Problems and Practical Fixes

Unexpected extra rows: Look for repeated header rows, blank lines, footers, or files that should not be in the folder. Filter these out in Power Query or clean the source exports.
Columns do not line up: Compare headers and data types across the files. Standardize names where possible, and investigate whether one file has an extra title row or a different layout.
Leading zeros disappear: Set the field to Text before relying on the imported output. Changing the display format to show zeros is not the same as preserving the original identifier as text.
Dates or decimals look wrong: Check the source locale and use an appropriate locale-aware conversion. A displayed date can be misinterpreted even when it looks plausible.
Refresh fails or includes the wrong files: Confirm that the source folder path still exists and inspect the file list and filters. If the workbook moved to another computer, the original folder path may not exist there.
Characters are corrupted: Recheck the CSV encoding or file origin and import settings. Re-saving a damaged display without correcting the encoding can make the problem harder to diagnose.

For a dependable check, compare the number of output rows with the expected total from the source files, allowing for any intentionally removed headers or filtered records. Also inspect a few records from different files, not just the first rows. A correct-looking first file does not prove that every file was interpreted the same way.

Excel for Mac and Excel for the Web

The menu names and available Power Query connectors can differ between Windows, Mac, and web editions. Excel for Microsoft 365 on Windows provides the folder-import workflow described above. Excel for Microsoft 365 for Mac has Power Query capabilities, but connector availability and editing features can vary by update and source; check Data > Get Data on the installed build before planning a folder-based process. If From Folder is unavailable, a Windows desktop version may be needed to create or manage that query.

Excel for the web does not provide the same full desktop import and query-authoring experience. A workbook with a query may offer some refresh capabilities depending on its source and account, but do not assume the browser can set up a folder import. Create and validate the query in the desktop application when the process depends on a local or network folder.

Frequently Asked Questions

Can Excel combine multiple CSV files into one sheet?
Yes. Use Data > Get Data > From File > From Folder, then choose Combine & Transform Data. Power Query appends compatible files into one result that can be loaded to a worksheet.

Does batch importing put each CSV on a separate tab?
Not with the usual From Folder combine workflow. It combines the files into one table. To create a separate worksheet for every CSV, import files individually or use a separate automation approach.

Will new CSV files appear automatically?
No. The query does not continuously monitor the folder. Put new files in the source folder, then refresh the query with Data > Refresh All. The query will include files that meet its saved filters.

Can I import CSVs with different column names?
You can, but the output may contain separate columns for differently named versions of the same field. Standardize the headers or add transformation steps to align them before relying on the combined table.

Why did Excel remove the zeros at the start of a code?
The column was interpreted as a number. Import the identifier as Text so values such as 00127 remain unchanged. Number formatting alone may not preserve the original text value.

Is CSV the same as an Excel file?
No. CSV stores delimited text for a single table-like dataset. It does not retain workbook features such as multiple sheets, formulas, or formatting. Save the finished result as an Excel workbook if you want to keep those features.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.