Delimited Text File Export | Newbie | | Join Date: Aug 2008
Posts: 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?
| | Moderator | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,576
| | | re: Delimited Text File Export
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
| | Newbie | | Join Date: Aug 2008
Posts: 4
| | | re: Delimited Text File Export
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?
| | Moderator | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,576
| | | re: Delimited Text File Export
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
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: Delimited Text File Export
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
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,419 network members.
|
