By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,387 Members | 1,709 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,387 IT Pros & Developers. It's quick & easy.

Delimited Text File Export

P: 4
I am exporting a text file from Access that will be fed into a mainframe application (third party - not something I manage). The file contains a header record, body - which is the variable number of records to be imported into the mainframe, and a trailer record.

Upon export, Access automatically places a hard return after the trailer record - creating a new line after the trailer - albeit with no data on that line.

How can I create the text export to stop at the end of the last record and not insert a hard return creating that last line? Or, is there a routine I can add after the export to modify the file to remove that last "emty" line?
Aug 27 '08 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 2.5K+
P: 2,545
Sorry, I know of no way to stop the hard return at the end of the last line. Its use after the last line is entirely conventional for such text files.

If it is causing you a problem all I can suggest is that you write a custom text file reader in VB or VBA to read each text file line and output them all again, except for the last line which can be written without an end-of-line character. I have not googled to see if such a utility is already available on the web somewhere.

Aug 28 '08 #2

P: 4
The last line (trailer) is always the number 9 - one digit, no return. Is there a routine I can write in VBA that will add the 9 without the return after the file is exported?
Aug 28 '08 #3

Expert Mod 2.5K+
P: 2,545
The VBA function below is very oldfashioned, but it works. It is used like this:

Expand|Select|Wrap|Line Numbers
  1. Call AddStopChar("C:Original FileName.txt", "C:Modified FileName.TXT)
Expand|Select|Wrap|Line Numbers
  1. Public Function AddStopChar(ByVal infilename As String, ByVal outfilename As String)
  2.     'Reads all lines from input file infilename then
  3.     'outputs the lines to output file outfilename
  4.     'and adds a single stop character to the end of the file
  5.     'without a newline character after
  6.     Dim strLine As String
  7.     Const stopchar = "9"
  8.     Open infilename For Input As #1
  9.     Open outfilename For Output As #2
  10.     Do While Not EOF(1)
  11.         Input #1, strLine
  12.         Print #2, strLine
  13.     Loop
  14.     Print #2, stopchar;
  15.     Close #1
  16.     Close #2
  17. End Function
There are other ways to concatenate files, but they tend to rely on shelling out to DOS and using the old Type command or the like to output all lines.

Aug 28 '08 #4

Expert 5K+
P: 8,677
My Reply is almost exactly the same as Stewart's, sorry Stewart, but I'll post it anyway for reference purposes:
Expand|Select|Wrap|Line Numbers
  1. Dim strLine As String
  3. '****************** Make your own substitutions here ******************
  4. Const conPATH_TO_EXPORTED_TXT_FILE As String = "C:\Test\Customers.txt"
  5. Const conNEW_FILE_NAME As String = "C:\Text Files\Demo\Customers_2.txt"
  6. '**********************************************************************
  8. Open conPATH_TO_EXPORTED_TXT_FILE For Input As #1
  9. Open conNEW_FILE_NAME For Output As #2
  11. Do While Not EOF(1)
  12.   Line Input #1, strLine
  13.   Print #2, strLine
  14. Loop
  15.   Print #2, "9"
  17. Close #1
  18. Close #2
Aug 28 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.