Exporting query or report to Excel results in Text field data loss

M

Merlin

Greetings

I have created a query in Access containing some Text fields (ie data type
Text or NText) with large text values.

If I export the results to Excel using File/Export... the large text fileds
are truncated to 255 characters in the resulting excel cells.

I have noticed that exporting a table in the same way does not truncate the
data.

Is there any way around this truncating? It is important that we get the
full data.

I have also tried presenting the data in a report and using docmd.OutputTo,
with the same result.

I am using Access XP, Excel XP and SQL Server 2000.

Thanks
 
W

Warren Brunk

What I like to do in this situation is export to a tab delimited or comma
seperated text file.

Then use excel to open the file. Excel has support for both file types.

thanks,
 
A

aaron.kempf

if you're using SQL Server you could use BCP.exe (on the server)
instead of Access.

BCP / BULK INSERT is a great tool
 
W

Warren Brunk

Steps to exporting a text file-->
Select a table in acess right click and choose Export
Choose "Text File (*.txt,*.csv,*.tab)"
Wizard will popup
Choose delimited click next
Choose csv click next
Name your file and save it to your desktop
Right click on the .csv file on your desktop that you saved and do Open
With --> Excel...

See if that works for you...
 
P

Peter Yang [MSFT]

Hello,

I understand the issue only occurs in query/view when exporting it as Excel
format. This behavior is the same on my side and it is a product limiation
that is awared by product team:

The long field will be truncated when exporting a view to Excel. I
understand your concerns and please rest assured this will be also to
routed to proper channel. They may consider changing this behavior in
future release.

To workaround the issue, we may have to export the data from a table
instead of a view. Here are the steps for your reference:

1. Create a view to get the data from the tables within Access, assuming
the view like this: CREATE VIEW dbo.View1 AS SELECT EmployeeID, Notes FROM
dbo.Employees

2. Create a stored procedure based on the View1 within Access, like this:
Create Procedure StoredProcedure1 As Select * INTO TempTable From View1

3. Run the StoredProcedure1 within Access to create the TempTable.

4. Export the TempTable to Excel. It is supposed that no truncation in the
resulting Excel file at this time.

You could use append query for mdb to work around the issue. However for
adp append query is not avaible.

207668 ACC2000: Exported Query Expression Truncated at 255 Characters
http://support.microsoft.com/default.aspx?scid=kb;EN-US;207668

The other workaround is using DTS in SQL 2000 to directly export the data
from SQL Server to a Excel file. You could use a Data transform task to get
data from the original view and export it to Excel. Based on my
experience, the DTS package may discovere that due to the preformatting of
your Excel Spreadsheet, DTS was considering the columns as Varchar (255)
instead of LongText when exporting data, it will also truncate it to 255.
After changing th column format manually to LongText, the data shall go
untruncated.

If anything is unclear, please feel free to let's know. We look forward to
your reply.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
R

Robert Morley

The other alternative is to export the results programmatically by
automating Excel. That's what I do on my end, though that's a lot of work
if you're trying to keep it simple.

Even there, as I remember it, there are problems with the .CopyFromRecordset
command and for some exports, I had to use a cell-by-cell method.

Finally, just for added fun, Excel often seems not to want to display
"Text"-formatted cells correctly when the text is long. Change the format
to "General", and it will appear correctly (assuming you managed to export
properly).



Rob
 
A

aaron.kempf

Peter

This behavior is the same on my side and it is a product limiation
that is awared by product team:


THERE IS NO SUCH THING AS A KNOWN LIMITATION.

IF THERE IS A BUG YOU NEED TO FIX IT; NOT SIT AROUND AND SAY 'IT IS A
KNOWN ISSUE'

THERE IS NO SUCH THING AS AN 'OPEN ISSUE' WHEN YOUR ABUSIVE COMPANY HAS
$40 BILLION IN CASH.

HIRE SOME FUCKING PROGRAMMERS AND FIX IT YOU STUPID FUCKING __CHINK__

-Aaron
 
P

Peter Yang [MSFT]

Hello Merlin,

I understand that you have found your own workaround by importing data into
Excel instead of exporting it from Access. Thank you for your sharing and
this shall certainly benefit the community. :)

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top