Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Data Truncation importing Excel spreadsheet into Access 2010
Posted on 2011-09-02
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
2
2-
2
- +2
8 Comments
Active today
>>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.
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.
Active today
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.
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.
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.
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.
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
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
Active today
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 :)
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 :)
Active today
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.
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.
Featured Post
Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month6 days, 6 hours left to enroll
627 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.