# Combine two files using Command Prompt



## abcluv (Jul 1, 2010)

Hi 

I have two files i need to merge, one is a .TSV file, the other a .CSV file

File1.tsv
ID Field1 Field2
1 detail1 Detail2
2 detail6 Detail7
3 detail9 
4, detail11 detail12

File2.csv
ID, Field3
1, detail3
2, detail8
3, detail10
4,


Output
ID, Field1, Field2, Field3
1, detail1, Detail2, detail3
2, detail6, Detail7, detail8
3, detail9, ,detail10
4, detail11, detail12,


I have very limited scripting knowledge - but what is the best way to combine these files, preferably using command prompt or perl script already written, something simple becuase I will be adding this to a .bat file i created to process since I have about 40 files I need to do this to each with about 500K rows. 

The above is a great start - ideally what I need to do is where there is a value in Field3 keep it and ignore the value is field2. Or keep the value in field2, if there is no field3 value. 

IDEAL Output
ID, Field1, Field4, 
1, detail1, detail3
2, detail6, detail8
3, detail9, detail10
4, detail11, detail12

But if I can at least get the files combined that would be a great start! 

I hope that make sense! 

Thank you!


----------



## Squashman (Apr 4, 2003)

Is there a header record at the top of each file?
Is one file tab delimited and the other file is comma delimited?
You want the output to be comma delimited?


----------



## abcluv (Jul 1, 2010)

There is a header row for each file
One file is tab delimited and the other is comma delimited
Yes - I would like the output to be comma delimited

thank you!


----------



## TheOutcaste (Aug 8, 2007)

Welcome to TSG!

A batch file would need to read one of the files into memory first, so that might not be practical with files as large as yours, so a VBScript would be easier.
This should do the trick, it gives both output files:

```
Option Explicit

Const ForReading = 1
Const ForWriting = 2

Dim strTSVFile, strCSVFile, strCombFile, strOutFile
Dim strRecord, strCombRecord, strIdealRecord, strNewHead, strIdealHead
Dim objFSO, objTSVFile, objCSVFile, objCombFile, objOutFile
Dim i, arrRecords
Dim strID1, strID2, strField1, strField2, strField3, strField4

strTSVFile = "C:\Temp Dir\vbtest\File1.tsv"
strCSVFile = "C:\Temp Dir\vbtest\File2.csv"
strCombFile = "C:\Temp Dir\vbtest\combined.csv"
strOutFile = "C:\Temp Dir\vbtest\Ideal.csv"

' Open files
Set objFSO = CreateObject("Scripting.FileSystemObject") 
Set objTSVFile=objFSO.OpenTextfile(strTSVFile, ForReading) 
Set objCSVFile=objFSO.OpenTextfile(strCSVFile, ForReading) 

' Check if the output files exist, create them if not, open if they do
' True will overwrite an existing file, False will not

If objFSO.FileExists(strCombFile)=False Then
  Set objCombFile = objFSO.CreateTextFile(strCombFile, True)
Else
  Set objCombFile = objFSO.OpenTextFile(strCombFile, ForWriting)
End if

If objFSO.FileExists(strOutFile)=False Then
  Set objOutFile = objFSO.CreateTextFile(strOutFile, True)
Else
  Set objOutFile = objFSO.OpenTextFile(strOutFile, ForWriting)
End if

' Read in header from file1
strRecord = objTSVFile.ReadLine 
arrRecords = Split(strRecord, vbTab)
strID1 = arrRecords(0)
strField1 = arrRecords(1)
strField2 = arrRecords(2)

' Read in header from file2
strRecord = objCSVFile.ReadLine 
arrRecords = Split(strRecord, ",")
strID2 = arrRecords(0)
strField3 = arrRecords(1)

' Make new headers
strNewHead = strID1 & "," & strField1 & "," & strField2 & "," & strField3
strIdealHead = strID1 & "," & strField1 & "," & "Field4"

' write new header to file
objCombFile.WriteLine strNewHead
objOutFile.WriteLine strIdealHead

' read in one line at a time, split, check, combine
' files should have the same number of lines.
On Error Resume Next
Do Until objTSVFile.AtEndOfStream 
  strRecord = objTSVFile.ReadLine 
  arrRecords = Split(strRecord, vbTab)
  strCombRecord = arrRecords(0) & "," & arrRecords(1) & "," & arrRecords(2)
  strField2 = arrRecords(2)
  strIdealRecord = arrRecords(0) & "," & arrRecords(1)
  strRecord = objCSVFile.ReadLine 
  arrRecords = Split(strRecord, ",")
  strCombRecord = strCombRecord & "," & arrRecords(1)
  If arrRecords(1) = "" Then
    strField4 = strField2
  Else
    strField4 = arrRecords(1)
  End If
  strIdealRecord = strIdealRecord & "," & strField4
  objCombFile.WriteLine strCombRecord
  objOutFile.WriteLine strIdealRecord
Loop

objTSVFile.Close
objCSVFile.Close
objCombFile.Close
objOutFile.Close
```
Be easy to modify so you can drag and drop the files you want to combine, just need to have different extensions so you can determine which file is using which delimiter. The output file names can be automatically generated from the two files dropped as well.


----------



## abcluv (Jul 1, 2010)

TheOutcaste - thank you!! i think this should work great!


----------

