Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Export memo field to Excel truncates values Expand / Collapse
Author
Message
Posted Wednesday, February 1, 2012 2:31 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:34 PM
Points: 51, Visits: 319
I am using MS Access 2007.
There are two tables each having a memo column.
I have written a IIF statement to compare both these columns. If the values match exactly then the query returns the column from the first table. If the values do not match then it must suffix the column text with "Error:".

Issue:
the query returns correct results, however when I try to export the value into Excel 2007, then it trauncates after 255 chars.

Please could you advise on how this can be fixed. This issu occurs only while exporting. In the query window the correct values are returned though !
Post #1245341
Posted Wednesday, February 1, 2012 3:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
For EXCEL read the following as EXCEL by default will only display a maximum of 255 characters in a text cell, the links below should be of assistance.

http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx#top

and

http://ewbi.blogs.com/develops/2005/02/autofit_an_exce.html


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1245362
Posted Wednesday, February 1, 2012 3:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:46 PM
Points: 170, Visits: 627
What version of Excel are you exporting to? If you are choosing the .xls format it will certainly do that. However if you export to the .xlsx format it should export up to 32767 characters from a memo field. Another possible issue is if you are sorting in your query - in that case it will also truncate to 255 characters. Note that Microsoft Access people are more likely to frequent other forums such as UtterAccess, WindowsSecrets or EileensLounge - you may be able to find the answer to your issue there as well.

Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Post #1245365
Posted Wednesday, February 1, 2012 10:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:34 PM
Points: 51, Visits: 319
I am using Excel 2007 and MS Access 2007.

I use DoCmd to import the data from 2Excel files into Access... and then compare the text and use Do Cmd to output the data to another Excel 2007.

I think there are two issues =
1. MSAccess while importing data, checks for the first 10 fields while settng the field type in the dynamic table. For the second file it makes a text field and hence accepts only 255 chrs.
2. I could still compare and provide output for the first 255 chars however, while displaying results, I compare teh memo field with the text field and disply the memo field value.
This value gets truncated after export to excel. The query window does show me the complete data from the memo field.
Post #1245462
Posted Thursday, February 2, 2012 5:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:46 PM
Points: 170, Visits: 627
By DoCmd I presume you are using the DoCmd.TransferSpeadsheet method. If you are importing the Excel file into a new table, Access does indeed check a few rows to determine the data type of each. If on the other hand you create a table with the desired field types, and then import into an existing table, it should import the complete memo field for each row.

I think it would help understand your situation if you could post the code you are using to implement the import and export as well as the SQL string for the query you are using to compare the two tables.


Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Post #1245665
Posted Thursday, February 2, 2012 9:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:34 PM
Points: 51, Visits: 319
Current issue:
-----------------
Yes I am usinig DoCmd to import the excel files into tables and to export query results into excel.
The table structure for import is not predefined.

For import:
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel12Xml, outsname(i) + "Output", outfadd, True, outsname(i) + "$"

For export:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strEngagementSheet, strPathFile

Also tried the following-
-----------------------
1. I did try creating a table structure and reading cell by cell from excel into the table, however, the recordset that read from teh excel cell (for the memo field) - displayed unidentified characters post 255th position.

2. I also tried to export the data into XML first and then importing XML file into Excel.
I have windows 7 installed and Access 2007 crashes when the XML is saved. I noticed that the XML file was getting saved but it was not well formed. The same code worked on a colleagues machine though. So I do not know what is missing in my machine.

Code for saving XML-
=====================
Dim objOrderInfo As AdditionalData
Dim objOrderDetailsInfo As AdditionalData


Set objOrderInfo = Application.CreateAdditionalData
Set objOrderDetailsInfo = objOrderInfo.Add("EngagementsDifference")

objOrderDetailsInfo.Add "EngagementsDifference"
Application.ExportXML ObjectType:=acExportQuery, DataSource:="EngagementsDifference", _
DataTarget:="EngagementsDifference.xml", _
AdditionalData:=objOrderInfo


Code for importing XML into EXCEL
=========================
ActiveWorkbook.XmlImport URL:= _
ActiveWorkbook.Path + "\EngagementsDifference.xml", ImportMap:= _
Nothing, Overwrite:=True, Destination:=Range("$A$1")
Post #1245884
Posted Thursday, February 2, 2012 3:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:34 PM
Points: 51, Visits: 319
I have modified the complete code piece.
Now I have made tables in Access with field type Memo for Description column.
The query has been modifed too.

I need to compare descrioptions from two excel files and display it in excel again.

Making tables helps in terms of textual comparison works for > 255 chars..however, now I see an issue with the output that i see. the excel cell to which the data is exported from access shows junk values after a certain limit. 255 I think.

I tried setting values for this field using mid(Description,0,250) + mid (Description,251,250) etc

I am sure people would have faced this issue earlier. Can someone please helpout.

SAmple of what I see now:
" Core objective assigned to the CRM2 Program was to develop an integrated CRM solution based on PeopleSoft CRM. Application is already live in pr癓Interfac⋆矒嶤维"
Post #1246160
Posted Thursday, February 2, 2012 3:31 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:34 PM
Points: 51, Visits: 319
Further information on teh above.

1. The query does not have any group by clause.
2. The only manipulation with the memo field is the prefixing with text "Error:" when the description in both input files do not match.

3. when i change the cell format in excel to "General" teh problem still appears.
If changed to "Text" then i see ###########
Post #1246165
Posted Thursday, February 2, 2012 3:59 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:46 PM
Points: 170, Visits: 627
One question I should have asked is whether you have Service Pack 2 of Office 2007 installed? There were some fixes to the import and export routines in that update. I'm attempting to duplicate you situation, but other things keep interrupting.


Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Post #1246174
Posted Thursday, February 2, 2012 4:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, December 11, 2014 9:34 PM
Points: 51, Visits: 319
Microsoft Office Access 2007 (12.0.6535.5005) SP2 MSO (12.0.6562.5003)
Post #1246179
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse