Excel Get Data From CSV Files in a Batch: Power Query

Yes. In Excel for Microsoft 365 on Windows, go to Data > Get Data > From File > From Folder, select the folder containing the CSV files, then choose Combine & Transform Data. Power Query can combine the files into one table, and you can refresh the query later to bring in new files from the same folder.

What batch import does

Batch importing is useful when several CSV files contain the same kind of records: for example, one sales export per month, daily inventory reports, or survey results split across departments. Instead of opening every file and copying its rows into a workbook, Power Query reads the files and appends their records into a single result.

This is different from placing several separate CSV files on separate worksheets. A folder query normally creates one combined table, with rows from the source files stacked together. If each file represents a different subject or has a different layout, a single combined table may not be appropriate; you may need separate queries or a deliberate transformation first.

CSV means comma-separated values, but the files are plain text, not Excel workbooks. They do not preserve cell formatting, formulas, multiple sheets, or Excel-specific features. The import process interprets each file’s text as rows and columns, then Power Query applies steps such as promoting a header row, setting data types, and combining records.

Import a folder of CSV files

Put the files you want to combine in one folder. Keep unrelated files elsewhere, or plan to filter them out in Power Query.
In Excel, open a blank workbook or the workbook that should contain the result. Select Data > Get Data > From File > From Folder.
Browse to the folder and select Open or OK, depending on the dialog. Excel shows a file list. Choose Combine & Transform Data to inspect and shape the data before loading it.
In the combine prompt, check the sample file and parsing options. Confirm the delimiter and file origin or encoding if the preview looks wrong, then continue.
In the Power Query Editor, review the combined preview. Check the headers, columns, data types, and any rows that should be removed. Choose Home > Close & Load to put the result in Excel, or use Home > Close & Load To to choose a destination and loading option.

The result is usually an Excel table. Power Query also creates supporting queries that describe how to interpret an example file and apply those steps to the other files. Those helper queries are normal parts of the process; avoid deleting them unless you understand how the main query depends on them.

Prepare the files for a dependable combine

Power Query works best when every CSV follows a consistent structure. For monthly sales exports, for example, each file should have the same headers—such as Date, Product, Quantity, and Sales—and use the same meaning for each column. Matching headers are more important than identical file names. A filename such as Sales-January.csv can differ from Sales-February.csv without affecting the rows, provided the contents follow the expected layout.

Use one header row. If some files have a title, blank lines, or a report summary above the column names, the sample-file transformation may treat those lines as data.
Keep column names consistent. “Customer ID” and “CustomerID” may be treated as different fields. Differences in spelling, punctuation, or spaces can create unexpected columns or missing values.
Check delimiters and quoting. A comma inside a quoted address or product description should remain part of that field. Some systems use semicolons as separators, especially when regional settings use commas for decimal numbers.
Protect identifiers from numeric conversion. Account codes, postal codes, and product IDs such as 00127 are identifiers, not quantities. Set these columns to Text so leading zeros are retained.
Keep a predictable folder scope. The folder connector can include files below the selected folder as well as files directly inside it. Review the Folder Path column and filter it if only one level or a particular subfolder should be included.

Before combining hundreds of exports, test with a small representative set. Include an ordinary file and any file that differs in a known way, such as a month with an extra field. That exposes structural problems before the output becomes a reporting dependency.

Choose the right parsing and data types

A CSV preview that looks garbled often reflects a parsing choice rather than damaged data. In the combine settings, the delimiter must match the file: comma, tab, semicolon, or another separator. The file origin setting controls character encoding. If names with accents or symbols appear as replacement characters, try the correct encoding, often UTF-8, and inspect the preview again.

Data types deserve special attention. Power Query may infer types from the contents, but an inferred type is not necessarily the right business meaning. A column that looks numeric can still be an identifier, and a date such as 03/04/2026 is ambiguous without knowing whether the source uses month/day/year or day/month/year. In the editor, select the column and choose an appropriate type. For ambiguous dates, use the option to change type with a locale so that Power Query interprets the values according to the source’s regional convention.

Also look for values that violate the expected type. A Quantity column may contain “N/A” in one file, while the other files contain numbers. Converting the column to a number can produce errors for those rows. Decide whether to replace the text with a blank, retain it as text, or correct the source; do not simply remove errors if the affected records matter.

Refresh the import when files change

After loading the combined table, save the workbook. When another matching CSV is added to the source folder, use Data > Refresh All to rerun the query. Power Query reads the folder again and applies the saved transformation steps. If a source file is removed, its records will generally disappear from the refreshed result as well.

Refresh is not the same as editing the output table. Treat the query result as generated data: manual edits made directly in the loaded table may be replaced or lost when the query refreshes. Make corrections in the source CSV, or add a separate worksheet or query for adjustments that should persist. If the folder moves, edit the query’s Source step in Power Query or use the query’s data source settings to point to the new location.

For a workbook used by other people, document the folder path and refresh process. A query that points to a local path such as a user’s Downloads folder may fail on another computer. A shared network location or an agreed shared folder can make the source more accessible, but users still need permission to read it and the workbook must be able to reach it.

Common problems and practical fixes

Only some files appear. Check file-name filters, extension filters, and the Folder Path column in the source query. Also verify that the missing files are in the selected folder tree and are actually CSV files.
Columns are shifted or merged. Confirm the delimiter and inspect whether quoted text contains separators. A semicolon-delimited file parsed as comma-delimited data will not split into the intended fields.
Leading zeros disappear. Change the affected column to Text in the transformation steps, then refresh. If the source has already stored a code without its zeros, the import cannot reliably reconstruct the original value without a known rule.
Some records have errors or blank columns. Compare the affected CSV with the sample file. Look for changed headers, extra fields, inconsistent dates, blank lines, or values that do not match the selected data type.
The query reports that a file is unavailable. Close the source file in another application if it is locked, check that the path still exists, and confirm that the current user has access. A workbook moved to a different computer may still be pointing to the original computer’s folder.

When a new export adds a column, do not assume it will automatically appear in the loaded result exactly as intended. The combine process uses transformation steps based on a sample file, and the expansion step can limit which columns are included. Inspect the query after a schema change and update the steps if necessary.

Folder import compared with other methods

For a recurring batch, Power Query’s folder connector is usually more maintainable than manually opening files and copying rows. It records the transformation steps, keeps the source files separate, and can repeat the combine on refresh. It is also easier to audit: you can inspect the query and its steps rather than relying on a sequence of manual edits.

Importing one file with Data > Get Data > From File > From Text/CSV is sensible when the task is a one-off or when the files have different structures. You can also create individual queries and append them in Power Query, but that requires managing each source separately. Copying and pasting is quick for a tiny set of files, but it is error-prone and does not provide a repeatable refresh process.

Do not confuse combining CSV files with merging tables. Combining, often called appending, stacks rows from similar files. Merging matches rows from different tables using a shared key, such as Customer ID. If January and February sales have the same columns, append them. If you need to add customer names from a separate customer list, merge the sales data with that list.

Excel for Mac and other access options

The steps above are for Excel for Microsoft 365 on Windows. Power Query is available in current Microsoft 365 editions of Excel for Mac, but connector and authoring capabilities can vary by Excel build. If your Mac version does not show From Folder, update Excel and check the available options under the Data tab. You can instead use a Windows version of Excel for the folder import, or import files individually and append them where the available Power Query features support that workflow.

Excel for the web is not a like-for-like substitute for creating a Windows folder query. Availability for creating or refreshing queries depends on the connector and the workbook’s environment. If the workbook relies on a local folder or network path, use desktop Excel with access to that path. For a team process, verify the exact source location and refresh behavior in the environment where the workbook will be maintained.

Frequently Asked Questions

Can Excel automatically import every CSV in a folder?
Yes. Use Data > Get Data > From File > From Folder, combine the files, and load the query. Excel does not silently monitor the folder; run Data > Refresh All when you want the query to check for changes.

Do all CSV files need the same name?
No. The names can differ. The files should have a consistent layout and compatible headers. You can filter by file name or extension in Power Query if the folder also contains files that should not be included.

Will new CSV files be included automatically?
They can be included on the next refresh if they are in the query’s folder scope and match the filters and expected structure. Adding a file does not necessarily update the table immediately; refresh the query to retrieve it.

Can I combine CSVs with different columns?
Sometimes, but inconsistent schemas need review. Power Query may produce blank values for fields absent from a file, while changes to the sample-file transformation or expansion step can affect which columns appear. Standardize the source files where possible, then inspect the combined preview and query steps.

Why did Excel remove zeros from my codes?
The column was interpreted as a number. In Power Query, set the column’s type to Text before loading, then refresh. Text preserves codes such as 00127; a numeric type treats them as the number 127.

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.