Output to a text file

I have a query expression with the following example output string [exp1]:

ABC Bank - Aida Santosi,,3623,N,,Level 9,20 Martin Place,,,Sydney,NSW,2000,,,,,,
nvoice,0.00,ANZ Bank - Aida Santos,,,,,,,,,,,,,,FRE,N,,E

It is a lengthy field and is written in comma separated format to a text file using a Macro with the Ouput command. My output format is TXT and I use a Report as the output object. My report has a single line entry of exp1.

There are multiple records of [exp1] being written to the output text file.

My problem is that the output file is inaccurate. I have a fixed length Report field and the content of the record is variable, with a different number of characters being possible on different records. When I import into my other application, I am not getting the right match on fields separated by the commas.  I am getting some weird control characters in the importing application import window which I can't see in my text file. I am also getting my text lines truncated and also have blank lines imported. When I do a hand copy and paste directly from the field [exp1] on my query into the text file, I get perfect results, so I know the expression is constructed properly.

Is there a better way to output the contents of the query field [exp1] so I can get a variable length output, with no werid characters at the beginning of the file and no blank lines in the file????
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Do not use fixed Length, use Comma Separated and make sure the total record length for every record does not exceed 255 Characters for a Text field, else use Memo.
garryvAuthor Commented:
what do you mean "do not use fixed length"? Where do I specify comma separated?, should I still use the macro output function?
garryvAuthor Commented:
Also, where do I set this as a Memo field. The pulldown for format on the [exp1] field in my report has no selectable options. How would I set this up as being a memo field?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

I'm using A2K. Are you ouputting from a table to a textfile?  If so, you can select the output format from the macro wizard.  If you are inputting from a text file, in the macro wizard, change the Transfer Type from Fixed Width to Import Delimited in the subpane.
garryvAuthor Commented:
I am using A2K. This is an EXPORT function I require - not import.
the field I want to export to a text file is constructed in a query. It is not from a table.
As I have mentioned, I have tried to export the field via a report. Is there a way to do this from the query directly? I have constructed the comma delimitation directly within the query expression. This means that the contents of the query expression is completely written out as 1 line to the output file with no need for delimitation being inserted by the export process.
What GRayL is saying, in the Macro, there are functions to Export to a text file.

The following are VBA functions which could be placed "On Click" of button on a Form, you can use either:

DoCmd.OutputTo acOutputQuery, "Your Query Name", acFormatTXT, "c:\myText.txt", False
DoCmd.TransferText acExportDelim, , "Your Query Name", "c:\myText.txt"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Your Query Name", "c\myText.cvs"

a code could be used too, but any of the above will do the job for you.

garryvAuthor Commented:
Option 1 above is no good as the field names come through in the text file.
Option 3 is no good as I do not want a CVS file, I need a text file that I create the delimiters for.

Option 2 works almost perfectly except for 2 things:

1. Each output line has a quote mark "  at the beginning and end of each record output. How do I get rid of these as they screw up the data integrity and make the export unusable.

2. Every 10 or so lines, I get a blank line inserted. I;ve seen this before and don't know how to get rid of it.

Any ideas on how to fix this, otherwise I am back to the drawing board with another approach.


Hi Garry

ok, here is the whole thing, all on "On Click" of Command button command0:

Private Sub Command0_Click()
Dim TextLine

DoCmd.TransferText acExportDelim, , "Your Query Name", "c:\myText.txt"

Open "c:\myText.txt" For Input As #1        ' Open Input file.
Open "c:\myTextNew.txt" For Output As #2    ' Open Output file.

Do While Not EOF(1)             ' Loop until end of Input file.
    Line Input #1, TextLine     ' Read Input line into variable.
    TextLine = Replace(TextLine, Chr(34), "")  'remove the " from the line
    If Len(TextLine) <> 0 Then 'skip the empty lines
        Print #2, TextLine           'write this line in the Output file
    End If        

Close #1    ' Close Input file.
Close #2    ' Close the Output file.

End Sub

So Access will produce myText.txt which contains your unwanted quotes and empty lines,
then the rest of the code will open this file and produce myTextNew.txt which is you final clean usable file.

garryvAuthor Commented:
I'm getting truncation at 255 characters. Is it as simple as setting up the Dim Textline as a memo field?
Hi Garry

I don't think the problem is from the text file,
I think Access truncated your values.

You can either check your Access query, or myText.txt (which is the file Access outputs), and see where the truncation is taking place.
I assume, in your query you are either using "menu bar > view > totals" which will truncate the memo field to 255 characters, OR you are using a Textbox field with 255 characters.

If that is the problem, in the query then you can aligne the fields for output, then we can do all sort of manipulations with the text file.

The code will read the full line no matter how long.

garryvAuthor Commented:
Hi Jaffer,

You have been very helpful to-date - my greatest thanks!

The query field is fine and shows the entire data correctly. The truncation occurs in the .txt file. I don't want to have multiple fields if I don't have to. Is there a way to set a field size that will allow > 255 characters? I can't see how to set this in the query. It does not give me an option of a memo field.


> The query field is fine and shows the entire data correctly

what about myText.txt (which is the file Access outputs), is it truncated?

what about option 1 & 3, are the output truncated?
Don't worry about the file name and the field names, we can take them out.

garryvAuthor Commented:
This is more than truncation. I am getting some weird stuff at the end of each line, but it is not the same for each record.

Here is the output from the expression in the query:

Iplex Pipes,,3640,N,,Deliver to: 48 Camelot Court,,,,Carlingford,NSW,2118,,,,,, gm.neath@iplexpipelines.com.au,,Glenda Neath,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Casual,Sharon Murray,,,,,0,2,0,7,0,0,FRE,0,,0,N,0,Invoice,0.00,Iplex Pipes,,,,,,,,,,,,,,FRE,N,,E

Here is the same output from the .txt file after using the above VBA code:

Iplex Pipes,,3640,N,,Deliver to: 48 Camelot Court,,,,Carlingford,NSW,2118,,,,,, gm.neath@iplexpipelines.com.au,,Glenda Neath,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Casual,Sharon Murray,,,,,0,2,0,7,0,0,FRE,0,,0,N,0,Invoice,0.00

It's definitely hitting some 255 character maximum somewhere.

I tried the other VBA functions also presented and these did not work at all. I think this approach will work aslong as I can get over the truncation error.

Any more ideas?
if you can zip your mdb and post it somewhere where I can download (www.yousendit.com is an option) then post the link here,
I will look at it, but please make a note of where exactly I should look.


I was right, the query did truncate the values,
use the original query which have all the individual fields (columns), instead of putting all the fields in one column,

try this line, with the rest of the code

DoCmd.TransferText acExportDelim, , "MYOBCustomerExportDetail", "c:\myText.txt"
                                                        ^^^^^^^^^^^^^^^^^ original query name


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.