Export memo field to Excel truncates values

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Microsoft Access » Microsoft Access » Export memo field to Excel truncates values

12 posts, Page 1 of 2

1 2 »»

Export memo field to Excel truncates values

Rate Topic

Display Mode

Topic Options

Author

Message

npranj

Posted Wednesday, February 01, 2012 2:31 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:15 AM
Points: 36, Visits: 254

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

bitbucket-25253

Posted Wednesday, February 01, 2012 3:14 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 5,099, Visits: 20,190

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

WendellB

Posted Wednesday, February 01, 2012 3:18 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:46 AM
Points: 106, Visits: 405

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.

Post #1245365

npranj

Posted Wednesday, February 01, 2012 10:37 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:15 AM
Points: 36, Visits: 254

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

WendellB

Posted Thursday, February 02, 2012 5:18 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:46 AM
Points: 106, Visits: 405

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.

Post #1245665

npranj

Posted Thursday, February 02, 2012 9:00 AM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:15 AM
Points: 36, Visits: 254

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

npranj

Posted Thursday, February 02, 2012 3:24 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:15 AM
Points: 36, Visits: 254

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

npranj

Posted Thursday, February 02, 2012 3:31 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:15 AM
Points: 36, Visits: 254

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

WendellB

Posted Thursday, February 02, 2012 3:59 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:46 AM
Points: 106, Visits: 405

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.

Post #1246174

npranj

Posted Thursday, February 02, 2012 4:27 PM

SSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:15 AM
Points: 36, Visits: 254

Microsoft Office Access 2007 (12.0.6535.5005) SP2 MSO (12.0.6562.5003)

Post #1246179

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions