|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
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,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Merlin" <evalua[ at ]community.nospam> wrote in message
news:FDE25CC9-0745-4085-AF0C-7DB9EEAAA1BA[ at ]microsoft.com...
[Quoted Text] > 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
>
|
|
How?
"Warren Brunk" wrote:
[Quoted Text] > 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,
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
>
> "Merlin" <evalua[ at ]community.nospam> wrote in message
> news:FDE25CC9-0745-4085-AF0C-7DB9EEAAA1BA[ at ]microsoft.com...
> > 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
> >
>
>
>
|
|
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...
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Merlin" <evalua[ at ]community.nospam> wrote in message
news:1676B1F2-3E6B-4274-AEEA-C8FEA0616D46[ at ]microsoft.com...
[Quoted Text] > How?
>
> "Warren Brunk" wrote:
>
>> 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,
>>
>> --
>> /*
>> Warren Brunk - MCITP - SQL 2005, MCDBA
>> www.techintsolutions.com
>> */
>>
>>
>> "Merlin" <evalua[ at ]community.nospam> wrote in message
>> news:FDE25CC9-0745-4085-AF0C-7DB9EEAAA1BA[ at ]microsoft.com...
>> > 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
>> >
>>
>>
>>
|
|
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.
|
|
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
"Merlin" <evalua[ at ]community.nospam> wrote in message
news:FDE25CC9-0745-4085-AF0C-7DB9EEAAA1BA[ at ]microsoft.com...
[Quoted Text] > 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
>
|
|
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.
======================================================
|
|
|