exporting tables from Access to text files

[Guest]

I am having trouble exporting a table from Access into text. The problem is
that I have a text field in my table (although it contains numerics only)
that gets exported as currency. So for example if I have the value
'0000000345' in my table, when I export it, the result is $345.00! I am using
an export file specification.

 

[Ken Snell [MVP]]

Tell us what is in your export specification. Are you exporting into an
existing EXCEL file, or is ACCESS creating an EXCEL file for you?

 

[Guest]

I am exporting to a text file, not Excel.
My process is this - I have a make-table query that creates the table that I
want to export - the resulting table is all text fields. In that query I am
formatting a field that is initially a double (2 decimal places) into a
string so that I can zero-fill and right justify. The table when I look at in
Access has the correct format, with the leading zeros.

I did change something since my original post: Now the exported text file
has the amounts without leading zeros and not right-justified.

 

[Ken Snell [MVP]]

You're right... you did say "text file"... sorry, too little sleep in past
four days.

Post the SQL of the query that you're exporting. Also post the exact
specification information for the particular field that is giving you a
problem.

--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

SELECT "C" AS [Record Type], "123" AS [Transaction Code], "6666660000" AS
[Client Number], " " AS Filler, [combined penalty payments].RefNbr AS
[Customer Number], "99" AS [Payment Number], [combined penalty
payments].BankTransit, [combined penalty payments].PayeesAcctNbr, " " AS
Filler2, String(10-Len([sumofamount]*100),"0") & [sumofamount]*100 AS
PaymentAmount, ' ' AS Reserved, "2005235" AS [Payment Date], [combined
penalty payments].PayeeName, "E" AS [Language Code], " " AS Reserved2, "TEST
- COMINC " AS [Client Short Name], "USD" AS [Currency], " " AS Reserved3,
"USA" AS Country, " " AS Filler3, " " AS Reserved4, "N" AS [Optional
RecInd] INTO [EFT credit records]
FROM [combined penalty payments];

The problem field is the 'Payment Amount' field which I am just trying to
export as text in positions 71-80. That is the only specific information that
I can think of with regards to the export. The leading zeros that are there
when I look at the table in Access do not come through in the text file.

 

[Ken Snell [MVP]]

You need to explicitly format the output of that field if you want it to be
"text":

SELECT "C" AS [Record Type], "123" AS [Transaction Code], "6666660000" AS
[Client Number], " " AS Filler, [combined penalty payments].RefNbr AS
[Customer Number], "99" AS [Payment Number], [combined penalty
payments].BankTransit, [combined penalty payments].PayeesAcctNbr, " " AS
Filler2, Format([sumofamount]*100,"0000000000") AS
PaymentAmount, ' ' AS Reserved, "2005235" AS [Payment Date], [combined
penalty payments].PayeeName, "E" AS [Language Code], " " AS Reserved2, "TEST
- COMINC " AS [Client Short Name], "USD" AS [Currency], " " AS Reserved3,
"USA" AS Country, " " AS Filler3, " " AS Reserved4, "N" AS [Optional
RecInd] INTO [EFT credit records]
FROM [combined penalty payments];

--

Ken Snell
<MS ACCESS MVP>

SELECT "C" AS [Record Type], "123" AS [Transaction Code], "6666660000" AS
[Client Number], " " AS Filler, [combined penalty payments].RefNbr AS
[Customer Number], "99" AS [Payment Number], [combined penalty
payments].BankTransit, [combined penalty payments].PayeesAcctNbr, " " AS
Filler2, String(10-Len([sumofamount]*100),"0") & [sumofamount]*100 AS
PaymentAmount, ' ' AS Reserved, "2005235" AS [Payment Date],
[combined
penalty payments].PayeeName, "E" AS [Language Code], " " AS Reserved2,
"TEST
- COMINC " AS [Client Short Name], "USD" AS [Currency], " " AS
Reserved3,
"USA" AS Country, " " AS Filler3, " " AS Reserved4, "N" AS [Optional
RecInd] INTO [EFT credit records]
FROM [combined penalty payments];

The problem field is the 'Payment Amount' field which I am just trying to
export as text in positions 71-80. That is the only specific information
that
I can think of with regards to the export. The leading zeros that are
there
when I look at the table in Access do not come through in the text file.

You're right... you did say "text file"... sorry, too little sleep in
past
four days.

Post the SQL of the query that you're exporting. Also post the exact
specification information for the particular field that is giving you a
problem.

 

[Guest]

I changed my query definition as you suggested and the export of the table or
query still is not formatted with leading zeros.

I appreciate your suggestions though. Thanks!

You need to explicitly format the output of that field if you want it to be
"text":

SELECT "C" AS [Record Type], "123" AS [Transaction Code], "6666660000" AS
[Client Number], " " AS Filler, [combined penalty payments].RefNbr AS
[Customer Number], "99" AS [Payment Number], [combined penalty
payments].BankTransit, [combined penalty payments].PayeesAcctNbr, " " AS
Filler2, Format([sumofamount]*100,"0000000000") AS
PaymentAmount, ' ' AS Reserved, "2005235" AS [Payment Date], [combined
penalty payments].PayeeName, "E" AS [Language Code], " " AS Reserved2, "TEST
- COMINC " AS [Client Short Name], "USD" AS [Currency], " " AS Reserved3,
"USA" AS Country, " " AS Filler3, " " AS Reserved4, "N" AS [Optional
RecInd] INTO [EFT credit records]
FROM [combined penalty payments];

--

Ken Snell
<MS ACCESS MVP>

SELECT "C" AS [Record Type], "123" AS [Transaction Code], "6666660000" AS
[Client Number], " " AS Filler, [combined penalty payments].RefNbr AS
[Customer Number], "99" AS [Payment Number], [combined penalty
payments].BankTransit, [combined penalty payments].PayeesAcctNbr, " " AS
Filler2, String(10-Len([sumofamount]*100),"0") & [sumofamount]*100 AS
PaymentAmount, ' ' AS Reserved, "2005235" AS [Payment Date],
[combined
penalty payments].PayeeName, "E" AS [Language Code], " " AS Reserved2,
"TEST
- COMINC " AS [Client Short Name], "USD" AS [Currency], " " AS
Reserved3,
"USA" AS Country, " " AS Filler3, " " AS Reserved4, "N" AS [Optional
RecInd] INTO [EFT credit records]
FROM [combined penalty payments];

The problem field is the 'Payment Amount' field which I am just trying to
export as text in positions 71-80. That is the only specific information
that
I can think of with regards to the export. The leading zeros that are
there
when I look at the table in Access do not come through in the text file.

You're right... you did say "text file"... sorry, too little sleep in
past
four days.

Post the SQL of the query that you're exporting. Also post the exact
specification information for the particular field that is giving you a
problem.

--

Ken Snell
<MS ACCESS MVP>


I am exporting to a text file, not Excel.
My process is this - I have a make-table query that creates the table
that
I
want to export - the resulting table is all text fields. In that query
I
am
formatting a field that is initially a double (2 decimal places) into a
string so that I can zero-fill and right justify. The table when I look
at
in
Access has the correct format, with the leading zeros.

I did change something since my original post: Now the exported text
file
has the amounts without leading zeros and not right-justified.
:

Tell us what is in your export specification. Are you exporting into
an
existing EXCEL file, or is ACCESS creating an EXCEL file for you?

--

Ken Snell
<MS ACCESS MVP>

I am having trouble exporting a table from Access into text. The
problem
is
that I have a text field in my table (although it contains numerics
only)
that gets exported as currency. So for example if I have the value
'0000000345' in my table, when I export it, the result is $345.00! I
am
using
an export file specification.