Appending to an Exported Fixed Width File in Access

  • Thread starter Thread starter David Layten
  • Start date Start date
D

David Layten

I am creating a file that the ADP Unemployment Group,
UCeXpress Format will use. The data is coming from my
accounting system where I will use linked tables and
queries to format the dat per their specifications.

My question is related to appending a fixed width test
file. The ADP specification has different record types
that need to be in one fixed width file. The file has
different record types, Header, Detail, etc.... The width
of the file will be the same for all records but position
and type of data will be different for each record type.

I wanted to create a macro that would use the
TransferText function to create the Header and then use
the same function to append to that file with the
different record types to "build" the file. Is this
possible or am I using the wrong tool for this?

Any help will be appreciated!
 
Hi David,

A textfile with all lines the same width but a different field structure
in different kinds of line? I think there are two ways to go.

1) VBA code to create a new text file, assemble each line of data in
turn and write it to the file. This gives you full control over what's
going on.

2) It may be possible to take advantage of the fact that the output file
can also be seen as a n ordinary fixed width file (i.e. with all lines
having the same structure) that only contains one field. You can create
queries that return each kind of line as a single fixed width field
(instead of
SELECT Field1, Field2...
use something like
SELECT Left([Field1] & " ", 10) &
Left([Field2] & " ", 10) & ... AS TheSingleField...
) and then use a UNION query to combine them all. The tricky bit is
generating additional fields in the queries that can be used to sort the
output of the UNION query into the order needed for the output file.
 
You will probably need 2 or more recordsets (depends on how you want to
segregate your data.)

Here is one way to export data to a fixed width file without the wizard:

Some advantages include easy updating of a spec change, easy exporting of
leading zeroes (notoriously difficult by the way) and a simple trick for
right justifying data.


Sub ExportData(strExportFile As String)
'strExportFile is the full path and name where you would like to create
and save the output file
Dim rs As Recordset
Dim strData As String
Dim intFileNum As Integer

'get file handle and open for output
intFileNum = FreeFile()

'opens the disk file
Open strExportFile For Output As #intFileNum

'open the recordset
Set rs = CurrentDb.OpenRecordset("MyTableOrQueryName", dbOpenSnapshot)
'the numbered comments show the fixed width positions
With rs
Do Until .EOF
'this will pad the end of the field with spaces
strData = ![key] & Space(11 - Len(![key])) '1-11
strData = strData & ![TransType] '12

'this is how to right justify an entry
strData = strData & Space(14 - Len(Format(![Qty], "0.0000"))) &
Format(![QtyRcv], "0.0000") '13-26

strData = strData & Format(![TransDate], "mm/dd/yyyy") '27-36
strData = strData & Format(![Date1], "mm/dd/yyyy") '37-46
strData = strData & Format(![Date2], "mm/dd/yyyy") '47-56
strData = strData & Format(![Date3], "mm/dd/yyyy") '57-66
strData = strData & Format(![Date4], "mm/dd/yyyy") '67-76
strData = strData & ![Num] & Space(10 - Len(![Num])) '77-86
strData = strData & ![Status] '87
strData = strData & ![Reason] '88

'write out to file
Print #intFileNum, strData
.MoveNext
Loop
End With

Close #intFileNum
rs.Close
Set rs = Nothing
MsgBox (strExportFile & " has been created.")
End Sub
 
Back
Top