Delimited Text File Export

[grumpydadtl]

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?

[Stewart Ross]

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.

-Stewart

[grumpydadtl]

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?

[Stewart Ross]

The VBA function below is very oldfashioned, but it works. It is used like this:

Call AddStopChar("C:Original FileName.txt", "C:Modified FileName.TXT)

Public Function AddStopChar(ByVal infilename As String, ByVal outfilename As String)
    'Reads all lines from input file infilename then
    'outputs the lines to output file outfilename
    'and adds a single stop character to the end of the file
    'without a newline character after
    Dim strLine As String
    Const stopchar = "9"
    Open infilename For Input As #1
    Open outfilename For Output As #2
    Do While Not EOF(1)
        Input #1, strLine
        Print #2, strLine
    Loop
    Print #2, stopchar;
    Close #1
    Close #2
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.

-Stewart

[ADezii]

My Reply is almost exactly the same as Stewart's, sorry Stewart, but I'll post it anyway for reference purposes:

Dim strLine As String
 
'****************** Make your own substitutions here ******************
Const conPATH_TO_EXPORTED_TXT_FILE As String = "C:\Test\Customers.txt"
Const conNEW_FILE_NAME As String = "C:\Text Files\Demo\Customers_2.txt"
'**********************************************************************
 
Open conPATH_TO_EXPORTED_TXT_FILE For Input As #1
Open conNEW_FILE_NAME For Output As #2
 
Do While Not EOF(1)
  Line Input #1, strLine
  Print #2, strLine
Loop
  Print #2, "9"
 
Close #1
Close #2