Solved Combine csv files into 1 new file run from VBA?
- Thread starter Number11
- Start date
was looking for a better way as the bat file leaves " " at the end of the file or is there a way to have the 3 files created at the same time its 3 queries
Last edited:
Thanks do you have any sample code for this method?
A text file is a simple text file. You can attach additional text to text. Simple VBA, without occupancy of a temporary table:
A loop at known paths then also makes sense.
Code:
Append2TextFile ReadFile(FullPathCSV1), FullPathNewFile
Append2TextFile ReadFile(FullPathCSV2), FullPathNewFile
'Append2TextFile Split(ReadFile(FullPathCSV2), vbCrLf, 2)(1), FullPathNewFile 'without headline
Append2TextFile ReadFile(FullPathCSV3), FullPathNewFile
'Append2TextFile Split(ReadFile(FullPathCSV3), vbCrLf, 2)(1), FullPathNewFile 'without headline
Code:
Public Sub Append2TextFile(ByVal Content As String, Optional ByVal TextFile As Variant)
Dim FF As Long
If IsMissing(TextFile) Then TextFile = CurrentProject.Path & "\Log.txt"
FF = FreeFile()
Open TextFile For Append As FF
Print #FF, Content
Close #FF
End SubVB-Tec / Daten / Dateien / Einlesen - Beliebige Datei einlesen
Beliebige Datei einlesen... Visual Basic-Technik, Beratung, Support
vb-tec.de
Thanks
Thanks so i did this and getting errors..
Dim Append2TextFile
Dim Readfile
Append2TextFile Readfile("C:\Database\Zip\1_Header_Q.CSV"), FullPathNewFile
Append2TextFile Readfile("C:\Database\Zip\2_Records_Q.CSV"), FullPathNewFile
'Append2TextFile Split(ReadFile(FullPathCSV2), vbCrLf, 2)(1), FullPathNewFile 'without headline
Append2TextFile Readfile("C:\Database\Zip\3_Footer_Q.CSV"), FullPathNewFile
'Append2TextFile Split(ReadFile(FullPathCSV3), vbCrLf, 2)(1), FullPathNewFile 'without headline
Append2TextFile is a procedure that I have shown and that you have to copy into your code module. Only then it is defined for your VBA project.
For ReadFile the same applies. Also here you have to transfer the function into your code. Probably you have to replace the function argument ByRef by ByVal.
For ReadFile the same applies. Also here you have to transfer the function into your code. Probably you have to replace the function argument ByRef by ByVal.
Ok thanks I will see what i can find online as i am totally confused by this method thanks for your time though
dont worry i am using this medthod nw..
Option Explicit
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
' define csv files that will be read
Dim arrCSVFiles : arrCSVFiles = Array("c:\temp\csv_file1.csv", _
"c:\temp\csv_file2.csv", _
"c:\temp\csv_file3.csv")
' define combined csv file
Dim outCSVFile : outCSVFile = "c:\temp\combined.csv"
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
' open output csv file
Dim objOutFile : Set objOutFile = objFSO.OpenTextFile(outCSVFile, ForWriting, True)
Dim csvFile, objFile, strTemp
' loop through the list of csv files to read
For Each csvFile In arrCSVFiles
' open file for reading
Set objFile = objFSO.OpenTextFile(csvFile, ForReading)
Do Until objFile.AtEndOfStream
strTemp = objFile.ReadLine
If Not strTemp = "" Then
' write line to combined csv file
objOutFile.WriteLine strTemp
End If
Loop
' close current opened csv file
objFile.Close
Next
' close combined csv file
objOutFile.Close
Option Explicit
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
' define csv files that will be read
Dim arrCSVFiles : arrCSVFiles = Array("c:\temp\csv_file1.csv", _
"c:\temp\csv_file2.csv", _
"c:\temp\csv_file3.csv")
' define combined csv file
Dim outCSVFile : outCSVFile = "c:\temp\combined.csv"
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
' open output csv file
Dim objOutFile : Set objOutFile = objFSO.OpenTextFile(outCSVFile, ForWriting, True)
Dim csvFile, objFile, strTemp
' loop through the list of csv files to read
For Each csvFile In arrCSVFiles
' open file for reading
Set objFile = objFSO.OpenTextFile(csvFile, ForReading)
Do Until objFile.AtEndOfStream
strTemp = objFile.ReadLine
If Not strTemp = "" Then
' write line to combined csv file
objOutFile.WriteLine strTemp
End If
Loop
' close current opened csv file
objFile.Close
Next
' close combined csv file
objOutFile.Close
