Convert to Text with Heading on the first line

  • Thread starter Arvin Villodres
  • Start date
A

Arvin Villodres

I have this table named tblTrans and I need to
transfer this into MS-DOS Text format.

I have used the DoCmd.TransferText command.

My table looks like this:
CODE | PAYCODE | AMOUNT |
001 001 235
001 001 111
001 001 213
001 001 212

My output should look like this

001111771
001001235
001001111
001001213
001001212

Where the first line should contain 111 as the group code
(found in another table named tblGrp) and 771 is
the total amount.

What do you think should I do with this?

I need your help. I would apprecaite whatever assistance
you would be able to extend. Thanks.
 
J

John Nurick

Hi Arvin,

There are basically two ways to go. One is to use a union query with a
dummy field to ensure that the group code goes in the first line; the
other is to use VBA and recordset operations to write the output file
line by line.

For the union query, start with a query that pulls the output you need
from tblTrans, adding a dummy "SortCode" field:

SELECT
CODE, PAYCODE, AMOUNT, 2 AS SortCode
FROM tblTrans
WHERE blah;

When that's working right, build a second query that pulls the values
you need for the first line of the file, again adding a dummy field for
sorting. At a guess it will be a totals query joining tblTrans and
tblGrp, grouped on CODE and on the dummy field
1 AS SortCode

Then combine the SQL of these two queries with the UNION operator and
add an ORDER BY clause:

SELECT
CODE, PAYCODE, AMOUNT, 2 AS SortCode
FROM tblTrans
UNION
SELECT
blah blah
FROM blah
GROUP BY blah
ORDER BY SortCode, Code, PayCode;

Finally, export the three fields you're interested in to a fixed-width
textfile.








I have this table named tblTrans and I need to
transfer this into MS-DOS Text format.

I have used the DoCmd.TransferText command.

My table looks like this:
CODE | PAYCODE | AMOUNT |
001 001 235
001 001 111
001 001 213
001 001 212

My output should look like this

001111771
001001235
001001111
001001213
001001212

Where the first line should contain 111 as the group code
(found in another table named tblGrp) and 771 is
the total amount.

What do you think should I do with this?

I need your help. I would apprecaite whatever assistance
you would be able to extend. Thanks.
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
A

Arvin Villodres

Here is what I did.

SELECT
tblCompanyInfo.BankCode, tblCompanyInfo.BAcctNo,
tblEmployeeInfo.ACCTNO, Format(([NETPAY] *
100), "000000000000000") AS AMOUNT,
Format([PAYDATE], "yyyymmdd") AS PD, 2 AS SortCode
FROM
tblCompanyInfo, tblPayrollTransactions
INNER JOIN
tblEmployeeInfo
ON
tblPayrollTransactions.EMPID = tblEmployeeInfo.EMPID
UNION SELECT
"001" AS SSortCode, 1171051776 AS SSSortCode,
tblPayrollTransactions.PAYCODE,
Format(Sum([NETPAY] * 100), "000000000000000") AS
SumOfNETPAY,
Format([PAYDATE], "yyyymmdd") AS PD, 1 AS SortCode
FROM
tblPayrollTransactions
GROUP BY
tblPayrollTransactions.PAYCODE,
tblPayrollTransactions.PAYDATE
ORDER BY SortCode;

And in the command button, on the OnClick event, I used
this command:

DoCmd.TransferText
acExportDelim, "", "unionqryAdvise", "D:\Advise.txt",
False, ""

and the output was this:

"001","5555555555","0000000001","000000015272892","20030806
",1
"001","1171051776","1171096899","000000000079845","20030806
",2
"001","1171051776","1171096991","000000000211743","20030806
",2

I need an output that excludes the commas and quotation
marks and without the value for the SortCode just like
this,

0015555555555000000000100000001527289220030806
0011171051776117109689900000000007984520030806
0011171051776117109699100000000021174320030806

At this point I'm already lost. I need your help badly.
Thanks for the reply and the suggestion. I could sure use
some of your suggestions right now.

Thanks very much.
-----Original Message-----
Hi Arvin,

There are basically two ways to go. One is to use a union query with a
dummy field to ensure that the group code goes in the first line; the
other is to use VBA and recordset operations to write the output file
line by line.

For the union query, start with a query that pulls the output you need
from tblTrans, adding a dummy "SortCode" field:

SELECT
CODE, PAYCODE, AMOUNT, 2 AS SortCode
FROM tblTrans
WHERE blah;

When that's working right, build a second query that pulls the values
you need for the first line of the file, again adding a dummy field for
sorting. At a guess it will be a totals query joining tblTrans and
tblGrp, grouped on CODE and on the dummy field
1 AS SortCode

Then combine the SQL of these two queries with the UNION operator and
add an ORDER BY clause:

SELECT
CODE, PAYCODE, AMOUNT, 2 AS SortCode
FROM tblTrans
UNION
SELECT
blah blah
FROM blah
GROUP BY blah
ORDER BY SortCode, Code, PayCode;

Finally, export the three fields you're interested in to a fixed-width
textfile.








I have this table named tblTrans and I need to
transfer this into MS-DOS Text format.

I have used the DoCmd.TransferText command.

My table looks like this:
CODE | PAYCODE | AMOUNT |
001 001 235
001 001 111
001 001 213
001 001 212

My output should look like this

001111771
001001235
001001111
001001213
001001212

Where the first line should contain 111 as the group code
(found in another table named tblGrp) and 771 is
the total amount.

What do you think should I do with this?

I need your help. I would apprecaite whatever assistance
you would be able to extend. Thanks.
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
J

John Nurick

I'm sorry, Arvin: I didn't get the instructions quite right.

Try this.
1) create another query based on the Union query to return the data in
the right order but without the sortcode, something like this:

SELECT BankCode, BAcctNo, AcctNo, AMOUNT, PD
FROM unionqryAdvise
ORDER BY sortcode;

2) export this query manually. In the export wizard, choose Fixed Width
text and click the Advanced... button to fine-tune the field widths.
Then save the setup as an export specification.

3) in the VBA, use something like
DoCmd.TransferText acExportFixed, "SpecName", _
"QueryName", "D:\Advise.txt" False, ""


Here is what I did.

SELECT
tblCompanyInfo.BankCode, tblCompanyInfo.BAcctNo,
tblEmployeeInfo.ACCTNO, Format(([NETPAY] *
100), "000000000000000") AS AMOUNT,
Format([PAYDATE], "yyyymmdd") AS PD, 2 AS SortCode
FROM
tblCompanyInfo, tblPayrollTransactions
INNER JOIN
tblEmployeeInfo
ON
tblPayrollTransactions.EMPID = tblEmployeeInfo.EMPID
UNION SELECT
"001" AS SSortCode, 1171051776 AS SSSortCode,
tblPayrollTransactions.PAYCODE,
Format(Sum([NETPAY] * 100), "000000000000000") AS
SumOfNETPAY,
Format([PAYDATE], "yyyymmdd") AS PD, 1 AS SortCode
FROM
tblPayrollTransactions
GROUP BY
tblPayrollTransactions.PAYCODE,
tblPayrollTransactions.PAYDATE
ORDER BY SortCode;

And in the command button, on the OnClick event, I used
this command:

DoCmd.TransferText
acExportDelim, "", "unionqryAdvise", "D:\Advise.txt",
False, ""

and the output was this:

"001","5555555555","0000000001","000000015272892","20030806
",1
"001","1171051776","1171096899","000000000079845","20030806
",2
"001","1171051776","1171096991","000000000211743","20030806
",2

I need an output that excludes the commas and quotation
marks and without the value for the SortCode just like
this,

0015555555555000000000100000001527289220030806
0011171051776117109689900000000007984520030806
0011171051776117109699100000000021174320030806

At this point I'm already lost. I need your help badly.
Thanks for the reply and the suggestion. I could sure use
some of your suggestions right now.

Thanks very much.
-----Original Message-----
Hi Arvin,

There are basically two ways to go. One is to use a union query with a
dummy field to ensure that the group code goes in the first line; the
other is to use VBA and recordset operations to write the output file
line by line.

For the union query, start with a query that pulls the output you need
from tblTrans, adding a dummy "SortCode" field:

SELECT
CODE, PAYCODE, AMOUNT, 2 AS SortCode
FROM tblTrans
WHERE blah;

When that's working right, build a second query that pulls the values
you need for the first line of the file, again adding a dummy field for
sorting. At a guess it will be a totals query joining tblTrans and
tblGrp, grouped on CODE and on the dummy field
1 AS SortCode

Then combine the SQL of these two queries with the UNION operator and
add an ORDER BY clause:

SELECT
CODE, PAYCODE, AMOUNT, 2 AS SortCode
FROM tblTrans
UNION
SELECT
blah blah
FROM blah
GROUP BY blah
ORDER BY SortCode, Code, PayCode;

Finally, export the three fields you're interested in to a fixed-width
textfile.








I have this table named tblTrans and I need to
transfer this into MS-DOS Text format.

I have used the DoCmd.TransferText command.

My table looks like this:
CODE | PAYCODE | AMOUNT |
001 001 235
001 001 111
001 001 213
001 001 212

My output should look like this

001111771
001001235
001001111
001001213
001001212

Where the first line should contain 111 as the group code
(found in another table named tblGrp) and 771 is
the total amount.

What do you think should I do with this?

I need your help. I would apprecaite whatever assistance
you would be able to extend. Thanks.
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
A

Arvin Villodres

Thank you very much sir. I followed your solution and
everything appeared exactly the way I planned it.

Hope you'll never get tired of people like me.

Thanks very much.

Arvin
-----Original Message-----
I'm sorry, Arvin: I didn't get the instructions quite right.

Try this.
1) create another query based on the Union query to return the data in
the right order but without the sortcode, something like this:

SELECT BankCode, BAcctNo, AcctNo, AMOUNT, PD
FROM unionqryAdvise
ORDER BY sortcode;

2) export this query manually. In the export wizard, choose Fixed Width
text and click the Advanced... button to fine-tune the field widths.
Then save the setup as an export specification.

3) in the VBA, use something like
DoCmd.TransferText acExportFixed, "SpecName", _
"QueryName", "D:\Advise.txt" False, ""


Here is what I did.

SELECT
tblCompanyInfo.BankCode, tblCompanyInfo.BAcctNo,
tblEmployeeInfo.ACCTNO, Format(([NETPAY] *
100), "000000000000000") AS AMOUNT,
Format([PAYDATE], "yyyymmdd") AS PD, 2 AS SortCode
FROM
tblCompanyInfo, tblPayrollTransactions
INNER JOIN
tblEmployeeInfo
ON
tblPayrollTransactions.EMPID = tblEmployeeInfo.EMPID
UNION SELECT
"001" AS SSortCode, 1171051776 AS SSSortCode,
tblPayrollTransactions.PAYCODE,
Format(Sum([NETPAY] * 100), "000000000000000") AS
SumOfNETPAY,
Format([PAYDATE], "yyyymmdd") AS PD, 1 AS SortCode
FROM
tblPayrollTransactions
GROUP BY
tblPayrollTransactions.PAYCODE,
tblPayrollTransactions.PAYDATE
ORDER BY SortCode;

And in the command button, on the OnClick event, I used
this command:

DoCmd.TransferText
acExportDelim, "", "unionqryAdvise", "D:\Advise.txt",
False, ""

and the output was this:

"001","5555555555","0000000001","000000015272892","200308 06
",1
"001","1171051776","1171096899","000000000079845","200308 06
",2
"001","1171051776","1171096991","000000000211743","200308 06
",2

I need an output that excludes the commas and quotation
marks and without the value for the SortCode just like
this,

0015555555555000000000100000001527289220030806
0011171051776117109689900000000007984520030806
0011171051776117109699100000000021174320030806

At this point I'm already lost. I need your help badly.
Thanks for the reply and the suggestion. I could sure use
some of your suggestions right now.

Thanks very much.
-----Original Message-----
Hi Arvin,

There are basically two ways to go. One is to use a
Click to expand...
union
query with a
dummy field to ensure that the group code goes in the first line; the
other is to use VBA and recordset operations to write
Click to expand...
the
output file
line by line.

For the union query, start with a query that pulls the output you need
from tblTrans, adding a dummy "SortCode" field:

SELECT
CODE, PAYCODE, AMOUNT, 2 AS SortCode
FROM tblTrans
WHERE blah;

When that's working right, build a second query that pulls the values
you need for the first line of the file, again adding a dummy field for
sorting. At a guess it will be a totals query joining tblTrans and
tblGrp, grouped on CODE and on the dummy field
1 AS SortCode

Then combine the SQL of these two queries with the
Click to expand...
UNION
operator and
add an ORDER BY clause:

SELECT
CODE, PAYCODE, AMOUNT, 2 AS SortCode
FROM tblTrans
UNION
SELECT
blah blah
FROM blah
GROUP BY blah
ORDER BY SortCode, Code, PayCode;

Finally, export the three fields you're interested in
Click to expand...
to
a fixed-width
textfile.








On Thu, 7 Aug 2003 18:34:32 -0700, "Arvin Villodres"

I have this table named tblTrans and I need to
transfer this into MS-DOS Text format.

I have used the DoCmd.TransferText command.

My table looks like this:
CODE | PAYCODE | AMOUNT |
001 001 235
001 001 111
001 001 213
001 001 212

My output should look like this

001111771
001001235
001001111
001001213
001001212

Where the first line should contain 111 as the group code
(found in another table named tblGrp) and 771 is
the total amount.

What do you think should I do with this?

I need your help. I would apprecaite whatever assistance
you would be able to extend. Thanks.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
J

John Nurick

Thank you very much sir. I followed your solution and
everything appeared exactly the way I planned it.
Click to expand...

Not at all - and thank you for the feedback.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert multiple lines of text record into table 3
Convert multiple line text to table - then to data file 3
Convert text to table macro from current selection 4
Help!! importing a text file into access with vba 0
Help!! importing a text file into access with vba 0
When printing, first 50 lines disappears if text longer than one p 0
Parsing multiple lines (or rows) of text 13
Text in last line on each page gets cut off 0

Top