Creating a Text File Output

[Guest]

I designed a query and I want Access to export the results to a text file
automatically. The only restriction with this is that I only want each column
of information from the query to take up a certain specific number of
character spaces when it goes in the text file. (ex: operator names, no
matter how long, can only take up the first 10 character spaces of each text
file row). Also, the text file needs to make certain character spaces blank
in order to distinguish information from the different query columns.

 

[Guest]

Hi Cloudbuster,

Check out the use of the Left function for limiting the number of
characters. For example:

Customer Name: Left([CustomerName], 10)

Notice that I have used a different alias, in that "Customer Name" is not
the same as "CustomerName". You will get a circular reference error if you
try to specify the same name.

Also, the text file needs to make certain character spaces blank
in order to distinguish information from the different query columns.

You might want to consider exporting as comma separated variable (*.csv),
instead of trying to rely on white spaces to separate columns.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[George Nicholson]

Start by looking at the entry for TransferText method in VB help. The
SpecificationName argument is the key here.

You'll need to create a Specification manually first, then you can use the
saved Spec for the TransferText argument. Create the spec by using the
Export wizard:
- Select the query
- File>Export
- Specify a name & location for the text file
- the wizard should open (if not, it may not have been installed)
- you want Fixed Length, not delimited
- Use the Advanced button to specify column widths, etc.
- Use the Advanced button & "SaveAs" to give the Spec a name & to save it
within the db
- Use the Advanced button & "Specs" to re-load an existing spec for
modification (remember to save it again)

Not exactly sure what you mean by the "the text file needs to make certain
character spaces blank" requirement. If this doesn't get you the results you
need, let us know.

HTH,

 

[Guest]

Sorry for the ambiguity on the question. I will use an example of what I need.

Each piece of data in every row can only take up a certain number of spaces
in that row. (Ex: AJONES 53 EX7 6420)

In this example, AJONES is the person's name, and it can only occupy the
first 10 spaces of the data row. If the name is less than 10 letters, the
remaining spaces need to be blank. The 11th space in the row (right before
the "53" in the example) must ALWAYS be blank, before starting the next piece
of data. The reason for these restrictions is I need every row of data to be
uniform, so that it looks like this:

AJONES 53 EX7 6420
EMEIL 43 EX6 5420

In both rows, the name field is <=10 spaces long with the 11th space always
being blank, before starting the next piece of data, which is always <=2
spaces. If it's less than 2, I want blank spaces in the unused field space,
then another mandatory blank space, before starting the next piece of data,
etc. Let me know if that makes more sense now...

 

[Guest]

Hi Cloudbuster,

Did you try George's suggestion? The Fixed-length that he mentioned seems to
describe your needs. You might need to combine this with the use of the Left
function, as I described.

Let us know if the results you get are not satisfactory, and if so, exactly
what steps you took.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________