mlcktmguy
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_Tri mmedSpaces ", _
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?
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
To create my output file I now use
DoCmd.TransferText acExportFixed, _
"WRBPayExportSpec_Trimmed"
"qryExportPaymentBatch_Tri
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!WhseUO M, 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.
2900 strExport = strExport & FSForEDI("0700", 4)
2910 strExport = strExport & FSForEDI(!TransId, 15)
2920 strExport = strExport & FSForEDI(rsLocation!WhseUO
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.
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.
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.
ASKER
Thank you, worked well.
For start the FreeFile is a good solution
Open in new window