473,231 Members | 2,294 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

Export Table Data To Formatted Text File

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
Jun 21 '07 #1
9 7631
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.

Jun 21 '07 #2
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.

Jun 21 '07 #3
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.


Jun 21 '07 #4
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.
Jun 21 '07 #5
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 <o...@vinegar.comwrote:
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.

Jun 21 '07 #6
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.



Jun 22 '07 #7
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
Jun 22 '07 #8
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


Jun 22 '07 #9
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
Jun 22 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Paolo | last post by:
Friends, I need help with some code to export different tables to a single spreadsheet in Excel. My excel file is named REPORT and the spreadsheet is named CLIENTS. I do have the code to export...
3
by: Jorge Cecílio | last post by:
Hi! I would like to export some MS-Access reports output to pdf. However, the only possibility offered by Access (afaik) for me to export formatted output is snp (snapshot) (I use MS-Office...
3
by: nologin | last post by:
Is it possible to export data exposed by DataView to Excel file for example ? Seba
3
by: Daniel Wetzler | last post by:
Dear MSSQL- experts, I have a strange problem with SQLSERVER 2000. I tried to export a table of about 40000 lines into a text file using the Enterprise manager export assitant. I was astonished...
5
by: RadhakrishnanR | last post by:
Hi, By using VB6.0, I want to export database table data into (i.e based on the selected file type(xls or txt)) excel file or text file with a tab delimited text file. My User interface has: ...
7
by: Vanessa | last post by:
hi Everyone, I have two questions on exporting data to Excel using ASP (w/o converting formatted excel file into web page and then plug in the dynamic data): 1. Can we export data into...
1
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm...
7
Merlin1857
by: Merlin1857 | last post by:
Its great producing data for users to look at in your web pages and generally that is sufficient for their needs but sometimes you may want to supply your user with the data in a form they can...
3
by: Wayne | last post by:
I'm trying to automate the export of a query to a text file using code. If I export the query manually I get the exact result that I want i.e. If I select the query then choose File/Export from...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.