Tanjima Hossain
If you are looking for ways to merge Excel files into one using CMD, then you are in the right place. Sometimes you may need to combine data from various Excel files into one and to do this quickly you just need to apply some command lines in CMD or Command Prompt.
So, let’s start with the main article.
Download Workbook
4 Steps to Merge Excel Files into One Using CMD
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. In the following, we have explained 4 steps to merge these files into one Excel file using CMD.
We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.
Step-01: Saving Excel Files as CSV Format in a Folder
The first step for merging multiple Excel files into one is to convert the XLSX files into CSV files.
➤ Go to the File Tab.
➤ Now, select the Save As option and then click on the dropdown symbol beside the Save Option.
Then, you will see different types of file formats in the list, explore them and choose the option CSV UTF-8 (Comma delimited) from the list of other formats.
➤ In addition to this, change the file name from January to 1-January (the serial numbers preceding the file name will arrange the files serially according to which serial we want to merge them).
➤ Finally, click on the Save option.
Then, we will have our new file name with the new format as 1-January.csv.
Similarly, we will change the file formats for the other two files also.
➤ For February.xlsx file select the option CSV UTF-8 (Comma delimited) from the list of other formats, change the name into 2-February, and click on Save.
After that, you will get the new filename 2-February.csv instead of February.xlsx.
➤ To change the format of the March.xlsx file select the option CSV UTF-8 (Comma delimited) from the list of other formats, change the name into 3-March, and click on Save.
Finally, you will get the new filename 3-March.csv.
Then, we have gathered them in a folder named Multiple Files where they are arranged serially. Later we will need the path to go to the folder Multiple Files.
Read More: How to Merge Excel File into Word Document
Step-02: Opening CMD or Command Prompt to Merge Excel Files into One
In this step, we will open the CMD or Command Prompt to merge the multiple Excel files into one using this CMD.
➤ Press the WINDOWS key + R and then you will have the Run wizard.
➤ Type cmd in the Open box and then press OK to run the command prompt.
As you can see, we have opened the CMD or Command Prompt.
Read More: How to Merge Multiple Excel Files into One Sheet (4 Methods)
Step-03: Using Some Commands to Merge Excel Files into One Using CMD
Now, we will type some commands with the command-line interface in this section in the Command Prompt to merge our desired files.
Firstly, we will need to copy the path of the folder Multiple Files in which we have stored our Excel files in CSV format to be merged.
➤ To have the Copy path option, you have to expand the ribbon by clicking on the dropdown symbol in the top right corner or pressing CTRL+F1.
In this way, you will be able to expand the ribbon.
➤ Select the folder Multiple Files and then click on the Copy path option.
Now, get back to the Command Prompt window, and here we will have to change the active directory to go to the path of our folder Multiple Files.
➤ Type cd and provide space after it.
➤ Press CTRL+V or Right-Click on your mouse to paste the copied path of our Multiple Files folder
After pressing ENTER, you will be taken to the directory that we have provided in the previous line.
➤ Now, type copy *.csv combined.csv (where combined is our new filename) followed by ＞in the new line.
After pressing ENTER, the following indicated command lines will be automatically generated where we can see the three file names which we want to merge.
After closing the CMD window, go to the folder Multiple Files where you will have the new merged file named combined.csv.
Read More: How to Merge Multiple Excel Files into One Sheet by VBA (3 Criteria)
Step-04: Opening New Created File and Save in XLSX Format
In the previous step, we created a new file combined.csv to merge all of the three files in the Multiple Files folder. Here, we will change the format of this file from CSV to XLSX.
➤ 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.
After that, the remnant values will be visible to us also.
➤ To save the file in XLSX format, go to the File tab.
Then, you will see various options here, and from them click on the Save As option.
➤ From the list of different formats we have selected the Excel Workbook option and then the Save option.
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.
Read More: How to Combine Multiple Worksheets into One Workbook
Conclusion
In this article, we tried to cover the steps to merge Excel files into one using CMD quickly. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.
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.