garryv
asked on
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,,,
santosa2@abc.com,,Aida
Santos,,,,,,,,,,,,,,,,,,,,
nvoice,0.00,ANZ Bank - Aida Santos,,,,,,,,,,,,,,FRE,N,
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????
ABC Bank - Aida Santosi,,3623,N,,Level 9,20 Martin Place,,,Sydney,NSW,2000,,,
santosa2@abc.com,,Aida
Santos,,,,,,,,,,,,,,,,,,,,
nvoice,0.00,ANZ Bank - Aida Santos,,,,,,,,,,,,,,FRE,N,
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????
GRayL
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.
ASKER
what do you mean "do not use fixed length"? Where do I specify comma separated?, should I still use the macro output function?
ASKER
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?
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.
ASKER
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.
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.
jaffer
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.
jaffer
ASKER
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.
Thanks,
Garry
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.
Thanks,
Garry
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
Loop
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.
jaffer
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
Loop
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.
jaffer
ASKER
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.
jaffer
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.
jaffer
ASKER
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.
Thanks,
Garry
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.
Thanks,
Garry
> The query field is fine and shows the entire data correctly
Good
what about myText.txt (which is the file Access outputs), is it truncated?
jaffer
Good
what about myText.txt (which is the file Access outputs), is it truncated?
jaffer
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.
Don't worry about the file name and the field names, we can take them out.
ASKER
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,2
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,2
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?
Here is the output from the expression in the query:
Iplex Pipes,,3640,N,,Deliver to: 48 Camelot Court,,,,Carlingford,NSW,2
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,2
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.
jaffer
I will look at it, but please make a note of where exactly I should look.
jaffer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.