 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / August 2005Query fields truncated during text export
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? 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.  SignatureKen Snell <MS ACCESS MVP> > 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 [quoted text clipped - 12 lines] > can think of and I can't get the full field to export. Has anyong else > encountered this problem? 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? > 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 [quoted text clipped - 15 lines] > > can think of and I can't get the full field to export. Has anyong else > > encountered this problem? 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. SignatureKen Snell <MS ACCESS MVP> >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 [quoted text clipped - 28 lines] >> > can think of and I can't get the full field to export. Has anyong else >> > encountered this problem? 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. > 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? [quoted text clipped - 39 lines] > >> > can think of and I can't get the full field to export. Has anyong else > >> > encountered this problem? 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. SignatureKen Snell <MS ACCESS MVP> > Yes, I'm exporting to a fixed-width text file. OK, I tried putting > quotes [quoted text clipped - 60 lines] >> >> > else >> >> > encountered this problem? Hi Ken, when I click reply I get a web form and it doesn't show any email address. My address is Wpenfold@ithaca.edu . 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 > 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 [quoted text clipped - 67 lines] > >> >> > else > >> >> > encountered this problem? 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. SignatureKen Snell <MS ACCESS MVP> > Hi Ken, when I click reply I get a web form and it doesn't show any email > address. My address is Wpenfold@ithaca.edu . If you'd like to email me [quoted text clipped - 90 lines] >> >> >> > else >> >> >> > encountered this problem? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.