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 txt 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
