Combine data from 2 or more csv files into one csv file using VBA

I have an Excel workbook set up with a macro that extracts information from 3 worksheets and creates  3 .csv files containing the information in the same format in the same separate columns.  The 3 .csv are located in one folder.  I want to combine the data of all three files and create one .csv file.  One of the files has a header record and the one of the files has a trailer record.  I want the macro to combine in sequence so that I end up with one file that has a header, detail and a trailer.

Can I do this?
jlove88Program ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
"Can I do this?"

Is your question really, "Can somebody help me with this, please?".

If so, please provide the "workbook set up with a macro" you already have available, & examples of the three ".csv" files you wish to combine.

However, you may not need to use MS-Excel to combine three comma-separated values files.

You could use an MS-DOS (Command) Prompt, & execute a command like the one below:

copy file1.csv+file2.csv+file3.csv output.csv

The three "input" files ("file1.csv", "file2.csv", & "file3.csv"), plus the single resultant "output" file ("output.csv") is attached.
file1.csv
file2.csv
file3.csv
output.csv
Kanti PrasadCommented:
Hi

You can do it as you are already reading and loading into 3 different sheets. Here is a sample
http://www.homeandlearn.org/open_a_text_file_in_vba.html

or attached the xls you have I can change it for you!

If you see you code you will have to open file to read or write
Open FilePath For Input As #1
Open FilePath For Output As #2

Line Input #1, LineFromFile

so read all the 3 files here instead they be read individually

then write it to one output

Write #2, CellData

Close #1
Close #2
So change your code at the lines after the header is imported and paste some relevant code from the 2nd file import and then paste some relevant code that is in the trailer.
jlove88Program ManagerAuthor Commented:
Thanks for the input.  Re: using  DOS commands in .bat file - I have such a file which combines data into one file, however, I really wanted to have a macro do this so that all the end user has to do is click on a macro button and it creates the end result.

So far my macro extracts data from three worksheets in the same workbook and saves it in 3 separate files.  What I want is all the information that I have extracted from the 3 worksheets to end up in one CSV file with a header and a trailer.  Code so far is as follows:

Dim FirstPass As Boolean
Dim RowCount As Long
Dim ColumnCount As Long
Dim wrk_fname As String
Dim icon1 As Integer
Dim retVal As Long
Dim targetDir As String
Dim targetDir1 As String
Dim targetDir2 As String
Dim targetDir3 As String
Dim myName As String
Dim myPass As String
Dim IE
 
Sub Output()
     
'The location where the files to be uploaded are placed:
 
targetDir = Sheet5.Cells(3, 2)
targetDir1 = targetDir + "\Output\"
targetDir2 = Sheet5.Cells(7, 2)
targetDir3 = targetDir + "\Archive\"
myName = Sheet5.Cells(4, 2)
myPass = Sheet5.Cells(5, 2)
myServer = Sheet5.Cells(6, 2)
 
'Check Output folder exists
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FolderExists(targetDir1) = False Then
Set objFolder = fs.CreateFolder(targetDir1)
End If
     
'Check Archive folder exists
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FolderExists(targetDir3) = False Then
Set objFolder = fs.CreateFolder(targetDir3)
End If
 
'Copy prior output files to Archive
Dim source As String
Dim dest As String
Dim FileName As String
 
source = targetDir1
dest = targetDir3
 
If Dir(source & "*.csv") > "" Then
 
FileName = Dir(source & "*.csv")
   Do While Len(FileName) > 0
      FileCopy source & FileName, dest & FileName
      FileName = Dir
   Loop
 
Kill source & "*.*"
     
End If
 
fname = targetDir1 + "rcirate1" + ".csv"
 
' 1. Create output
 
Open fname For Output As #1
 
Print #1, "Pay Group"; ","; "File Number"; ","; "Rate 4"; ","; "Rate 5"; ","; "Rate 6"
 
For RowCount = 2 To 10000                                '
    comp = Sheet1.Cells(RowCount, 1)
  If comp <> "" And comp = "WAPrem" And Sheet1.Cells(RowCount, 28) <> 0 Then
    paygrp = Sheet1.Cells(RowCount, 2)
    file = Sheet1.Cells(RowCount, 3)
    rt1 = ""
    rt2 = ""
    rt3 = Format(Sheet1.Cells(RowCount, 28), "0.0000")
     
    Print #1, paygrp; ","; Trim(file); ","; Trim(rt1); ","; Trim(rt2); ","; Trim(rt3)
     
    End If
   
Next
   
Close #1
 
fname1 = targetDir1 + "rcirate2" + ".csv"
 
' 1. Create output
 
Open fname1 For Output As #1
 
 
For RowCount = 2 To 10000                                '
    comp1 = Sheet2.Cells(RowCount, 1)
  If comp1 <> "" And comp1 = "TOYRR" And Sheet2.Cells(RowCount, 9) <> 0 Then
    paygrp = Sheet2.Cells(RowCount, 4)
    file = Sheet2.Cells(RowCount, 2)
    rt1 = Format(Sheet2.Cells(RowCount, 9), "0.0000")
    rt2 = ""
    rt3 = ""
     
    Print #1, paygrp; ","; Trim(file); ","; Trim(rt1); ","; Trim(rt2); ","; Trim(rt3)
   
   
    End If
   
Next
   
Close #1
 
fname2 = targetDir1 + "rcirate3" + ".csv"
 
Open fname2 For Output As #1
 
 
 
For RowCount = 2 To 10000                                '
    comp2 = Sheet3.Cells(RowCount, 1)
  If comp2 <> "" And comp2 = "WA1vac" And Sheet3.Cells(RowCount, 28) <> 0 Then
    paygrp = Sheet3.Cells(RowCount, 2)
    file = Sheet3.Cells(RowCount, 3)
    rt1 = ""
    rt2 = Format(Sheet3.Cells(RowCount, 28), "0.0000")
    rt3 = ""
   
    Print #1, paygrp; ","; Trim(file); ","; Trim(rt1); ","; Trim(rt2); ","; Trim(rt3)
   
   
    End If
   
Next
    Print #1, "ZZZ"; ","; Trim(Sheet5.Cells(11, 3)); ","; Trim(Sheet5.Cells(11, 4)); ","; Trim(Sheet5.Cells(11, 5))
   
Close #1
 
End Sub
[ fanpages ]IT Services ConsultantCommented:
"Thanks for the input.  Re: using  DOS commands in .bat file - I have such a file which combines data into one file, however, I really wanted to have a macro do this so that all the end user has to do is click on a macro button and it creates the end result."

Without reading further into your existing Visual Basic for Applications code, are you opposed to simply calling the previously proven ".bat" batch file directly from MS-Excel (&, optionally, monitoring the result for a known positive outcome)?

If not, & I can appreciate that you may wish to move away from the use of MS-DOS, then I am presuming the code you posted above creates the three files (fname, fname1, and fname2).

If so, simply remove the two later calls to Open fname1 For Output As #1, and Open fname2 For Output As #1, & only use Close #1 once (right at the end of the processing).

After the first Open statement, all subsequent writing (Print #1 statements) will be directed to a single file (fname).

For example...

' ALL CODE IS TO BE RETAINED PRIOR TO HERE

fname = targetDir1 + "rcirate1" + ".csv"
 
' Create output
 
Open fname For Output As #1
 
Print #1, "Pay Group"; ","; "File Number"; ","; "Rate 4"; ","; "Rate 5"; ","; "Rate 6"
 
For RowCount = 2 To 10000                                '
    comp = Sheet1.Cells(RowCount, 1)
  If comp <> "" And comp = "WAPrem" And Sheet1.Cells(RowCount, 28) <> 0 Then
    paygrp = Sheet1.Cells(RowCount, 2)
    file = Sheet1.Cells(RowCount, 3)
    rt1 = ""
    rt2 = ""
    rt3 = Format(Sheet1.Cells(RowCount, 28), "0.0000")
     
    Print #1, paygrp; ","; Trim(file); ","; Trim(rt1); ","; Trim(rt2); ","; Trim(rt3)
     
    End If
   
Next
   
' Close #1 <- *** REMOVE THIS LINE
' fname1 = targetDir1 + "rcirate2" + ".csv" <- ALSO REDUNDANT
' Open fname1 For Output As #1 <- NOT REQUIRED
  
For RowCount = 2 To 10000                                '
    comp1 = Sheet2.Cells(RowCount, 1)
  If comp1 <> "" And comp1 = "TOYRR" And Sheet2.Cells(RowCount, 9) <> 0 Then
    paygrp = Sheet2.Cells(RowCount, 4)
    file = Sheet2.Cells(RowCount, 2)
    rt1 = Format(Sheet2.Cells(RowCount, 9), "0.0000")
    rt2 = ""
    rt3 = ""
     
    Print #1, paygrp; ","; Trim(file); ","; Trim(rt1); ","; Trim(rt2); ","; Trim(rt3)
   
   
    End If
   
Next
   
' Close #1 <- *** REMOVE THIS LINE
' fname2 = targetDir1 + "rcirate3" + ".csv" <- ALSO REDUNDANT
' Open fname2 For Output As #1 <- NOT REQUIRED
  
For RowCount = 2 To 10000                                '
    comp2 = Sheet3.Cells(RowCount, 1)
  If comp2 <> "" And comp2 = "WA1vac" And Sheet3.Cells(RowCount, 28) <> 0 Then
    paygrp = Sheet3.Cells(RowCount, 2)
    file = Sheet3.Cells(RowCount, 3)
    rt1 = ""
    rt2 = Format(Sheet3.Cells(RowCount, 28), "0.0000")
    rt3 = ""
   
    Print #1, paygrp; ","; Trim(file); ","; Trim(rt1); ","; Trim(rt2); ","; Trim(rt3)
   
   
    End If
   
Next
    Print #1, "ZZZ"; ","; Trim(Sheet5.Cells(11, 3)); ","; Trim(Sheet5.Cells(11, 4)); ","; Trim(Sheet5.Cells(11, 5))
   
Close #1

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jlove88Program ManagerAuthor Commented:
Thank you for your feedback.  I will likely go with changing the code to your suggestion, however, I am not opposed to using the MS-DOS command and would be interested in learning how that can be called from Excel.  My .bat file is very simple:  COPY OUTPUT\*.csv OUTPUT\rcirate.csv

Thanks again.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.