How to Merge Excel Files into One Using CMD: 4 Steps

Here, we have the following three files January.xlsx, February.xlsx, and March.xlsx containing the sales records for the January, February, and March months of a company.

how to merge Excel files into one using CMD

how to merge Excel files into one using CMD

how to merge Excel files into one using CMD

We have used the Microsoft Excel 365 version here, but you can use any other version.


Step 1 – Saving Excel Files as CSV Format in a Folder

  • Go to the File Tab.

saving Excel files

  • Select the Save As option and then click on the dropdown symbol beside the Save Option.

saving Excel files

  • Choose the option CSV UTF-8 (Comma delimited) from the list of other formats.
  • Change the file name from January to 1-January (the serial numbers preceding the file name will arrange the files serially).
  • Click on the Save option.

saving Excel files

We now have our new file name with the new format as 1-January.csv.

saving Excel files

Change the file formats for the other two files:

  • For the February.xlsx file select the option CSV UTF-8 (Comma delimited) from the list of other formats, change the name to 2-February, and click on Save.

saving Excel files

After that, you will get the new filename 2-February.csv instead of February.xlsx.

saving Excel files

  • For the March.xlsx file, select the option CSV UTF-8 (Comma delimited) from the list of other formats, change the name to 3-March, and click on Save.

saving Excel files

You will get the new filename 3-March.csv.

saving Excel files

Put all three files in a folder named Multiple Files so they are arranged serially. We will need the system path to the folder later.

how to merge Excel files into one using CMD


Step 2 – Opening CMD or Command Prompt to Merge Excel Files into One

Press the WINDOWS key + R to open the Run wizard.

opening CMD window

  • Type cmd in the Open box and press Enter or click on OK to run the command prompt.

opening CMD window

This opens the Command Prompt.

opening CMD window


Step 3 – Merging Excel Files into One Using CMD

We need to copy the path of the folder Multiple Files in which we have stored our Excel files in CSV format to be merged:

  • Go to the folder.
  • Expand the ribbon by clicking on the drop-down symbol in the top-right corner or pressing Ctrl + F1.
  • using some commandsSelect the folder Multiple Files and then click on the Copy path option.

using some commands

  • Now go to the Command Prompt, where we need to change the active directory to go to the path of our folder Multiple Files:
  • Type cd and a space.

using some commands

  • Press Ctrl + V.

using some commands

  • Press Enter.

using some commands

  • Type copy *.csv combined.csv (where combined is our new filename).

using some commands

  • Press Enter and wait for the confirmation text indicating that a file was copied.

using some commands

  • Close the Command Prompt.
  • After closing the CMD window, go to the folder Multiple Files where you will have the new merged file named combined.csv.

how to merge Excel files into one using CMD


Step 4 – Opening New Created File and Save in XLSX Format

After opening the combined.csv file, we can see the Sales Record of January header first and then the corresponding values of the January file. In this way, the records of the February file and March file are also appearing serially.

  • To see the remaining values scroll down throughout the sheet.

open and save in XLSX format

Here’s how the rest of the file should look, with some remnants from copying inserted at the end.

open and save in XLSX format

Now we’ll save the file in the .xlsx format:

  • Go to the File tab.

open and save in XLSX format

  • Click on the Save As option.

open and save in XLSX format

  • Select the Excel Workbook option and then the Save option.

open and save in XLSX format

Finally, the name of the merged file is now combined.xlsx.
So, after following the aforementioned steps we have merged 3 Excel files into one with the help of CMD.

how to merge Excel files into one using CMD


Download Workbook


<< Go Back To Merge Excel File | Merge in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

2 Comments
  1. Hi! wanted to know what is the limit of entries which can be merged when using this method? I got to know that the limit of this merger method is upto 30k entries only. Please correct me if I am wrong or else what can be the limit of this?
    Regards,
    Muizz

    • Hello Muizz Shaikh,
      Thanks for your question. The maximum number of entries should be within the limit of the maximum row numbers of Excel which is 1,048,576. So, you can merge the files as long as the entries of the combined file support this limit.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo