Importing Into Access - Type Conversion Failure Message

[Jon R]

Importing an Excel spreadsheet into Access generated an error message
and a table labeled "tbl_SOIVI_2003$_ImportErrors: Table". The table
lists the type conversion errors with the following descriptive data:
Error - Type Conversion Failure
Field - F57
Row - 8
My question is how do I find field F57, Row 8. There are only 52
fields in the Access table (columns in Excel). What location does the
F57, 8 represent?
Jon Rasmussen

 

[Jeff Boyce]

Jon

I'm not sure how Access can decide there are at least 57 fields ... have you
looked in the table definition Access creates for the imported data to see
how many Access actually set up, or are you relying on the 'fact' that your
Excel spreadsheet has only 52?

An error like this may indicate that Access interpreted the field type of
one of your Excel "columns" as one data type (e.g., numeric), when, in fact,
in Excel, row 8 has text data. Take a look at row 8 (or 7 or 9 or ...) in
Excel and see if the data is somehow different there.

A solution to get around this problem is to create a table in Access that
has the correct data types, as YOU know them to be, then import into that
table first. The second step(s) is to then parse the import data into your
more-permanent tables. A lot of folks used to Excel overlook this second
step, assuming that spreadsheet-organized data is good enough for Access.
Sorry, but you don't get the best of Access' features and functions if you
feed it 'sheet data!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Jon R]

Jon

I'm not sure how Access can decide there are at least 57 fields ... have you
looked in the table definition Access creates for the imported data to see
how many Access actually set up, or are you relying on the 'fact' that your
Excel spreadsheet has only 52?

An error like this may indicate that Access interpreted the field type of
one of your Excel "columns" as one data type (e.g., numeric), when, in fact,
in Excel, row 8 has text data. Take a look at row 8 (or 7 or 9 or ...) in
Excel and see if the data is somehow different there.

A solution to get around this problem is to create a table in Access that
has the correct data types, as YOU know them to be, then import into that
table first. The second step(s) is to then parse the import data into your
more-permanent tables. A lot of folks used to Excel overlook this second
step, assuming that spreadsheet-organized data is good enough for Access.
Sorry, but you don't get the best of Access' features and functions if you
feed it 'sheet data!

--
Regards

Jeff Boyce
Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/

Microsoft Registered Partnerhttps://partner.microsoft.com/

Jeff
Thanks. After reading your help I went back to the Excel worksheet
for a closer look. I was importing a named range in an Excel
worksheet. It appears that Access picked up columns outside (to the
right of) of the named range. I want back to the Excel worksheet and
cleared/deleted all data that I did not want to import. After doing
this, the import worked with no type conversion errors. Will you
please tell me how to find the "table definition Access creates for
the imported data" - is this the table in designview? Thanks again.
Jon Rasmussen

 

[Jon R]

I would take a good look at column 57 row 8. Make sure it really is
empty and not just looks empty.

Joseph
I replied to Jeff before I read your help. Your were also right on
with the issue and where the problem was being created. The solution
suggested by your reply was what I did - went back to the Excel
spreadsheet and cleaned out the unneeded columns so that the worksheet
(not a named range) contained only the data I wanted to import.
Thanks.
Jon Rasmussen

 

[Jeff Boyce]

Jon

When you use Access' "import", it has to decide (with some input from you),
how to convert the data you tell it to import. You get to tell Access the
name of the table to import to (whether a new table, or appending to an
existing table).

Before you get to design view for the table, you start in the database
window, which should list all tables in your .mdb file (including 'linked'
tables), except for those designated as hidden and/or system tables (and you
can modify your options to see those, too).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Jon R]

Jon

When you use Access' "import", it has to decide (with some input from you),
how to convert the data you tell it to import. You get to tell Access the
name of the table to import to (whether a new table, or appending to an
existing table).

Before you get to design view for the table, you start in the database
window, which should list all tables in your .mdb file (including 'linked'
tables), except for those designated as hidden and/or system tables (and you
can modify your options to see those, too).

--
Regards

Jeff Boyce
Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/

Microsoft Registered Partnerhttps://partner.microsoft.com/

Jeff
Thank you for your help. I have learned much about importing,
appending, formatting from the discussion help. I cleaned up the data
and its formatting in Excel before importing. It is now importing
into Access with no data conversion errors.
Jon