Exporting header record and data to csv file

[Mike Sweeting]

I need to create a data file for uploading into another
application using a csv file. In order for the receiving
application to upload the csv file there needs to be a
header record followed by the actual data. Needless to say
the header record has far fewer fields than the data
records.
I am at a loss how to create such a csv file. I know how
to create a csv file from a single query or table (with
the same number of fields) but not how to add a header
record at the start.
Can anyone help please?

 

[Nikos Yannacopoulos]

Mike,

"the header record has far fewer fields than the data records"... not making
sense. Did you mean something else?

You can do this through code, but if this is a one-time task, I guess it's a
lot easier to just export your table or query to Excel (which will get you
the headers there), and save as a .csv file from Excel.

HTH,
Nikos

 

[Mike Sweeting]

Thanks, Nikos

Unfortunately, this will be a weekly file upload, done by
someone else and therefore has to be automatic. The point
I was trying to make is that the first line of the csv
file (or header) has to contain 4 fields while the actual
data that follows contains many more. If I try and use the
table containing the data to hold the header data as well,
the resulting csv file has a first line with many unwanted
commas.

I thought I might be able to create a file with just the
header details and then to merge the data fields to it on
the subsequent rows. However, I cannot see how this can be
done.

I forgot to mention that I am using Access 2000, if it
makes any difference.

Many thanks

 

[Ted]

It's been a while since I have worked with reading and
writing to text files directly - but this would be an
option. Basically, you could (through code) either
create a new file and write the header info or open one
with the header info already there, then use the Write
statement to write the values that you want to export (by
stepping through a recordset) to the text file. If you
want to look into this, look in the table of contents of
VB Help and go under Visual Basic Conceptual Topics and
look for "Writing Data to Files". It would take a little
code writing, but would be fully automated for future
exports.

There may be a way to easily append one text file to
another (I would think so), which would actually solve
your problem much better, but unfortunately I'm not aware
of how to do it.

Of course, if this is a one-time thing, you should be
able to open your exported csv info in Notepad or Wordpad
or other word processor and just insert the line you need
and then save (as a text file, not as a Word Doc or other
special format).

Not sure if that will help, but wanted to pass it on just
in case.

-Ted

 

[John Nurick]

Hi Mike,

One way of doing this is:

1) create a textfile containing the file header. Do this either by
exporting a query that returns one four-field record
(DoCmd.TransferText) or by assembling the header in a string variable
and writing it to disk (e.g. with the function below). I'll assume
you've put the name and path in the variable strFileSpec.

2) export the data to another textfile in the normal way. This is a
temporary file, let's assume it's name and path are in strTempFile.

3) concatenate the two files with VBA statement like this:
Shell "COPY /A """ & strFileSpec & """ + """ & strTempFile _
& """ """ & strFileSpec & """"
This generates and executes a Windows command like
COPY /A "D:\Some Folder\My File.txt" + "D:\A Folder\Temp.txt"
"D:\Some Folder\My File.txt"

4) delete the temp file:
Kill strTempFile

 

[John Nurick]

Function to write a string to disk:

Function WriteToFile(Var As Variant, _
FileSpec As String, _
Optional Overwrite As Long = True) _
As Long
'Writes Var to a textfile as a string.
'Returns 0 if successful, an errorcode if not.

'Overwrite argument controls what happens
'if the target file already exists:
' -1 or True (default): overwrite it.
' 0 or False: append to it
' Any other value: abort.

Dim lngFN As Long

On Error GoTo Err_WriteToFile
lngFN = FreeFile()
'Change Output in next line to Append to
'append to existing file instead of overwriting
Select Case Overwrite
Case True
Open FileSpec For Output As #lngFN
Case False
Open FileSpec For Append As #lngFN
Case Else
If Len(Dir(FileSpec)) > 0 Then
Err.Raise 58 'File already exists
Else
Open FileSpec For Output As #lngFN
End If
End Select
Print #lngFN, CStr(Nz(Var, ""));
Close #lngFN
WriteToFile = 0
Exit Function
Err_WriteToFile:
WriteToFile = Err.Number
End Function

 

[Mike Sweeting]

Many thanks John and everyone else. I'll give it a try.

Mike