Merge all CSV or TXT files in a folder in one worksheet
Example 1
Merge all data from the csv files in a folder into a text file
Note: with a few small changes you can also use this for txt files. Replace *.csv for *.txt
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 how to do that 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.
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
There is no need to change anything in the code example for csv files to test it.
But read the Tips below the macro if you not get the result you want.
Option Explicit
#If VBA7 Then
Private Declare PtrSafe Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long
#Else
Private Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long
#End If
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 FileExtStr As String
Dim FileFormatNum As Long
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"
'Folder where you want to save the Excel file
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If
'Set the extension and file format
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007 or higher
FileExtStr = ".xlsx": FileFormatNum = 51
'If you want to save as xls(97-2003 format) in 2007 use
'FileExtStr = ".xls": FileFormatNum = 56
End If
'Name of the Excel file with a date/time stamp
XLSFileName = DefPath & "MasterCSV " & _
Format(Now, "dd-mmm-yyyy h-mm-ss") & FileExtStr
'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 Excel file
Set Wb = ActiveWorkbook
Application.DisplayAlerts = False
Wb.SaveAs Filename:=XLSFileName, FileFormat:=FileFormatNum
Application.DisplayAlerts = True
Wb.Close savechanges:=False
MsgBox "You find the Excel file here: " & vbNewLine & XLSFileName
'Delete the bat and text file you temporary used
Kill BatFileName
Kill TXTFileName
Application.ScreenUpdating = True
End If
End Sub
Format or skip columns
If you want to format or skip a columns then you see you can add FieldInfo in OpenText like
FieldInfo:=Array(Array(1, 2), Array(3, 4))
This example change the format of column 1 and 3 (column number, format number)
This are the format numbers
1: General
2: Text
3: Month-Day-Year
4: Day-Month-Year
5: Year-Month-Day
6: Month-Year-Day
7: Day-Year-Month
8: Year-Day-Month
9: Skip column
Merge txt files instead of csv files
Replace
Print #1, "Copy " & Chr(34) & foldername & "*.csv" _
With
Print #1, "Copy " & Chr(34) & foldername & "*.txt" _
If you use it for txt files then you can change the delimiter or maybe you want to use FixedWidth.
The best thing you can do is to record a macro when you import one txt file manual.
Then look at the recorded code and add the code lines to Workbooks.OpenText .
Merge CSV Files Into One Large CSV File In Windows 7
If you regularly download CSV files from a database, or receive a number of CSV files containing similar information, you might need to combine all of those files into one larger file. The ability to merge CSV files automatically can be a big time and sanity saver, almost as much as
setting a print area in Excel to fix a spreadsheet that isn’t printing well. I recently encountered a situation where I had a large amount of data that was split up into about 100 different CSV files, each of which contained the same number of rows with the same type of data in each row. Each CSV file represented an order from a company, and my company needed to be able to quickly sort all of that data into one file. The combined data could then be organized into a pivot table so that our production team knew how much of each product they needed to make. Your reasons for doing this may vary, but it can be the simplest solution if you need to combine and sort a lot of data. Rather than opening each file individually, then copying and pasting all of the data into one file, you can automate the process with the command prompt. Having witnessed someone manually copy and paste all of the data from multiple CSV files into one CSV file, I know that the ability to merge CSV files is one that can be a huge time saver.
Step 1: Move all of the CSV files into one folder. This file does not need to be on your Desktop, but I typically put mine there for the sake of simplicity. Take note of the location, however, as you will need to reference it later. Additionally, once you have finished the merge CSV files process, the output CSV file will also be located in this same folder.
Step 2: Click the Start button, click All Programs, click the Accessories folder, then right-click the Command Prompt option and choose Run as Administrator. You can also type “cmd” into the search field at the bottom of the Start menu, which will bring up the command prompt as a search result. You can then right-click the Command Prompt search result, then click Run as Administrator.
Step 3: Type “cd,” followed by a space, then followed by the folder location, on the first line, then press “Enter.” If you look at the image below, you will see that my folder is called “csv files” and is located on the Desktop of a user that I have called “Demo.” You can also find the folder location by right-clicking the folder, then clicking Properties.
Step 4: Type copy *.csv all-groups.csv on the next line, then press Enter on your keyboard. You can change the output file name from “all-groups.csv” to whatever file name you wish. The name that you select in this step is going to be the name of the large file containing all of your merged CSV files.
Step 5: Open the folder to see the new file that you just created.
Step 6: Double-click the file to see all of the combined information. In the image below, note that I’ve specified in the third column which file originally contained that line of data. When you merge CSV files, the resulting output file is going to have all of the information organized in this manner, where the data from one file is added after all of the data that has been previously added.
How to combine multiple CSV files into one using CMD
- EmailThis is a trick which can save you a lot of time when working with a dataset spread across multiple CSV files. Using a simple CMD command it is possible to combine all the CSV’s into a single entity ready for all your pivot and table wizardry.
Step 1
Save all of the CSV files into a single folder. Make sure that the folder is free from any CSV’s you do not want included in the compression.
Step 2
Navigate to “Run” from the Windows Start Menu
- On XP this is located in the Start Menu itself
- In Vista / Win 7 you may have to navigate first to “Accessories” to find the Run executable.
Type “cmd” into the executable field and hit Enter.
Step 3
When the CMD window opens; you will be presented with your default document folder housing all of your personal files. From here you need to execute the “cd” command which navigates to your desired folder. Type “cd” after the chevron followed by a space then your desired folder location including the drive architecture. For example if your cmd window opens with “C:\Users\Your Name>” pre-populated, the complete command line would read: “C:\Users\Your Name>cd C:Desired Folder”.
Top Tip – if you navigate to your desired folder in Windows Explorer (e.g. clicking My Documents from the Desktop) you can see the folder location path in the top of the window. However, don’t close it – you cannot copy & paste into CMD so you will need to type the folder path out!
Step 4
Once you have entered the command line and hit Enter, the desired folder location will appear on the subsequent line. Now you need to use the “copy” function to merge all the CSV files together. In a similar fashion to the “cd” command, type “copy” after the chevron, followed by a space then “combine.csv”. This copies the data from all CSV files in that location into a single file called ‘combine.csv’.
Top Tip: Because the CSV file format cannot support multiple tabs all of your data will be copied into one worksheet within the CSV workbook. For this reason it may be worth collating all your data in a similar structure – so as to avoid large amounts of formatting work at the end.
Step 5
Once executed you will be presented with confirmation; outlining which files have been copied into a single entity. Navigate to your destination folder and enjoy the fruits of your CSV-based labour in combine.csv!
0 коммент.:
Отправить комментарий