How to retain formatting when exporting to text file

  • Thread starter Thread starter oli merge
  • Start date Start date
O

oli merge

Hi,

I am using Access 2003. I regularly have to clean address lists and export
to fixed width text files for loading else where.

As part of the cleaining process, I append the data to a template table
which has the correct field lengths and then copy this, rename and export
this new table to a fixed width text file.

Recently I have been asked to capitalise everything, which I was delighted
to find I could do by just entering '>' into the format of each field in the
table design.

However, I now notice that this seems to be for display purposes only, and
when the exported text file is opened it still has a mixture of caps and
lowercase. Why is this? This seems pretty un-helpful to me, what is the point
of having formatting if it isnt really formatting the data but the way it is
displayed?

Anyway, I am looking for a solution to capitalise everything in my tables
that I export as quickly and easily as possible. The import thing is that the
exported text file retains the formatting and doesnt return to mixed case.
Would this be achievable with a query acting on the template table which I
append to? Would I have to use VBA?

any advice would be appreciated.
 
The point is to allow flexibility in how data is presented.
You can solve your problem with an Update query that will convert all text
fields to uppercase in the template table prior to the export. In the query
designer, use the Update To row for each text field to do the conversion
using the Ucase function:
Field Name: [SomeTextField]
Update To: UCase([SomeTextField]

Do the same for all text fields in your table.
 
Hi,

Thanks for the quick reply, thats exactly what I needed and since Im using
the same template table means I dont have to re-write the query each time!

Cheers,

Oli

Klatuu said:
The point is to allow flexibility in how data is presented.
You can solve your problem with an Update query that will convert all text
fields to uppercase in the template table prior to the export. In the query
designer, use the Update To row for each text field to do the conversion
using the Ucase function:
Field Name: [SomeTextField]
Update To: UCase([SomeTextField]

Do the same for all text fields in your table.
--
Dave Hargis, Microsoft Access MVP


oli merge said:
Hi,

I am using Access 2003. I regularly have to clean address lists and export
to fixed width text files for loading else where.

As part of the cleaining process, I append the data to a template table
which has the correct field lengths and then copy this, rename and export
this new table to a fixed width text file.

Recently I have been asked to capitalise everything, which I was delighted
to find I could do by just entering '>' into the format of each field in the
table design.

However, I now notice that this seems to be for display purposes only, and
when the exported text file is opened it still has a mixture of caps and
lowercase. Why is this? This seems pretty un-helpful to me, what is the point
of having formatting if it isnt really formatting the data but the way it is
displayed?

Anyway, I am looking for a solution to capitalise everything in my tables
that I export as quickly and easily as possible. The import thing is that the
exported text file retains the formatting and doesnt return to mixed case.
Would this be achievable with a query acting on the template table which I
append to? Would I have to use VBA?

any advice would be appreciated.
Click to expand...
Click to expand...
 
Using the same template is a good idea.
Glad I could help.
--
Dave Hargis, Microsoft Access MVP


oli merge said:
Hi,

Thanks for the quick reply, thats exactly what I needed and since Im using
the same template table means I dont have to re-write the query each time!

Cheers,

Oli

Klatuu said:
The point is to allow flexibility in how data is presented.
You can solve your problem with an Update query that will convert all text
fields to uppercase in the template table prior to the export. In the query
designer, use the Update To row for each text field to do the conversion
using the Ucase function:
Field Name: [SomeTextField]
Update To: UCase([SomeTextField]

Do the same for all text fields in your table.
--
Dave Hargis, Microsoft Access MVP


oli merge said:
Hi,

I am using Access 2003. I regularly have to clean address lists and export
to fixed width text files for loading else where.

As part of the cleaining process, I append the data to a template table
which has the correct field lengths and then copy this, rename and export
this new table to a fixed width text file.

Recently I have been asked to capitalise everything, which I was delighted
to find I could do by just entering '>' into the format of each field in the
table design.

However, I now notice that this seems to be for display purposes only, and
when the exported text file is opened it still has a mixture of caps and
lowercase. Why is this? This seems pretty un-helpful to me, what is the point
of having formatting if it isnt really formatting the data but the way it is
displayed?

Anyway, I am looking for a solution to capitalise everything in my tables
that I export as quickly and easily as possible. The import thing is that the
exported text file retains the formatting and doesnt return to mixed case.
Would this be achievable with a query acting on the template table which I
append to? Would I have to use VBA?

any advice would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top