exporting tables from Access to text files

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mazda5 - 24 Aug 2005 20:28 GMT

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.

Reply to this Message

Ken Snell [MVP] - 24 Aug 2005 20:53 GMT

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?

Signature

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.

Reply to this Message

Mazda5 - 24 Aug 2005 21:30 GMT

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?
[quoted text clipped - 5 lines]
> > using
> > an export file specification.

Reply to this Message

Ken Snell [MVP] - 24 Aug 2005 21:49 GMT

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.

Signature

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
[quoted text clipped - 20 lines]
>> > using
>> > an export file specification.

Reply to this Message

Mazda5 - 25 Aug 2005 14:09 GMT

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.
[quoted text clipped - 27 lines]
> >> > using
> >> > an export file specification.

Reply to this Message

Ken Snell [MVP] - 25 Aug 2005 15:47 GMT

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];

Signature

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
[quoted text clipped - 57 lines]
>> >> > using
>> >> > an export file specification.

Reply to this Message

Mazda5 - 25 Aug 2005 18:21 GMT

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":
[quoted text clipped - 72 lines]
> >> >> > using
> >> >> > an export file specification.

Reply to this Message