Solved

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

Posted on 2008-10-08
6
1,028 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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