Exporting and combining two ASCII files

[Ken Ivins]

I have a database with a table for Groups and one for Subscribers in those
groups (one to many). I need to create 2 ASCII files the merge them to email
to another location.

The first table is fixed width with my sender ID in it followed by a
continuous string of up to 30 GroupIDs (9 character ID numbers). Then
multiple lines of GroupIDs (34/line) until all the GroupIds are listed. The
table is has a total of 5 basic fields and one long GroupID field for a
total of 351 characters.

The second fixed width table has subscriber information in it. It has about
50 fields of various lengths for a total of 351 characters.

My final export must combine these two tables. The first table at the top
then the information of the second table.

I am comfortable in sending this by email through code. I am also fine in
creating the second table.

I am not sure how to create the second table that automatically wraps the
Group IDs. I assume I start with a pivot table to get all the GroupIDs but
where to go from there is my problem.

Lastly how to combine these two into one ASCII file really is beyond my
knowledge.

This set of newsgroups have been very helpful in the past and I really
appreciate your time and effort. I hope some day to learn enough to repay
my dept by helping others as well.

Thanks,
Ken

 

[Joe Fallon]

My first thought would be to open up the appropriate recordsets in code and
then output themin the structure you require all in one shot.

Here is some generic sample code to get you started:

Here is a sample of some general export code:

Public Sub ExportDelim(strTable As String, strExportFile As String,
strDelimiter As String, Optional blnHeader As Boolean)

'strTable is the table or query name
'strExportFile is the full path and name of file to export to
'strDelimiter is the field deliminator to use like Chr(9) for tab or
Chr(44) for comma or ??

Dim fld As Field
Dim varData As Variant
Dim rs As Recordset
Dim intFileNum As Integer

'set recordset on table or query
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

'get file handle and open for output
intFileNum = FreeFile()
Open strExportFile For Output As #intFileNum

If blnHeader Then
'output the header row if requested
varData = ""
For Each fld In rs.Fields 'traverse the fields collection
varData = varData & fld.Name & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out the header row
Print #intFileNum, varData
End If

'now your data
Do While Not rs.EOF
varData = ""
'concatenate the data row
For Each fld In rs.Fields
varData = varData & fld.Value & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out data row
Print #intFileNum, varData

rs.MoveNext
Loop

Close #intFileNum
rs.Close
Set rs = Nothing
End Sub

 

[Ken Ivins]

Joe,

Thanks. I all of a sudden went crazy. So I am just checking this. I'll look
it over and get back to you with any questions. It look good though. I was
at a loss here and appreciate your help.

Ken Ivins