Copy Multiple Excel Files DOS Batch Help

[Anthony22]

I have multiple CSV files that need to be copied into one, however when it creates the single file, the data on the first row in the next file gets added to the last row. i.e...

First Name, Last Name, Address, Phone Number
First Name, Last Name, Address, Phone Number
First Name, Last Name, Address, Phone NumberFirst Name, Last Name, Address, Phone Number
First Name, Last Name, Address, Phone Number
First Name, Last Name, Address, Phone Number

Is there a way to add a row prior to the next set of data being copied?

Here's what I have scripted:

COPY C:\Folder\*.csv C:\Folder\NewLocation\Master.csv

 

[Ruddles]

Put this in a file called doit.bat in C:\Folder\:-

@echo off
 
if "%1"=="" goto main
 
echo Copying %1
type %1 >>C:\Folder\NewLocation\Master.csv
echo. >>C:\Folder\NewLocation\Master.csv
goto exit
 
:main
if exist C:\Folder\NewLocation\Master.csv del C:\Folder\NewLocation\Master.csv
for %%f in (C:\Folder\*.csv) do C:\Folder\doit.bat %%f
echo.
dir C:\Folder\NewLocation\Master.csv
 
:exit

 

[Anthony22]

Ruddles, you are absolutely awesome!!! Thanks

 

[Ruddles]

Thanks for the feedback!

 

[Anthony22]

What if I have multiple folders? Is it best to create the batch for each one or can I combine them into one?

This worked for me but I ran into the original issue of wrapping. I tried to modify the code you provided but couldn't figure it out.

copy c:\folder1\*.csv c:\folder1\NewLocation\Master1.csv
copy c:\folder2\*.csv c:\folder2\NewLocation\Master2.csv
copy c:\folder3\*.csv c:\folder3\NewLocation\Master3.csv

 

[Ruddles]

The problem with using COPY *.CSV is that it doesn't give you the opportunity to insert a linefeed between each file.

To be honest it would be a lot simpler to create a batch file for each source folder.

 

[Anthony22]

Okay, that works for me. Thanks again for your wonderful help.

 

[Ruddles]

You could try this: create a new Excel workbook, press Alt-F11 to open the VB editor, then go Insert > Module and paste this code in the new module:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Fixedsys]Public Sub CombineMultipleCSV()[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Fixedsys]  Dim sFileArray As Variant
  Dim sFile As Variant
  Dim sOutFile As Variant
  Dim sFileCount As Integer
  Dim inFH As Integer
  Dim outFH As Integer
  Dim sRecord As String
  Dim dtStart As Date
  
  sFileArray = Application.GetOpenFilename( _
       FileFilter:="Comma-separated values (*.csv), *.csv, Text files (*.txt), *.txt", _
       FilterIndex:=1, MultiSelect:=True)
  If Not IsArray(sFileArray) Then Exit Sub
  
  sFileCount = UBound(sFileArray) - LBound(sFileArray) + 1
  If UBound(sFileArray) = LBound(sFileArray) Then
    MsgBox "You have only selected one input file!" & Space(10), vbOKOnly + vbExclamation
    Exit Sub
  End If
  
  sOutFile = Application.GetSaveAsFilename(FileFilter:="Comma-separated values (*.csv), *.csv")
  If sOutFile = "False" Then Exit Sub
  
  If Dir(sOutFile) <> "" Then Kill sOutFile
  
  dtStart = Now()
  
  Close
  outFH = FreeFile()
  Open sOutFile For Append As #outFH
  For Each sFile In sFileArray
    inFH = FreeFile()
    Open sFile For Input As #inFH
      Do Until EOF(inFH)
        Line Input #inFH, sRecord
        Print #outFH, sRecord
      Loop
    Close #inFH
  Next sFile
  Close #outFH
    
  MsgBox "Finished: " & CStr(sFileCount) & " files combined." & Space(10) & vbCrLf & vbCrLf _
       & "Output file: " & sOutFile & Space(10) & vbCrLf & vbCrLf _
       & "Run time: " & Format(Now() - dtStart, "hh:nn:ss") & Space(10), vbOKOnly + vbInformation
  
End Sub[/FONT]

Finally click the Run icon or press F5. You'll be prompted for the files you want to combine and then for the name of the new file you want to create.

Let me know if this doesn't work for you.

 

[Anthony22]

The macro worked to a degree but isn't the best solution for what I'm looking to implement. The original code you provided for the batch is perfect though and I appreciate the time you spent. I'm good to go for now.
Cheers

 

[Ruddles]

No probs - I originally did it for the mental exercise but then I thought of a real use for it.

So thanks for giving me the idea!

For anyone who's still lurking, here's my final code:-

[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Const WORKSHEET_NAME As String = "Sheet1" [COLOR=green]' the name of the worksheet if you want stats[/COLOR][/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Public Sub CombineMultipleFiles()[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]  Dim ws As Worksheet
  Dim sFileArray As Variant
  Dim sFile As Variant
  Dim sOutFile As Variant
  Dim sFileCount As Integer
  Dim inFH As Integer
  Dim outFH As Integer
  Dim sRecord As String
  Dim iLineCount As Long
  Dim iRow As Integer
  Dim dtStart As Date
  
  sFileArray = Application.GetOpenFilename( _
       FileFilter:="CSV files (*.csv), *.csv, Text files (*.txt), *.txt, " _
                 & "VBA files (*.bas; *.cls), *.bas; *.cls,  " _
                 & "All files (*.*), *.*", _
       FilterIndex:=1, MultiSelect:=True, Title:="Select files to open:-")
  If Not IsArray(sFileArray) Then Exit Sub
  
  sFileCount = UBound(sFileArray) - LBound(sFileArray) + 1
  If UBound(sFileArray) = LBound(sFileArray) Then
    MsgBox "You have only selected one input file!" & Space(10), vbOKOnly + vbExclamation
    Exit Sub
  End If
  
  sOutFile = Application.GetSaveAsFilename( _
      FileFilter:="CSV files (*.csv), *.csv, Text files (*.txt), *.txt", _
      Title:="Select output file:-")
  If sOutFile = "False" Then Exit Sub
  
  If Dir(sOutFile) <> "" Then Kill sOutFile
  
  dtStart = Now()
  
  If WORKSHEET_NAME <> "" Then Set ws = ThisWorkbook.Sheets(WORKSHEET_NAME)
  
  Close
  outFH = FreeFile()
  Open sOutFile For Append As #outFH
  ws.Columns("A:B").ClearContents
  ws.Columns("A:B").Font.Bold = False
  If WORKSHEET_NAME <> "" Then
    With ws
      .Range("A1") = "Filename"
      .Range("B1") = "Records"
      .Range("B1").NumberFormat = "#,##0"
      .Range("A1:B1").Font.Bold = True
    End With
  End If
  iRow = 1
  
  For Each sFile In sFileArray
    inFH = FreeFile()
    Open sFile For Input As #inFH
      iLineCount = 0
      Do Until EOF(inFH)
        Line Input #inFH, sRecord
        Print #outFH, sRecord
        iLineCount = iLineCount + 1
      Loop
    Close #inFH
    iRow = iRow + 1
    If WORKSHEET_NAME <> "" Then
      With ws
        .Cells(iRow, 1) = sFile
        .Cells(iRow, 2) = iLineCount
        ActiveWindow.ScrollRow = IIf(iRow <= 20, 1, iRow - 20)
      End With
    End If
  Next sFile
  Close #outFH
  
  If WORKSHEET_NAME <> "" Then
    With ws
      .Cells(iRow + 1, 1) = "Total"
      .Cells(iRow + 1, 1).Font.Bold = True
      .Cells(iRow + 1, 2) = "=SUM(B2:B" & CStr(iRow) & ")"
      .Cells(iRow + 1, 2).Font.Bold = True
    End With
  End If
  
  MsgBox "Finished: " & CStr(sFileCount) & " files combined." & Space(10) & vbCrLf & vbCrLf _
       & "Output file: " & sOutFile & Space(10) & vbCrLf & vbCrLf _
       & "Run time: " & Format(Now() - dtStart, "hh:nn:ss") & Space(10), vbOKOnly + vbInformation
  
  ActiveWindow.ScrollRow = 1
  
End Sub[/FONT]