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 = &H400Public 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_ACTIVEEnd 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 IfEnd 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.