User Tools

Site Tools


data_load_problem_how_to_join_many_csv_files_together

This is an old revision of the document!


Joining Many CSV Files Together

Intro

When you have many files that have to be joined together so that you don't have to do a high number of loads, here is a way to accomplish that. You need to have Microsoft Excel on the machine, and you need to be able to run macros when you open the Excel Sheet.

Solution

The way this solution works is you create a macro in a spreadsheet. Then you change the macro to point to files on your PC. After this you should be able to merge all csv files from one directory into a file in another directory.

Step 1

Place the following code into a module in Excel.

Sub Main()

    Dim fso As Scripting.FileSystemObject
    
    Set fso = New Scripting.FileSystemObject
    
    Dim strFolder
    strFolder = "M:\Docs\WindwardCustomers\GraniteCity\Inventory"
    
    Dim folder As Scripting.folder
    
    Set folder = fso.GetFolder(strFolder)
    
    Dim file As Scripting.file
    
    Dim objFile As Scripting.TextStream
    Set objFile = fso.CreateTextFile("M:\Docs\WindwardCustomers\GraniteCity\AppendedInv.csv", ForAppending, False)
    
    For Each file In folder.Files
        fso.MoveFile file.Path, Left(file.Path, Len(file.Path) - 3) & "csv"
    Next file

    Dim tmpFile As Scripting.file
    Dim tmpStream As Scripting.TextStream
    
    For Each file In folder.Files
            Set tmpStream = fso.OpenTextFile(file.Path)
            'Set tmpStream = tmpFile.OpenAsTextStream(ForReading)
            objFile.WriteLine tmpStream.ReadAll
    Next file
    
    MsgBox "Done"
    
End Sub 

Step 2

Modify a line in the code, change the path to the directory where your csv files reside. <code|x>

  strFolder = "M:\Docs\WindwardCustomers\GraniteCity\Inventory"

</code|x>

Step 3

Modify a line in the code, change the path to the directory where your combined file will go. Do not choose the same directory as the where the csv files reside. <code|x>

  Set objFile = fso.CreateTextFile("M:\Docs\WindwardCustomers\GraniteCity\AppendedInv.csv", ForAppending, False)

</code|x>

Step 4

Run the macro in Excel. A message box saying “Done” will popup when the files have been combined.

data_load_problem_how_to_join_many_csv_files_together.1221166684.txt.gz · Last modified: 2008/09/11 13:58 (16 years ago) by cliff