Don’t forget to share this post

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.
Combine CSV Files Into One Excel File Go to folder
Combine CSV Files Into One Excel File - Go to folder
  1. 2. Open an Excel spreadsheet and go to Data > Get Data > From File > From Folder.
Combine CSV Files Into One Excel File From Folder
Combine CSV Files Into One Excel File - From Folder
  1. 3. Select the folder containing the CSV files and click “Open”.
Combine CSV Files Into One Excel File Open folder
Combine CSV Files Into One Excel File - Open folder
  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.
Combine CSV Files Into One Excel File Combine Transform Data
Combine CSV Files Into One Excel File - Combine & Transform Data
  1. 5. Leave the selection to default in the “Combine Files” window, and click “OK” to proceed.
Combine CSV Files Into One Excel File Click OK
Combine CSV Files Into One Excel File - Click OK
  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.
Combine CSV Files Into One Excel File Filter column
Combine CSV Files Into One Excel File - Filter column
  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.
Combine CSV Files Into One Excel File Edit data format
Combine CSV Files Into One Excel File - Edit data format

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.
Combine CSV Files Into One Excel File Remove Duplicates
Combine CSV Files Into One Excel File - 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.
Combine CSV Files Into One Excel File Close and Load to
Combine CSV Files Into One Excel File - Close and Load to
How To Use Excel Power Query for Beginners Promotion
How To Use Excel Power Query for Beginners?

With Power Query, you can import data from different sources and prepare the data for analysis. Here's how to use Power Query in Excel.

READ MORE

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.
Combine CSV Files Into One Excel File Close Load to
Combine CSV Files Into One Excel File - Close & Load to
  1. 2. Press Windows+R to open the “Run” box. Type “cmd” and then click “OK” to launch the Command Prompt.
Combine CSV Files Into One Excel File Type cmd
Combine CSV Files Into One Excel File - Type cmd
  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.
Combine CSV Files Into One Excel File Type cd and path
Combine CSV Files Into One Excel File - Type cd and path
  1. 4. The new path of the folder should now appear in the command line.
Combine CSV Files Into One Excel File New path
Combine CSV Files Into One Excel File - New path
  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.
Combine CSV Files Into One Excel File Change file name
Combine CSV Files Into One Excel File - Change file name
  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.
Combine CSV Files Into One Excel File Close Cmd Prompt
Combine CSV Files Into One Excel File - Close Cmd Prompt

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 Excel Files Into One

Discover the most popular methods used to manually or automatically combine multiple Excel spreadsheets and data inputs into one master file

READ MORE
How to Combine Multiple Excel Files Into One

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, Layer.

  • 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 same Excel file.
  • Layer: As an automation tool for your business process, Layer works on top of Excel files. 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.

How to combine Excel files with Layer?

Layer is a spreadsheet platform that works on top of Excel and Google Sheets. It allows you to easily manage and automate spreadsheet workflows. Using Layer, you can:

  • Upload or connect your existing Excel or Google Sheets-based budget.
  • Share different sheets or even cell ranges of your spreadsheet with various stakeholders or departments involved in the budgeting process.
  • Automate your communication flows and keep track of your data submissions, contributors, and deadlines.
  • Review every single change made and decide which ones to merge with your spreadsheet or discard.
  • Eliminate errors in your budget or manually copying and pasting data across files.

Sign up and schedule an onboarding call to get started with Layer right now.

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.

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Apr 14 2022