Avoiding Data Cut-Off in Excel

It’s been a busy week and I’ve been working hard. I think I’ll reward myself by writing a blog post for people I’ve never met. Here we go.

Let’s talk about getting Access Memo Fields into Excel.

 

So you’re there you are, happily copying data from this Access table to Excel. Notice the comment length here (big stuff).

 

 

Then you figure out that Access is cutting off your ‘comments’ field at 255 characters.

 

This is because your ‘comments’ field is actually a Memo data type. In a field designated as a Memo field, you can enter as many characters as you like (no 255 character limit). Unfortunately, copying and pasting Memo fields into Excel will result in data that is cut off. I know…Excel can handle well over 30,000 characters in a cell. The problem is when you copy and paste from Access, Access still exports only 255 characters for backwards compatibility purposes.

 

So what do you do?

(a) Start copying and pasting individual comments 255 characters at a time.

(b) Parse your comments into separate 255-character fields, then concatenate them in Excel.

(c) Get a job at Barnes & Noble to spend your days alphabetizing books.

The answer is (c). 

But just in case you can’t afford to quit your job, here are a couple of methods you can use to get Access Memo fields into Excel.

 

Method 1: Use the Export with Formatting Option

Right click on your table and choose to export it.

 

When choosing an output, be sure to select the ‘Export data with formatting and layout’ option.

 

 

Method 2: Use a TransferSpreadsheet Macro

You can also create a Macro to use the TransferSpreadsheet action. Using this action will bypass Access’ tendency to cut data off to 255 characters.

As you can see, you’ll have to identify the source table, and the destination path.

 


 

 

There you have it. Now if you’ll excuse me, I’ve got to go get ready for my Barnes & Noble interview.

11 thoughts on “Avoiding Data Cut-Off in Excel

  1. Gordon

    This is a PITA when transferring data programmatically aswell, although the character limit varies with the method used.

    IIRC ADO.NET throws an exception if you try to bring across a field of more than 911 characters (how’s that for an arbitrary number?). This was causing me real pain until I found the MSDN article and realised what was causing it.

    Luckily for me I had control over the data entry for the system and could limit the input to 911 characters which was not really a limiting factor in the system.

  2. Dale

    If you want to just get the memo data to Excel quick and easy, just copy all the data you want and paste to the worksheet using Paste Special:Text. A lot of times I can manipulate memo quickly and paste them back to Access with no fuss.

  3. mark hennig

    Fantastic! 3 minutes past finding the problem a clear solution has been outlined…thanks

  4. Jacob

    What about copying/pasting from one excel to another? There’s the same 255 character limit. Do you have any advice on what to do about that?

    Thanks in advance!

  5. Ex-cel-pert

    @Jacob:
    before inserting the contents to the destination excel spreadsheet be sure to format the cells first as “Text” instead of “General”. Then insert your copied contents and you will see all characters (>255) are preserved :)

  6. Brian

    If you are doing Totals and “grouping by” in your query, selecting “First” on the memo field instead of “group by” will allow you to export the full contents of the field.

  7. Peter

    I noticed that the problem did not occur when I exported from Access 2013 table to Excel 2013, but happened with a query based on the same table. Found solution: Remove the “unique values” property in the query’s design. Apparent reason: Access cannot compare memo fields to one another to determine the uniqueness that you asked for; so it uses the first 256 or so characters to do the match, and in the process the whole field gets cut down to that size when exported.
    Hope this helps somebody.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>