Export Table Data To Formatted Text File

[NEWSGROUPS]

I have data in a table in an Access 2000 database that needs to be exported
to a formatted text file. For instance, the first field is an account number
that is formatted in the table as text and is 8 characters long. This field
needs to be exported as pic(15) padded in the front with 0's (zeros). The
next field an ID name that is 15 characters that needs to be exported as
pic(20) padded with trailing spaces. There are about 5 fields in total that
need this type of formatting when being exported (and about 200 records) to
this text file as well as a requirement of a space between each field. Any
help would be greatly appreciated.

Marco

[salad]

NEWSGROUPS wrote:

I have data in a table in an Access 2000 database that needs to be exported
to a formatted text file. For instance, the first field is an account number
that is formatted in the table as text and is 8 characters long. This field
needs to be exported as pic(15) padded in the front with 0's (zeros). The
next field an ID name that is 15 characters that needs to be exported as
pic(20) padded with trailing spaces. There are about 5 fields in total that
need this type of formatting when being exported (and about 200 records) to
this text file as well as a requirement of a space between each field. Any
help would be greatly appreciated.

Marco

Maybe create a query and export that instead of a table.

When you create the export (click on the query and select File/Export
then select .Txt as the type then the Export button. You'll step
through the wizard. You want to press Advanced to create an export
specification if you will be doing this more than once. One of the
options is to specify the delimiter...space being one.

Man, I haven't seen anything like pic(15) since my...too many years have
passed.

[NEWSGROUPS]

I appreciate your reply and have tried this method, but this will be an
everyday occurance and this does not allow the padding in the front or rear
of the field with spaces or zeros.

Marco

"salad" <oi*@vinegar.comwrote in message
news:W6*****************@newsread3.news.pas.earthl ink.net...

NEWSGROUPS wrote:

>I have data in a table in an Access 2000 database that needs to be
exported to a formatted text file. For instance, the first field is an
account number that is formatted in the table as text and is 8 characters
long. This field needs to be exported as pic(15) padded in the front with
0's (zeros). The next field an ID name that is 15 characters that needs
to be exported as pic(20) padded with trailing spaces. There are about 5
fields in total that need this type of formatting when being exported
(and about 200 records) to this text file as well as a requirement of a
space between each field. Any help would be greatly appreciated.

Marco

Maybe create a query and export that instead of a table.

When you create the export (click on the query and select File/Export then
select .Txt as the type then the Export button. You'll step through the
wizard. You want to press Advanced to create an export specification if
you will be doing this more than once. One of the options is to specify
the delimiter...space being one.

Man, I haven't seen anything like pic(15) since my...too many years have
passed.

[David Harlowe]

In the Design view of the Query you should specify the fields that need to
be padded as defined fields. The account field might look something like
this:

acct15:right("000000000000000" & TRIM(account),15)

The ID field might look something like this with 20 spaces between the quote
marks:

id20:left(TRIM(ID) & " ",20)

Using these string functions you should be able to build whatever fields you
need to export.

I hope this helps,
David
"NEWSGROUPS" <he******@yahoo.comwrote in message
news:Mu******************************@comcast.com. ..

I appreciate your reply and have tried this method, but this will be an
everyday occurance and this does not allow the padding in the front or

rear

of the field with spaces or zeros.

Marco

"salad" <oi*@vinegar.comwrote in message
news:W6*****************@newsread3.news.pas.earthl ink.net...

NEWSGROUPS wrote:

I have data in a table in an Access 2000 database that needs to be
exported to a formatted text file. For instance, the first field is an
account number that is formatted in the table as text and is 8

characters

long. This field needs to be exported as pic(15) padded in the front

with

0's (zeros). The next field an ID name that is 15 characters that needs
to be exported as pic(20) padded with trailing spaces. There are about

5

fields in total that need this type of formatting when being exported
(and about 200 records) to this text file as well as a requirement of a
space between each field. Any help would be greatly appreciated.

Marco

Maybe create a query and export that instead of a table.

When you create the export (click on the query and select File/Export

then

select .Txt as the type then the Export button. You'll step through the
wizard. You want to press Advanced to create an export specification if
you will be doing this more than once. One of the options is to specify
the delimiter...space being one.

Man, I haven't seen anything like pic(15) since my...too many years have
passed.

[salad]

NEWSGROUPS wrote:

I appreciate your reply and have tried this method, but this will be an
everyday occurance and this does not allow the padding in the front or rear
of the field with spaces or zeros.

Marco

"salad" <oi*@vinegar.comwrote in message
news:W6*****************@newsread3.news.pas.earthl ink.net...

>>NEWSGROUPS wrote:

>>>I have data in a table in an Access 2000 database that needs to be
exported to a formatted text file. For instance, the first field is an
account number that is formatted in the table as text and is 8 characters
long. This field needs to be exported as pic(15) padded in the front with
0's (zeros). The next field an ID name that is 15 characters that needs
to be exported as pic(20) padded with trailing spaces. There are about 5
fields in total that need this type of formatting when being exported
(and about 200 records) to this text file as well as a requirement of a
space between each field. Any help would be greatly appreciated.

Marco

Maybe create a query and export that instead of a table.

When you create the export (click on the query and select File/Export then
select .Txt as the type then the Export button. You'll step through the
wizard. You want to press Advanced to create an export specification if
you will be doing this more than once. One of the options is to specify
the delimiter...space being one.

Man, I haven't seen anything like pic(15) since my...too many years have
passed.

David gave you the correct methods. You'll need to learn how to perform
string manipulation. Look at the following functions in
help...hint...create a new module. Enter the following words into the
code module. Hightlight each one and press the F1 key. Look at any
code examples for each function.
Left
Right
Mid
String
Len
Space
Trim
LTrim
Rtrim
NZ
Format
Str
Cstr (and related items)
Instr
for starters.

The & is used to concatenate variables/values together.

[bobh]

Man, I haven't seen anything like pic(15) since my...too many years have

passed.

I haved seen this since back in my COBOL days, oh the
memories...... :)
bobh.

On Jun 20, 10:11 pm, salad <[e-mail]:

NEWSGROUPS wrote:

I have data in a table in an Access 2000 database that needs to be exported
to a formatted text file. For instance, the first field is an account number
that is formatted in the table as text and is 8 characters long. This field
needs to be exported as pic(15) padded in the front with 0's (zeros). The
next field an ID name that is 15 characters that needs to be exported as
pic(20) padded with trailing spaces. There are about 5 fields in total that
need this type of formatting when being exported (and about 200 records) to
this text file as well as a requirement of a space between each field. Any
help would be greatly appreciated.

Marco

Maybe create a query and export that instead of a table.

When you create the export (click on the query and select File/Export
then select .Txt as the type then the Export button. You'll step
through the wizard. You want to press Advanced to create an export
specification if you will be doing this more than once. One of the
options is to specify the delimiter...space being one.

Man, I haven't seen anything like pic(15) since my...too many years have
passed.

[NEWSGROUPS]

I have tried this suggestion with much sucess.Thank you. But one problem
still remains. One of the fields I have to export to this text file is a
number field formatted as a double that represents a dollar amount (what
this means for instance is the amount $255.00 is stored in the table as 255
and the amount $255.50 is stored as 255.5). I needed to export these fields
without the decimal point so what should be on the text file for the $255.00
is 25500 and the $255.50 will be shown as 25550 with the right most 2 digits
being the implied decimal. The problem here as you can see from my example
is there are not always 2 decimal places on all my records. I tried
appending the records to a currency field which did display the correct
format but when I pass this to a function to strip the decimal place or use
an in string to get the left() of the decimal the $255.00 returns an error
and the $255.50 returns 2555 as if the decimal does not exist. Can anyone
offer assistance in this matter as I have tried everything possible.

Marco

"David Harlowe" <da***********@nomail.comwrote in message
news:6q******************@newssvr29.news.prodigy.n et...

In the Design view of the Query you should specify the fields that need to
be padded as defined fields. The account field might look something like
this:

acct15:right("000000000000000" & TRIM(account),15)

The ID field might look something like this with 20 spaces between the
quote
marks:

id20:left(TRIM(ID) & " ",20)

Using these string functions you should be able to build whatever fields
you
need to export.

I hope this helps,
David
"NEWSGROUPS" <he******@yahoo.comwrote in message
news:Mu******************************@comcast.com. ..

>I appreciate your reply and have tried this method, but this will be an
everyday occurance and this does not allow the padding in the front or

rear

>of the field with spaces or zeros.

Marco

"salad" <oi*@vinegar.comwrote in message
news:W6*****************@newsread3.news.pas.earth link.net...

NEWSGROUPS wrote:
I have data in a table in an Access 2000 database that needs to be
exported to a formatted text file. For instance, the first field is an
account number that is formatted in the table as text and is 8

characters

>long. This field needs to be exported as pic(15) padded in the front

with

>0's (zeros). The next field an ID name that is 15 characters that
needs
to be exported as pic(20) padded with trailing spaces. There are about

5

>fields in total that need this type of formatting when being exported
(and about 200 records) to this text file as well as a requirement of
a
space between each field. Any help would be greatly appreciated.

Marco
Maybe create a query and export that instead of a table.

When you create the export (click on the query and select File/Export

then

select .Txt as the type then the Export button. You'll step through
the
wizard. You want to press Advanced to create an export specification
if
you will be doing this more than once. One of the options is to
specify
the delimiter...space being one.

Man, I haven't seen anything like pic(15) since my...too many years
have
passed.

[salad]

NEWSGROUPS wrote:

I have tried this suggestion with much sucess.Thank you. But one problem
still remains. One of the fields I have to export to this text file is a
number field formatted as a double that represents a dollar amount (what
this means for instance is the amount $255.00 is stored in the table as 255
and the amount $255.50 is stored as 255.5). I needed to export these fields
without the decimal point so what should be on the text file for the $255.00
is 25500 and the $255.50 will be shown as 25550 with the right most 2 digits
being the implied decimal. The problem here as you can see from my example
is there are not always 2 decimal places on all my records. I tried
appending the records to a currency field which did display the correct
format but when I pass this to a function to strip the decimal place or use
an in string to get the left() of the decimal the $255.00 returns an error
and the $255.50 returns 2555 as if the decimal does not exist. Can anyone
offer assistance in this matter as I have tried everything possible.

Marco

I'm curious...did you try Round yet? You really would want to round a
double...who knows what the real value of the number is.

Don't know what version you are working. You might want to check out
the following link for rounding
http://www.mvps.org/access/modules/mdl0054.htm if the Round() function
isn't available in your version.

So your column might look like this:
RoundedValue : Round([Price],2) * 100

[NEWSGROUPS]

Thank you it worked perfectly. I guess I was over thinking this. I though it
had to be more complex.

Thanks again,
Marco
"salad" <oi*@vinegar.comwrote in message
news:88*****************@newsread4.news.pas.earthl ink.net...

NEWSGROUPS wrote:

>I have tried this suggestion with much sucess.Thank you. But one problem
still remains. One of the fields I have to export to this text file is a
number field formatted as a double that represents a dollar amount (what
this means for instance is the amount $255.00 is stored in the table as
255 and the amount $255.50 is stored as 255.5). I needed to export these
fields without the decimal point so what should be on the text file for
the $255.00 is 25500 and the $255.50 will be shown as 25550 with the
right most 2 digits being the implied decimal. The problem here as you
can see from my example is there are not always 2 decimal places on all
my records. I tried appending the records to a currency field which did
display the correct format but when I pass this to a function to strip
the decimal place or use an in string to get the left() of the decimal
the $255.00 returns an error and the $255.50 returns 2555 as if the
decimal does not exist. Can anyone offer assistance in this matter as I
have tried everything possible.

Marco

I'm curious...did you try Round yet? You really would want to round a
double...who knows what the real value of the number is.

Don't know what version you are working. You might want to check out the
following link for rounding http://www.mvps.org/access/modules/mdl0054.htm
if the Round() function isn't available in your version.

So your column might look like this:
RoundedValue : Round([Price],2) * 100

[salad]

NEWSGROUPS wrote:

Thank you it worked perfectly. I guess I was over thinking this. I though it
had to be more complex.

Thanks again,
Marco

LOL. I have found that when I get frustrated about how I want it to do
this but the program wants to do that I need to step back, relax, do
something to get my mind off the task at hand. Coming back refreshed
and with an open mind often times provides solutions....I was too buried
in the trees to see the forest.

>

"salad" <oi*@vinegar.comwrote in message
news:88*****************@newsread4.news.pas.earthl ink.net...

>>NEWSGROUPS wrote:

>>>I have tried this suggestion with much sucess.Thank you. But one problem
still remains. One of the fields I have to export to this text file is a
number field formatted as a double that represents a dollar amount (what
this means for instance is the amount $255.00 is stored in the table as
255 and the amount $255.50 is stored as 255.5). I needed to export these
fields without the decimal point so what should be on the text file for
the $255.00 is 25500 and the $255.50 will be shown as 25550 with the
right most 2 digits being the implied decimal. The problem here as you
can see from my example is there are not always 2 decimal places on all
my records. I tried appending the records to a currency field which did
display the correct format but when I pass this to a function to strip
the decimal place or use an in string to get the left() of the decimal
the $255.00 returns an error and the $255.50 returns 2555 as if the
decimal does not exist. Can anyone offer assistance in this matter as I
have tried everything possible.

Marco

I'm curious...did you try Round yet? You really would want to round a
double...who knows what the real value of the number is.

Don't know what version you are working. You might want to check out the
following link for rounding http://www.mvps.org/access/modules/mdl0054.htm
if the Round() function isn't available in your version.

So your column might look like this:
RoundedValue : Round([Price],2) * 100