Exporting fixed-width Files with Macros

[Betti via AccessMonster.com]

Good day,

I used two quires to create a header and detail record for a fixed-width file
and used two macros to export the data using the Transfer Text action with a
Export Specification file which in turn is creating two files instead of one
(One for the header and one for the detail record) . When I tried having two
actions writing to the same data file the second action over writes what the
first action wrote. Is there any way of writing a macro to create one file
to export the two quires using two specification files?

The following is what my one macro looks like:

Action: TransferText
Transfer Type: Export Fixed Width
Specification Name: QryFormatedHeader
Table Name: qryHeader
File Name: C:\datafileH.txt
Has Filed Name: No

Action: TransferText
Transfer Type: Export Fixed Width
Specification Name: QryFormatedDetail
Table Name: qryDetail
File Name: C:\datafileD.txt
Has Filed Name: No

I am using the following DOS command to merge the two data files and creating
one file and would like to create a macro to do that for me instead of having
to do it manually:

Copy datafileH.txt + datafileD.txt

Thank you for all your help in advance.

 

[Steve Schapel]

Betti,

Assuming the QryFormatedHeader and QryFormatedDetail queries have the
same number of fields, you could probably use a Union Query to combine
them together, and then use the Upion Query as the basis of your
TransferText export.

If this doesn't help, could you post back with examples of the queries
and the data they produce? Thanks.

 

[Betti via AccessMonster.com]

Steve,

Unfortunately, the columns are not the same that is why I couldn’t use Union
to do what I need to do.

The following is a simple of the data specs for both quires:

SELECT DISTINCT "H" AS FormattedRecType,
"INST" AS FormattedName,
"CIFPOST" AS FormattedFileID,
" " AS FormattedBlank1,
Date() AS FormattedDate,
"01" AS FormattedRevisionNo,
" " AS FormattedBlank2,
"0500" AS FormattedRecSize,
" " AS FormattedBlank3, "X" AS FormattedX
FROM CIFPST1;

Output:

HINST00CIFPOST 06090501 X



SELECT CIFPST1.RECTYPE AS FormattedRecType,
Format(Str([CIFPST1.BLANK1]),
"s""pa""c\e") AS FormattedBlank1,
Right(" " & [CIFPST1.ACCOUN],11) AS FormatedAcctNo,
Right(" " & [CIFPST1.STATEMENT_],4) AS FormattedStateCycle, (CIFPST1.NAME)
AS FormattedName,
(CIFPST1.CURR_MAILI) AS FormattedAddress1
FROM CIFPST1;

Output:

A 90000099 15SOMEONE 1111 SOMEWHERE

And I am using Export Specification file to get the exact width and start
position for each one of the files.

I hope this will help in answering my question.

Thanks,
Betti

 

[Steve Schapel]

Betti,

At this stage I am afraid I can't really understand what you are trying
to do. The first query contains 10 columns, and the second 6 columns.
It is not clear which columns relate to which other. If you don't want
the exported data in separate fields/columns, then you could just
concatenate them within your Access query into the one entity, I
suppose. Another idea to explore would be to make a table which is
structured according to how you want the exported data to hang together,
and then use an Append Query, similar to your existing queries perhaps,
to shuffle the required data temporarily into this table, and then base
your export on this table.

 

[Ken Snell [MVP]]

Or perhaps use a union query as suggested, but with the second query in the
union having four Null or empty string fields (calculated fields) as the
last four fields?

--

Ken Snell
<MS ACCESS MVP>

Betti,

At this stage I am afraid I can't really understand what you are trying to
do. The first query contains 10 columns, and the second 6 columns. It is
not clear which columns relate to which other. If you don't want the
exported data in separate fields/columns, then you could just concatenate
them within your Access query into the one entity, I suppose. Another
idea to explore would be to make a table which is structured according to
how you want the exported data to hang together, and then use an Append
Query, similar to your existing queries perhaps, to shuffle the required
data temporarily into this table, and then base your export on this table.

--
Steve Schapel, Microsoft Access MVP

Steve,

Unfortunately, the columns are not the same that is why I couldn’t use
Union
to do what I need to do.

The following is a simple of the data specs for both quires:

SELECT DISTINCT "H" AS FormattedRecType,
"INST" AS FormattedName,
"CIFPOST" AS FormattedFileID, " " AS FormattedBlank1, Date() AS
FormattedDate, "01" AS FormattedRevisionNo, " " AS FormattedBlank2,
"0500" AS FormattedRecSize, " " AS FormattedBlank3, "X" AS FormattedX
FROM CIFPST1;

Output:

HINST00CIFPOST 06090501 X



SELECT CIFPST1.RECTYPE AS FormattedRecType, Format(Str([CIFPST1.BLANK1]),
"s""pa""c\e") AS FormattedBlank1, Right(" " &
[CIFPST1.ACCOUN],11) AS FormatedAcctNo,
Right(" " & [CIFPST1.STATEMENT_],4) AS FormattedStateCycle,
(CIFPST1.NAME)
AS FormattedName,
(CIFPST1.CURR_MAILI) AS FormattedAddress1
FROM CIFPST1;

Output:

A 90000099 15SOMEONE 1111 SOMEWHERE

And I am using Export Specification file to get the exact width and start
position for each one of the files.

I hope this will help in answering my question.

Thanks,
Betti