I have a program that exports data from a table to a comma deliminated text file. Now I need to add three more tables and export to text files in the same way but then combine them into one file separated by CR and LF's between each file's data section. I can make the separate files but am unsure of the code to combine them into one file. example file1 CRLF file2 CRLF file3 CRLF file4
How would I do this as an sql statement or is there an easier way?
Thanks for your help
the do.cmd method does not seem to work as you predicted.
I tried using the chr$10 and chr$13 but the problem that I was concerned about is that the field is exported as a line feed and carriage return but it is also comma deliminated. I am not sure if the extra commas are going to be a problem when importing data from this file.
I can see that using a query on this problem will not work due to how a query will structure the data and will append the tables one row at a time. I need the completed files attached at the end of each table with a CRLF between.
So it goes back to the question of a simple script to append them with CRLF in between.
something like append (VBCRLF & (file2)) to file 1 then repeat for files 3 and 4
I should be able to execue that as long as the complete path is in the name.
I'm not an expert on SQL so I don't know if an append command even exists.
When you export the union query to a text file as a CSV data you will get the CR LF at the end of each line by default. If you want some fine control over the export then you can use the following in Access.
You will need to add a reference to the scripting module first in VBA. In the VBA editor go Tools, References and select Microsoft Scripting Runtime (the file name is C:\Windows\System32\scrrun.dll and is part of Windows). You will need to read the Help details on the Microsoft MSDN web site for the full syntax
With this you can create a text file and export the data row by row formatting each row as you want. This will be a slow method if you have a large data set.
or maybe you could use this function to accomplish appending files
' Joins two or more files
' Call JoinFiles("File123.txt", vbNewLine, "File1.txt", "File2.txt", "File3.txt")
Public Sub JoinFiles(sFileNew As String, sSeparator As String, _
ParamArray sFiles() As Variant)
Dim Index As Long
Dim FileIn As Long
Dim FileOut As Long
Dim sBuf As String
' Delete output file if it exists
On Error Resume Next
' Create the new output file
FileOut = FreeFile
Open sFileNew For Binary Access Write As #FileOut
' For each file in the sFiles array...
For Index = LBound(sFiles) To UBound(sFiles)
' Open the input file
FileIn = FreeFile
Open sFiles(Index) For Binary Access Read As FileIn
' Create a string buffer to hold the entire file contents
sBuf = Space(FileLen(sFiles(Index)))
' Read the contents of the file to the string buffer
Get #FileIn, , sBuf
' Close the input file
' Write the string buffer to the output file
Put #FileOut, , sBuf
' If not the last file, write the separator string
If Index < UBound(sFiles) Then
' Check if sSeparator is at end of file
If Right$(sBuf, Len(sSeparator)) <> sSeparator Then
sBuf = sBuf & sSeparator