Solved

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

Posted on 2008-10-08
6
1,081 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
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

816 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now