Create ASCII interface file

[Guest]

I have an application that needs to create an interface file with each record
being a fixed length string 508 characters long, the end of which is lot of
hard spaces. I have been able to create this string (perfectly) as part of a
memo field that then needs to be exported to a txt file, which is to be used
as an interface and read by other software.

If I use an "Output to" macro, the following happens:

If the target is a report, because those features are tied to a pageset up
setting, paragraph returns are added that must be manually removed and all
trailing spaces are trimmed off.

If the target is a query, each record is defined by a bars and hyphens,
which must be removed manually.

I need a process that can reliably generate this file without any need for
the end user to do any manipultations. Can I manage this with a module?

My confession: I manage to understand VBA when I read it but seldom manage
to write it.

 

[John Nurick]

Hi Ken,

If you have a memo field that contains the strings you want to export,
create a query that returns just that one field, and use
DoCmd.TransferText to export the query.

The default settings will enclose the text in "quotes". To prevent that,
you will need to set up an export specification by exporting the query
manually, clicking the Advanced buton in the text export wizard and
using the resulting dialog. Once you have saved the specification you
can then pass its name to DoCmd.TransferText.

A couple of Howevers:

1) You say you have the 508-character string as "part of a memo field".
If that means you need to use an expression in the query to return only
a part of the contents of a memo field that contains other stuff as
well, you're stuck because of a quirk that means that if you use
formatting or other fnctions on the contents of a memo field when
exporting a query, the result is treated as a text field and truncated
at 255 characters.

2) You can set up and save an export specification that produces a
fixed-length record directly. This avoids the problem in (1), and may
also be simpler for you than assembling the 508-character string in a
memo field before trying to export it.

 

[Guest]

Thanks, John.

I need to export the entire memo field and not just a portion of it, so the
problem outlined in #1 of your reply will probably not present a problem.
I've had luck exporting the memo field as a delimited ascii text file and can
easily remove the delimiters but I need to send it without those as fixed
width records to avoid end user involvement in the interface file.

I'll keep working on it. I just haven't gotten the manual approach right
just yet.

Thanks again.

 

[John Nurick]

Just
1 create a query that returns only the memo field.

2 in the Database Window, select the query and then select Export from
the File menu.

3 Specify that you want to export to a text file.

4 In the first page of the text export wizard, select "Delimited".

5 In the next page, it doesn't matter what yuo select as the
"delimiter", because you're only exporting one field. But you need to
set the "text qualifier" to nothing and (probably) make sure "Include
field names on first row" is not checked.

6 Click the Advanced button, check the settings.

7 Click Save As, and save the settings as an import/export
specification.

8 Complete the export and check that the result is what you need.

You can then use the specification name from 7 in DoCmd.TransferText
when you are exporting under program control.