none
Question about exporting data from Excel to Access 255 Character Limit RRS feed

Answers

All replies

  • I just tested it and all text was imported of values longer than 255.

    Have you made sure that the Data Type of the relevant fields is Memo (Access 2010 or before) or Long Text (Access 2013 or later)? If they are Text (Access 2010 or before) or Short Text (Access 2013 or later) fields, the text will be truncated.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, April 15, 2016 9:54 PM
  • Hi,

    As Hans Vogelaar MVP has mentioned already,

    please check "Data Type" of the problematic field of Access table.
    If it is "Short Text" (max field size is 255), change it to "Long Text".
      

      

    Regards.
    Friday, April 15, 2016 11:42 PM
  • Thanks guys!  I am using Access 2007 and the field type is Memo, so this doesn't really make sense.  Do you think something could be throwing it off.  It seems almost random.  MANY of the records in this field have more than 255 characters.  About 7 or so, out of over 300, seem to be impacted.

    Any thoughts?

    MY BOOK

    Monday, April 18, 2016 7:10 PM
  • Hi ryguy72,

    >> It seems almost random.  MANY of the records in this field have more than 255 characters.  About 7 or so, out of over 300, seem to be impacted.

    Do you mean some records which more than 255 characters work correctly? If so, I would suggest you test with records part by part to check which records have this issue. Also, I suggest you create a new excel file with simple records which more than 255 characters to check whether this issue still exist.

    If you use an Import function in Access 2007, will the records be truncated?

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, April 19, 2016 3:17 AM
  • So, I dug in and this is what's happening.  Several ranges are being concatenated together.  The field that truncates at 255 chars looks like this:

    !Control = Range("D50").Value & Range("D51").Value & Range("D52").Value & Range("D53").Value & Range("D54").Value & Range("D55").Value & Range("D56").Value & Range("D57").Value & Range("D58").Value

    In Excel, in the Immediate Window, if I check the concatenated value, like this...

    ?Range("D50").Value & Range("D51").Value & Range("D52").Value & Range("D53").Value & Range("D54").Value & Range("D55").Value & Range("D56").Value & Range("D57").Value & Range("D58").Value

    I DO SEE THE ACTUAL VALUE.  In the first instance, it is 305 characters.  Somehow, it is getting truncated to 255 characters when it is being passed to Access.

    Is there a best practice for concatenating cells?  I don't think there is a specific VBA function for concatenating cells.


    MY BOOK

    Tuesday, April 19, 2016 4:18 PM
  • Actually................I think what it comes down to is if you copy/paste, you get this 255 character truncation behavior.  If you create an ODBC connection from Access back to Excel, everything flows through completely.

    MY BOOK

    Tuesday, April 19, 2016 5:17 PM
  • Hi ryguy,

    Do you mean this issue could be resolved by using ODBC connection? If you set concatenated value in one cell, will it be truncated? If not, I suggest you store them in one cell, and then export them to access to check whether them will be truncated.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, April 20, 2016 2:22 AM
  • Exactly, this issue could be resolved by using ODBC connection.

    Thanks everyone.


    MY BOOK

    Wednesday, April 20, 2016 2:42 AM
  • Hi ryguy,

    Thanks for sharing solution, and I suggest you mark your reply as answer to close this thread, then others who run into the same issue would find solution easily.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, April 20, 2016 5:59 AM