[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2008-10-08
6
Medium Priority
?
1,289 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 18

Accepted Solution

by:
jmoss111 earned 200 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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