MS Access Database with External Data - exporting tables from Access to text files

Author

exporting tables from Access to text files

Mazda5

2005-08-24, 8:26 pm

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]

2005-08-24, 8:26 pm

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>

"Mazda5" <Mazda5@discussions.microsoft.com> wrote in message
news:4DDE6958-D3AE-4ECD-BD4D- FE2BA76A6F06@microso
ft.com...
>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.

Mazda5

2005-08-24, 8:26 pm

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]" wrote:

> 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>
>
> "Mazda5" <Mazda5@discussions.microsoft.com> wrote in message
> news:4DDE6958-D3AE-4ECD-BD4D- FE2BA76A6F06@microso
ft.com...
>
>
>

Ken Snell [MVP]

2005-08-24, 8:26 pm

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>

"Mazda5" <Mazda5@discussions.microsoft.com> wrote in message
news:150CD9C1-A4B6-4F2B-B428- 42D25E709A29@microso
ft.com...[color=darkred]
>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]" wrote:
>

Mazda5

2005-08-25, 9:25 am

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(& #91;sumofamount]*100
),"0") & & #91;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]" wrote:

> 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>
>
>
> "Mazda5" <Mazda5@discussions.microsoft.com> wrote in message
> news:150CD9C1-A4B6-4F2B-B428- 42D25E709A29@microso
ft.com...
>
>
>

Ken Snell [MVP]

2005-08-25, 11:25 am

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(& #91;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>

"Mazda5" <Mazda5@discussions.microsoft.com> wrote in message
news:E5C2A150-7224-4751-95F6- 4D12B5C6B5ED@microso
ft.com...[color=darkred]
> 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(& #91;sumofamount]*100
),"0") & & #91;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]" wrote:
>

Mazda5

2005-08-25, 1:25 pm

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!

"Ken Snell [MVP]" wrote:

> 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(& #91;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>
>
>
>
> "Mazda5" <Mazda5@discussions.microsoft.com> wrote in message
> news:E5C2A150-7224-4751-95F6- 4D12B5C6B5ED@microso
ft.com...
>
>
>

Sponsored Links