How to Import Multiple CSV Files into Excel at the Same Time
Question details
Users need a streamlined method to batch import dozens of CSV files into a single Excel workbook, avoiding the tedious process of importing them one by one.
- Product
- Microsoft Excel
- Device & OS
- Windows, Mac
- Scenario
- Consolidating data from dozens of identically structured CSV files into a single Excel spreadsheet for data analysis.
- Observed behavior
- The default Import Data wizard only allows the selection of a single CSV file at a time, creating a bottleneck, particularly for Excel for Mac users.
Ensure all the CSV files you intend to import share the same column structure and are consolidated into a single, dedicated folder on your computer.
Use Power Query to Import CSV Files from a Folder
The most efficient, no-code method to merge multiple CSV files is using Power Query's 'From Folder' feature, which is fully supported in Excel for Windows.
Power Query automates the extraction and consolidation process. When you point it to a specific directory, it can append the data from all CSV files within that folder into one continuous table.
Navigate to the 'Data' tab on the Excel ribbon, click on 'Get Data', then select 'From File', and choose 'From Folder'.
Browse your computer's directory to locate the folder containing your CSV files, select it, and click 'Open'.
In the preview window that appears, click the 'Combine' dropdown button at the bottom and select 'Combine & Transform Data'.
The Power Query Editor will open, allowing you to review the merged data. Once verified, click 'Close & Load' in the Home tab to import the final dataset into your worksheet.
Create a VBA Macro to Process Multiple CSV Files
For Mac users or those on older versions of Excel without full Power Query support, writing a VBA macro is the best workaround to automate the bulk import process.
Manage Complex Data and Macros Easily with WPS Office
If you are struggling with Excel's limitations across different operating systems, consider switching to WPS Office. It provides a highly compatible, lightweight spreadsheet environment with robust support for VBA macros and CSV file handling, making bulk data management seamless and cost-effective.
- 1. Download the software: Visit the official WPS Office website and download the free installer for Windows, Mac, or Linux.
- 2. Open your CSV or Excel files: Launch WPS Spreadsheet and open your existing data files directly without format conversion.
- 3. Run your batch import macros: Access the Developer tab to run your VBA scripts and batch import your CSV files effortlessly.
Frequently Asked Questions
Why does Excel only let me import one CSV file at a time?
The standard 'Import Data from Text/CSV' wizard is specifically designed to parse the schema and delimiters of a single file. To merge multiple files automatically, Excel relies on its advanced data engine, Power Query, or custom VBA scripts.
Does the Power Query 'From Folder' feature work on Mac?
Currently, Power Query for Excel on Mac has fewer connectors than the Windows version. The 'Get Data From Folder' option is often unavailable or severely limited on Mac, forcing users to rely on VBA macros to automate multiple file imports.
Will importing multiple CSV files duplicate my column headers?
If you use Power Query's 'Combine & Transform' feature, Excel automatically detects the column headers from the sample file and removes the duplicate header rows from the appended files. If you use a VBA macro, you must ensure your script is written to skip the first row of subsequent files.
