How to Merge Multiple Files to one Output File?
In our daily work routine we deal with lot of data & log files of different formats collected from different sources. To name a few .TXT, .EXE, .XLS, .XLSX, .PST, .DOC, .LOG etc., Well, am just rethinking “Should I be explaining about all files types?”, When I am just going to explain how to Join or Merge or do data consolidation of same format files into one single file.
Commands in this article can be used to merge files of same type or same file extension. At a big enterprise level, merging of ‘Big Data’ or huge volume data from different sources into meaningful information is termed as Data Integration. Where as the open source code available here is just the basics for any learner. Lets take the first step.
Data Integration or Merging files thru Programmatic way
Assume you have list of files to be merged to a single output file as mentioned in an example excel sheet as below.
The code in this article reads list of files that you provide and merge them into one file. The code provided can be modified to include any delimiter at end of each file before it is merged to the main file if required.
Copy paste the code to Excel VB Editor and Press F5 to run our simple Data Integration tool. In case, if all these coding is a complex things for you, simply use the WinZIp, WinRAR or other file compressor software available in market.
Visual Basic1234567891011121314151617181920212223242526272829303132333435363738394041Private Sub Merge_Files()'''''Declare VariablesDim InpFileNum As IntegerDim OutFileNum As IntegerDim In_File_Path As StringDim Out_File_Path As StringDim In_File_Data As String'''''Assigning the Input & Output File pathiRow = 2In_File_Path = VBA.Trim(Sheets(1).Cells(iRow, 1))Out_File_Path = VBA.Trim(Sheets(1).Cells(iRow, 2))'''''Open Output File in Proper Write ModeOutFileNum = FreeFileOpen Out_File_Path For Output As OutFileNum'''''Loop Thru each file in Input ListWhile Sheets(1).Cells(iRow, 1) <> ""'''''Open Input File in Read ModeInpFileNum = FreeFileOpen In_File_Path For Input As InpFileNum'''''Read Input File line by line till End Of File and Write to OutputWhile Not VBA.EOF(InpFileNum)Line Input #InpFileNum, In_File_DataPrint #OutFileNum, In_File_DataWend'''''Close Currently Opened File & Process Next File in Input ListClose InpFileNumiRow = iRow + 1In_File_Path = VBA.Trim(Sheets(1).Cells(iRow, 1))Wend'Close Output FileClose OutFileNumMsgBox "Files Merged"End Sub
Note: This code performs open, read & write operations on source files in ASCII text mode only. If you need to merge binary files, please refer how to access files in Binary mode.
Also Read: How to Remove Junk Data From Text File?
If not VBA, How to do Data consolidation?
Before doing this with Excel VBA code, let’s look at other alternative method. There is also simple shorter version of DOS command available to merge 2 files. Assume you have 2 files Source_File_1.exe and Source_File_2.ext. Use the Command as below. To know about more options available with this command, type ‘Help Copy’ at the dos prompt.
- Copy Source_File_1.ext + Source_File_2.ext Merged_Output_File.ext
How to dynamically Merge Files using Command Prompt?
If we know the list of files to be merged then we can use the above method. In case if we don’t have the exact list, but we need to convert a list of files found in a directory then use the below command. (Assuming we are merging list of Txt or VCF file.)
- CommandPrompt:\>for %f in (*.txt) do type “%f” >> MergedOutput.txt
- CommandPrompt:\>for %f in (contacts*.vcf) do type “%f” >> MergedContacts.vcf
Previous Post: Excel VBA Change Cell Color Index RGB code
Next Post: Excel VBA to Import Data from another Workbook – 4 Different Ways
Merge Multiple Files to One File – Data Integration