Combine Multiple CSV Files Into One Excel File (Multiple Methods)

Although a CSV file can hold a huge amount of data, the sheer weight of these files can cause issues when you want to share the file with others. As a result, large CSV files are often divided into multiple, smaller files. This can become an overwhelming task when you need to edit your data. Luckily, CSV files are compatible with most software applications, so you can easily import your CSV files into your desktop and online spreadsheet tools.

In this article, you’ll learn how to combine multiple CSV files into one Excel file by using the Power Query built-in feature. You’ll also learn how to use the Windows Command Prompt function to merge multiple CSV files. Finally, you’ll explore our top online tools that are an alternative to Excel’s built-in functions.

How to combine multiple CSV files into one?

Combining CSV files into one is crucial to leveling up your productivity and efficiency in data transformation and management. To avoid making manual errors with the traditional copy and pasting method, we will show you two powerful ways to combine CSV files into one Excel file:

• The Power Query feature in Excel allows you to combine multiple CSV files into one.
• The Command Prompt function in Windows lets you merge CSV files easily.

Note that using Power Query as a built-in feature is only available in Excel 2019 and 365. If you are working on macOS, you cannot use all the features shown in this article, including the “Power Query Editor”. We recommend advancing to the last section to learn about alternative ways to combine multiple CSV files into one Excel file. Let’s begin by showing how to use the Power Query feature in Excel.

How to combine multiple CSV files into one using Power Query?

In How to Use Excel Power Query for Beginners, we illustrate how Power Query can be used to import data from various sources and then combine and merge tables according to your formatting preferences. The best thing about this is you won’t have to write a single line of code. Now, we’ll show you how you can import and combine multiple CSV files and format them according to your preference.

1. 1. Go to the folder where you have stored all your CSV files. If not, create a folder only containing the CSV files you would like to combine.

1. 2. Open an Excel spreadsheet and go to Data > Get Data > From File > From Folder.

1. 3. Select the folder containing the CSV files and click “Open”.

1. 4. Select one of the three options available from the “Combine” drop-down menu: “Combine & Transform Data”, “Combine & Load”, or “Combine & Load To…”. We recommend the first option since it loads all CSV files to the “Power Query Editor” where you can arrange and sort the data according to your preference.

1. 5. Leave the selection to default in the “Combine Files” window, and click “OK” to proceed.

1. 6. In the “PowerQuery Editor” window, activate the filter in the “Source.Name” column to delete any other file if the folder contains other files.

1. 7. If you wish to edit the data formatting of a specific column, click on its header, then “Data Type”. For example, you can change certain values to “Currency” or “Date” formats.

The “Power Query Editor” offers a great range of data transformation options, including the removal of duplicates. This is especially useful when combining multiple CSV files, considering that you are dealing with very similar data. To do this:

1. 8. Select the column, aka “unique identifier”, where you want to keep unique values, and then Remove Rows > Remove Duplicates.

1. 9. Once you finish editing your data, go to Home > Close & Load or “Close & Load To…”. This depends on whether you want to import your data to a new sheet as a table or to an existing sheet as a table, “PivotTable” or “PivotTable chart”. Here I will select the first option as I want to import it into a different sheet as a table.

How to merge multiple CSV files in Excel using Command Prompt?

The Command Prompt tool, aka CMD, is a Windows Explorer feature; for this feature to work accurately, make sure you have stored only the CSV files you will merge into one Excel file into one folder.

1. 1. Go to the folder storing your CSV files and press down Shift as you right-click the folder and select “Copy path”. In Windows 10, this function will also appear in the Home tab of the File Explorer.

1. 2. Press Windows+R to open the “Run” box. Type “cmd” and then click “OK” to launch the Command Prompt.

1. 3. Type “cd” followed by a space in the “Command Prompt” window, and then press Ctrl + V to paste the folder path. A faster way to do this is to drag the folder and drop it into the “Command Prompt” window. Press “Enter” to execute the command.

1. 4. The new path of the folder should now appear in the command line.

1. 5. Type “copy *.csv merged-csv-files.csv” in the command line, right after the folder path. Once you finish, press Enter. Note that you can substitute “merged-csv-files.csv” for a filename of your preference.

1. 6. Close the “Command Prompt” window and head to the folder containing your original files. You should now see a file named “merged-csv-files.csv” or the name you chose.

The methods you have seen so far apply to Windows 10 and later versions, as well as Excel 2019 and 365. However, suppose you are currently working on other versions of Windows, Excel, a different operating system, or prefer to work online. In that case, we recommend the following tools to combine multiple CSV files into one Excel file.

How to combine multiple CSV files into one Excel file online?

There are many free online tools available for you to merge CSV files into one. We suggest the following options, including our all-around automation tool, Sheetgo.

• Sheetgo: As an automation tool for your business processes, Sheetgo integrates your files and tools to give you full control over your data. It allows you to manage and automate spreadsheet workflows by selecting the data to share, edit, or merge. It will automatically merge files and manage changes according to your set preferences.
• Merge-csv: This is a completely free online tool where you can upload the CSV files and download them into one Excel file. The advantage here is that you don’t need to install any software; the disadvantage is that you lack control over the type of import.
• Ablebits.com: Ablebits released the Copy Sheets tool within their Ultimate Suite for Excel, which allows you to import each CSV file as a separate sheet in one Excel file.

Conclusion

In a few simple steps, without the need to write code, you can continue using CSV files to collect data and use Excel spreadsheets to manage it, regardless of the size of your data or the type of operating system you use.

By the end of this article, you should now know how to combine multiple CSV files into one Excel file using the two methods available for users working on Windows 10 or later versions, as well as Excel 2019 and 365. If you are working on previous versions or MacOS, you will require an add-on to use Power Query or CMD.

If you are interested in learning more about combining multiple Excel files into one, read our blog article, How to Combine Multiple Excel Files Into One. For Google Sheets users, read How to Import CSV files to Google Sheets.