Solved

How to avoid Type Conversion Error when importing data from Excel into Access

Posted on 2008-10-08
6
1,167 Views
Last Modified: 2010-04-21
I put some data into a spreadsheet for a user to edit.  When they gave me back the edited spreadsheet, I got Type Conversion Errors when I tried to import it back to Excel.

These errors occured on fields that had numbers in them.  For example, I gave the user
F1               F2          F3             F4
ABCDE        60         290000    9000
BCDEF        10         100000    9000

They gave me back

ABCDE        60         290000     9000
BCDEF        60         100000     9000
WERTF       10          290000    9000

When I imported it into Access, the first line imported fine (no changes to this line).
On the 2nd line, I got a Type Conversion Error on F2 and the value "60" didn't load.  
On the 3rd line, I got a Type Conversion Error on F2, F3, and F4.

I need to be able to easily exchange data between Excel and Access without losing valuable data to Type Conversion Errors.  The Access file import wizard looks like it should allow me to specify the field type before importing, but I find that I can only select the first field.  Is there a way to either 1) force Access to ignore any types that come from Excel and treat everything as text or 2) force Excel to enter all data as text?
0
Comment
Question by:monkeybiz12345
[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
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:sandygettings
ID: 22674944
Two suggestions:

1. Post sample Excel workbook to demonstrate the error;
2. Offer more than 50 points for a solution. This one is going to take a bit of work.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22675099
Hi monkeybiz,

When you said "I got Type Conversion Errors when I tried to import it back to Excel"  did you mean to say Access?

What are the data types of the columns that you're trying to import into?

Jim

0
 

Author Comment

by:monkeybiz12345
ID: 22694941
Yes, Jim, I meant to say "Access".

I've prepared several samples for illustration and, so far, am unable to reproduce the problem.  I think the issue may have been the way I created the Excel wrokbook from the Access file originally.

When preparing the sample, I changed all the fields in the Access table to type Text before creating the Excel file.  I've been able to import these back into Access without error, even after editing the data in Excel.  I've asked the user who did the editing to test this for me, in case it has something to do with their copy of Excel or the way they are doing the editing.  

I don't recall changing the Access field types to Text on the original file so that may be my solution.
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 18

Accepted Solution

by:
jmoss111 earned 50 total points
ID: 22698006
That is normally the problem with data missing and type conversion errors. I always import into text data types and if something needs data type conversion I change it once inside access. For a quick and dirty import where i think i'll have problems i insert a row at the beginning of the excel file that is all text,
0
 

Author Closing Comment

by:monkeybiz12345
ID: 31504511
Thanks, Jim.  Looks like Access decides which data type to use based on the data in the first record.  I kinda stumbled on this awhile ago when resorting the data allowed it to import without error.  It didn't register at the time that this was the reason.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22716854
I think its something like 8 rows.
0

Featured Post

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.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

724 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