Joint Technologies ltd

 

Found this cool script to merge a bunch of CSVs into one big one. works great after you use my other post on XLS to CSV convertion.

Ron de Bruin (last update 17 March 2006)

1) Non VBA example from Dave Peterson 

2) VBA example I made based on Dave's example

Note: with a few small changes you can also use this for tx t files

Change *.csv to *.txt in example 1 and 2 and in the code also use Tab = true

 

Example 1

 

Merge all data from the csv files into a text file

1) Windows Start Button | Run
2) Type cmd and hit enter ("command" in Win 98)
3) Go to the folder with the CSV files (for help enter "help cd")

4) Type copy *.csv all.txt and hit enter to copy all data in the files into all.txt.

5) Type exit and hit enter to close the DOS window

 

Now we must import the text file all.txt into Excel.

 

1) Open Excel

2) When you use File Open to open all.txt the Text Import Wizard will help you import the file

3) Choose Delimited

4) Next

5) Check Comma

6) Finish

 

 

Example 2

This code will ask you to browse to the folder with the csv files and after you click OK in this dialog

it merge all data into a txt file and then import and save it into a Excel file for you.

If you want to have more control then you can also use code from this page to open the csv files and copy.

http://www.rondebruin.nl/copy3.htm

Change xls in the code to csv if you want to use it.

Copy the code below into a normal module of a workbook :

Alt-F11

Insert>Module

Paste the macro

Alt q to go back to Excel

Alt F8 to open your macro list

Select Merge_CSV_Files and press Run

' Start Code

Declare Function OpenProcess Lib "kernel32" _
        (ByVal dwDesiredAccess As Long, _
        ByVal bInheritHandle As Long, _
        ByVal dwProcessId As Long) As Long
 

Declare Function GetExitCodeProcess Lib "kernel32" _
        (ByVal hProcess As Long, _
        lpExitCode As Long) As Long
 

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103
 

Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
    Dim hProg As Long
    Dim hProcess As Long, ExitCode As Long
    'fill in the missing parameter and execute the program
    If IsMissing(WindowState) Then WindowState = 1
    hProg = Shell(PathName, WindowState)
    'hProg is a "process ID under Win32. To get the process handle:
    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
    Do
        'populate Exitcode variable
        GetExitCodeProcess hProcess, ExitCode
        DoEvents
    Loop While ExitCode = STILL_ACTIVE
End Sub

Sub Merge_CSV_Files()
    Dim BatFileName As String
    Dim TXTFileName As String
    Dim XLSFileName As String
    Dim DefPath As String
    Dim Wb As Workbook
    Dim oApp As Object
    Dim oFolder
    Dim foldername

    'Create two temporary file names
    BatFileName = Environ("Temp") & "\CollectCSVData" & Format(Now, "dd-mm-yy-h-mm-ss") & ".bat"
    TXTFileName = Environ("Temp") & "\AllCSV" & Format(Now, "dd-mm-yy-h-mm-ss") & ".txt"

    ' Create path to xls file
    DefPath = Application.DefaultFilePath
    If Right(DefPath, 1) <> "\" Then
        DefPath = DefPath & "\"
    End If
    XLSFileName = DefPath & "MasterCSV " & Format(Now, "dd-mm-yy h-mm-ss") & ".xls"

    'Browse to the folder with CSV files
    Set oApp = CreateObject("Shell.Application")
    Set oFolder = oApp.BrowseForFolder(0, "Select folder with CSV files", 512)
    If Not oFolder Is Nothing Then
        foldername = oFolder.Self.Path
        If Right(foldername, 1) <> "\" Then
            foldername = foldername & "\"
        End If

        'Create the bat file
        Open BatFileName For Output As #1
        Print #1, "Copy " & Chr(34) & foldername & "*.csv" & Chr(34) & " " & TXTFileName
        Close #1

        'Run the Bat file to collect all data from the CSV files into a TXT file
        ShellAndWait BatFileName, 0

        If Dir(TXTFileName) = "" Then
            MsgBox "There are no csv files in this folder"
            Kill BatFileName
            Exit Sub
        End If

        'Open the TXT file in Excel
        Application.ScreenUpdating = False
        Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows, StartRow _
                :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
                ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, _
                Space:=False, Other:=False

        'Save text file as a XLS file
        Set Wb = ActiveWorkbook
        Application.DisplayAlerts = False
        Wb.SaveAs Filename:=XLSFileName, FileFormat:=xlWorkbookNormal
        Application.DisplayAlerts = True

        Wb.Close savechanges:=False
        MsgBox "You find the XLS file here: " & XLSFileName
        

        'Delete the bat and text file you have create
        Kill BatFileName
        Kill TXTFileName

        Application.ScreenUpdating = True
    End If
End Sub

‘ End code

 

This entry was posted on Wednesday, May 2nd, 2007 at 10:46 pm and is filed under Tech Stuff, Today a Client Asked.... You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.