asked on

Creating a txt file with varying length records

I need to create a txt file for a client from my Access 2013 application. The file will contain a header record of length 21, 1 or more detail records of length 36 and a trailer record of length 26.

I have all of the logic in place to create the records and I am currently storing them, in the order created (hdr, detail....., trailer), in a local table (tblExportBatch_Work_Local )with one field 'ExportText' of length 36.

To create my output file I now use

DoCmd.TransferText acExportFixed, _
"WRBPayExportSpec_Trimmed", _
"qryExportPaymentBatch_TrimmedSpaces", _
wkOutputFileName, _
False

'wkOutputFileName' is the name I want to assign to the created output file.

It works but the client does not want spaces at the end of each header and trailer record. As it stands now all records in the output file are 36 characters in length.

Is there any way to accomplish outputting the record without spaces after the header and trailer using the transfertext command?

Perhaps I have to change my methodology. I've seen but never used other methods of creating a text file using something like

fHandle = FreeFile
Open Output_File For Append As #fHandle
Print #fHandle, wkHeaderRec

Print #fHandle, wkDetailRec (the detail write is in a loop)

Print #fHandle, wkTrailerRec
Close #fHandle

Will this new method create the records in the output file with only the length of the field being written?

How do I assign the name stored in 'wkOutputFileName' to the output file that is created?

Will this method fail if the named output file already exists?

Do I need to put ctrl feeds, vbCrtlf, at the end of each record?

John Tsioumpris

We need a some sample from the data your query is outputting..
For start the FreeFile is a good solution

Dim rst as DAO.Recordset
Set rst = CurrentDB.OpenRecordset("YourQuery")
    Open "wkOutputFileName" For Output As #1    ' Create file name.
With rst
While Not .EOF
    Print #1, Trim(.Fields(0)) & "," & .Fields(1)    ' Outputting the data from field 0 & 1 ...manipulate  to your liking
.MoveNext
Wend

End With
rst.Close
Set rst = Nothing
    Close #1' Close file.

Open in new window

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Dettman (EE MVE)

Note to that you can build it up in a string and then write it out all at once. i.e:

2900 strExport = strExport & FSForEDI("0700", 4)
2910 strExport = strExport & FSForEDI(!TransId, 15)
2920 strExport = strExport & FSForEDI(rsLocation!WhseUOM, 2)
2930 strExport = strExport & FSForEDI(dblTotOrdQty, 8)
2940 strExport = strExport & FSForEDI(intTotLines, 6)

2950 intFileNum = FreeFile
2960 Open strFileName For Append As #intFileNum
2970 Print #intFileNum, strExport
2980 Close #intFileNum

Jim.

Jim Dettman (EE MVE)

BTW, FSForEDI here does two things:

1. Takes out illegal characters
2. Sets the string to a specific length.

so records are built up field by field and each can be a different length.

Jim.

mlcktmguy

ASKER

Thank you, worked well.