Avoiding Data Cut-Off in Excel

February 3rd, 2010 by datapig Leave a reply »

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.

Advertisement

9 comments

  1. Gordon says:

    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. JP says:

    You import spam comments into Access? Is that what you call working hard?

  3. datapig says:

    JP: That’s what we in the business call Greekin. I used a website that auto-generates Greekin.

  4. I prefer to pull the data in from Excel (database query), which also retains the memo fields (for as much as Excel can handle, that is).

  5. datapig says:

    Jan Karel: Good point!

  6. Dale says:

    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.

  7. mark hennig says:

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

  8. Jacob says:

    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!

  9. Ex-cel-pert says:

    @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 :)

Leave a Reply