Query fields truncated during text export

[Wendy Penfold]

When I export data in fixed format to a text file from a query, calculated
character fields are being truncated. I'm exporting around 10,000 records.
The field should be 15 characters in length and when I use len() to check it,
that's what it is. The first few hundred records in the file happen to
contain a value that is 5 positions long, the rest of the field is filled
with spaces,("MAJOR "). The text export truncates the spaces and
only exports 5 characters of the field. Later in the table, the values are
longer, but still only 5 characters are being exported, ("CONCE" instead of
"CONCENTRATION ") I've tried every form of string padding and FORMAT() I
can think of and I can't get the full field to export. Has anyong else
encountered this problem?

 

[Ken Snell [MVP]]

Use an export specification to specify the width of the field. You can do
this in the export wizard window...click the Advanced button at bottom left
of screen.

 

[Wendy Penfold]

I am using an export spec. The export wizard insists that the field is 5
chars in length and that's how it shows in the viewer. But when I add
another field to the query to show the LEN of the calculated field, it says
it's 15. Any other ideas?

 

[Ken Snell [MVP]]

I am understanding that you're exporting in a fixed-width format. Is this
correct? Is the field width in the specification set to 15?

Trailing spaces usually are trimmed by Jet database engine when exporting to
a delimited file format. The only way I know to keep them is to use a fixed
width format, which isn't always easy.

You could try surrounding the field's contents with " characters at
beginning and end (delimit the field's contents) as part of the query that
you export.

--

Ken Snell
<MS ACCESS MVP>

 

[Wendy Penfold]

Yes, I'm exporting to a fixed-width text file. OK, I tried putting quotes
around the string. In the query it looks like 'MAJOR ' . I select
export and in the sample export format window, with delimited checked, it
shows: "'MAJOR '" I click 'Fixed Width', and then the viewer
shows: 'MAJO for the field. So it really seems to like 5 characters
regardless of the length of the data--it's not just the spaces it's
truncating. Do you think this qualifies as a bug. I've tried looking in KB
and I can't seem to find any other report of this problem.

 

[Ken Snell [MVP]]

Can you zip up an example of your database and email to me? I'd like to take
a look at this behavior firsthand. My email address is obtained from my
"reply to" email address by removing the words this is not real from that
munged address.

Please include information about the name of the query/etc. so that I can
reproduce your actions.
--

Ken Snell
<MS ACCESS MVP>

 

[Wendy Penfold]

Hi Ken, when I click reply I get a web form and it doesn't show any email
address. My address is (e-mail address removed) . If you'd like to email me
directly. I'll try to zip something up to send to you that recreates the
problem. The query is based on tables linked to an Oracle DB so I'll have to
make an access table. W

 

[Ken Snell [MVP]]

Email sent to you.

I don't know if linking to Oracle is the issue, but it's very possible that
it is. It may become necessary to import the data from Oracle into a
temporary table in your ACCESS front end, export the data from the temporary
table, and then delete the data from the temporary table.