Solved

Data Truncation importing Excel spreadsheet into Access 2010

Posted on 2011-09-02
8
2,592 Views
Last Modified: 2016-10-24
Trying to import an excel spreadsheet into a new Access 2010 database.

First I create the fields and make sure they are all memo.

But I get truncation anyway.

See attached.  Why can't I make the memo field bigger than 255 characters?
 MemoSizeInAccess2010.pdf

Thanks.
0
Comment
Question by:gateguard
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36473451
>>Why can't I make the memo field bigger than 255 characters?

It's a fundamental limitation of the product: the Excel import method truncates at 255 characters.

You will have to use VBA code to bypass this.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36473504
What you are seeing in the Access options window reflects two entirely different characteristics of the database.  
The "Default field type" and "Default text field size" are mutually exclusive (they do not relate to each other).  

The "Default field type" is just that.  When you create a new field in a table, it will automatically default to whatever datatype you have selected in this combo box.

The "Default text field size" is the length that will be assigned to text fields whenever you specify that a field is a Text field.  I generally set mine to 50.

As to the truncation of data, Patrick's recommendation to us VBA code is the only way I have found around this shortcoming as well.  The challenge there is making sure that each row in your Excel speadsheet contains a unique field that can be used to iterate through the rows of the spreadsheet to capture your long strings and insert them back into the Access table.

0
 

Author Comment

by:gateguard
ID: 36473624
matthewspatrick, fyed, that makes sense, that it has to be done through VBA code (and thanks fyed for the explanation of the options window).

I am sure both of you are right but is there some link to a Microsoft Technet page (or other page) that definitely says this.

I'm not questioning you, but I'd like to have the source document for my records.

Thanks... and thanks again.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 166 total points
ID: 36473802
http://support.microsoft.com/kb/839785
http://support.microsoft.com/kb/189897
http://social.msdn.microsoft.com/forums/en-US/vsto/thread/4ba977d2-aaf9-4c26-bfa4-59ce3c081098/

It is also *somewhat* of a limit of the sheet
See the Excel help file under: Excel specifications and limits:
<Column width  255 characters >

You can also Google "Excel 255 limit" for many other links to "Verify" this for yourself or your supervisors...
;-)

Jeff
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 167 total points
ID: 36473845
I have to backtrack a bit, for Access 2010 at least.

I just tried this successfully in Access 2010...

1) Created an Excel file with dummy data, and the last column containing up to 500 characters

2) I made extra sure that the very first data row contained 500 characters

3) I imported the data into Access

4) I confirmed that the table design marked the column as type memo, and that a query run against that column returns the correct lengths

So, the moral seems to be, make sure that your very first value contains the proper amount of text :)
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 167 total points
ID: 36473895
Patrick,

You make a great point, but I think Access actually evaluates the first 25 records to determine datatype when creating a new table in Access.  I saw this in an article in Access Advisor a number of years ago, and if I recall, it showed a method to expand that beyond 25 rows.  You would think that when importing to an already existing table, it would simply use the appropriate datatype and do the appropriate type conversion.

It's one of the things I hate about importing from Excel.  Microsoft has decided that they can figure out the datatype of the field better than we can, and gives us no way to define the import specification of an Excel file.
0
 

Author Closing Comment

by:gateguard
ID: 36474515
I ended up creating the table structure in advance, in Access, and on the memo field clearing the @ sign on the format option.  Then the truncation ended.
0
 

Expert Comment

by:Rathan Prithvi
ID: 41856863
Hey  i have some problem Row truncartion error it will Occures in Access so please Advise  What should i do?
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question