?
Solved

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

Posted on 2008-10-08
6
Medium Priority
?
1,388 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

807 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