?
Solved

How to force Access to scan entire imported file to determine field type?

Posted on 2011-10-21
7
Medium Priority
?
261 Views
Last Modified: 2012-05-12
I am importing Excel worksheets into an existing Access table and am having problems with the text being imported into a memo field being truncated at 255 characters.  I think this is because Access scans the first 25 characters of the imported field to determine the field type and many of the Excel files being imported have short text fields in the first 25 rows so Access determines that it should import the field as a text field rather than a memo field.  I seem to recall there being a way to force Access to scan the entire Excel file during the import to determine field type (not just the first 25 rows), but I can't remember how that was done.  Am I correct in recalling that is possible, and if so, how is it done?
0
Comment
Question by:marknlynn3
  • 3
  • 3
7 Comments
 
LVL 8

Expert Comment

by:Amit Khilnaney
ID: 37009066
Enclosing image from following resource..

You need to select the data type accordingly

For more See this

http://www.addictivetips.com/microsoft-office/access-2010-import-worksheet-from-excel-2010/
specifyfields2.png
0
 

Author Comment

by:marknlynn3
ID: 37009251
I am importing data into an existing table so the field types have already been defined, and they can't be altered with the import wizard.  Access is scanning the first 25 rows during input to determine field types and even though the field it is importing to is defined as a memo field, it determines that since the first 25 rows had text fields of length 255 characters or less that all the records must be less than or equal to 255 characters and it truncates records with more text than that.  I believe there is a Access setting that can be changed so that Access evaluates the entire file when determining field types during import.  That's what I'm searching for.  Otherwise, I will have to move a record with a lot of text to be the first record in each Excel file to be imported, which I would prefer not to have to do for each imported file.
0
 
LVL 8

Assisted Solution

by:Amit Khilnaney
Amit Khilnaney earned 2000 total points
ID: 37009414
Can't say how to do this

but this is by design as per Microsoft

http://support.microsoft.com/kb/839785

Solution seems to be like importing the excel file to a new access database and then importing new access database to existing database (Seems like a solution)
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Accepted Solution

by:
marknlynn3 earned 0 total points
ID: 37012045
I kept searching and found how to do it.  It involves changing the Windows registry.  I have Windows 7 running a 32-bit version of Microsoft Office 2010.  I launched the program RegEdit and navigated to: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel  I changed the TypeGuessRows setting from 8 to 0, which causes the entire Excel file to be scanned to determine the data type of each field rather than just scanning the first 8 rows.  This can slow down the importing of data if you have large files, but certainly speeds up the import process for me compared to having to manually move a record with a large text field to be the first record in each imported file or having to import each file to a new Access table and then merge that with the master table.  On the other hand if you don't feel comfortable changing the registry the other two methods will work well, just a little slower.

Here are some links with more detailed information:

http://www.dailydoseofexcel.com/archives/author/dick-kusleika/

http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/40f62ac7-7a14-44a7-9e38-115fde4f0e66
0
 
LVL 8

Expert Comment

by:Amit Khilnaney
ID: 37012626
marknlynn3: Great Work..

0
 

Author Closing Comment

by:marknlynn3
ID: 37043507
Thanks for looking into this issue.
0
 

Expert Comment

by:rmeltz
ID: 37738180
Many thanks for the post!  Works great now...
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

593 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