Type Conversion Failure

[JDM]

Importing Excel spreadsheet into an Access table. After
import, a [Excel file name]$_ImportErrors table is
generated. Table lists errors, field and row. Error
is "Type Conversion Failure". The entire column in the
spreadsheet is formatted as "text". The database table
field is data type "text". Not all rows in the
spreadsheet column generate the error. No matter what I
do, re-entering text, etc., the error is generated for
these same cells. If I edit a cell that did not generate
an error initially, the next time the spreadsheet is
imported, that cell now generates the same "Type
Conversion Failure" error. At my wits end. Any
thoughts. Thanks.

JDM

 

[Ken Snell]

Although your field is formatted as text and the spreadsheet column is
formatted as text, ACCESS may treat the data as numeric because of the
actual contents of the first 20 or so rows. Are you importing into an
existing table?

Give us some examples of the types of data values that are "failing".

 

[Guest]

-----Original Message-----
Although your field is formatted as text and the spreadsheet column is
formatted as text, ACCESS may treat the data as numeric because of the
actual contents of the first 20 or so rows. Are you importing into an
existing table?

Give us some examples of the types of data values that are "failing".

--
Ken Snell
<MS ACCESS MVP>

Importing Excel spreadsheet into an Access table. After
import, a [Excel file name]$_ImportErrors table is
generated. Table lists errors, field and row. Error
is "Type Conversion Failure". The entire column in the
spreadsheet is formatted as "text". The database table
field is data type "text". Not all rows in the
spreadsheet column generate the error. No matter what I
do, re-entering text, etc., the error is generated for
these same cells. If I edit a cell that did not generate
an error initially, the next time the spreadsheet is
imported, that cell now generates the same "Type
Conversion Failure" error. At my wits end. Any
thoughts. Thanks.

JDM

.
Ken,

Your reply this morning confirms my experimentation.
After "exhaustive" trial and error prior to reading your
reply, I added the letter "a" at the top of all text
columns and a "zero" at the top of all numerical columns
in the spreadsheet. Yes, text columns had numerical
contents in the top cell or cells. The spreadsheet now
imports properly without generating an "error" table.
What is still puzzling is the fact that not all of the
many numerical fields in the "text" column generated
errors, only a few, scattered throughout the height of the
column. When I edited any "good" numerical cells in the
spreadsheet column, they then became bad cells and were
added to the error table on the next importation to
Access. Baffling! Originally I BOTH appended to an
exisitng table and also let the imported spreadsheet
generate its own table. This generated two
different "error" tables that included cells in the
appended version that were not included in the self-
generated version. Way over my head in understanding!

I have spent too much time on getting a solution to this.
Is my work-around the solution, or are there other
suggestions? In Access, I think I can get rid of the line
with "a"s and zeros by only including those lines with
values greater than zero. I will try this today. I thank
you very much for your reply.

JDM

 

[Ken Snell]

Your approach is a workable workaround. Alternatively, you can insert a '
character in front of every value in each cell so that ACCESS will treat it
as text.

--
Ken Snell
<MS ACCESS MVP>

-----Original Message-----
Although your field is formatted as text and the spreadsheet column is
formatted as text, ACCESS may treat the data as numeric because of the
actual contents of the first 20 or so rows. Are you importing into an
existing table?

Give us some examples of the types of data values that are "failing".

--
Ken Snell
<MS ACCESS MVP>

Importing Excel spreadsheet into an Access table. After
import, a [Excel file name]$_ImportErrors table is
generated. Table lists errors, field and row. Error
is "Type Conversion Failure". The entire column in the
spreadsheet is formatted as "text". The database table
field is data type "text". Not all rows in the
spreadsheet column generate the error. No matter what I
do, re-entering text, etc., the error is generated for
these same cells. If I edit a cell that did not generate
an error initially, the next time the spreadsheet is
imported, that cell now generates the same "Type
Conversion Failure" error. At my wits end. Any
thoughts. Thanks.

JDM

.
Ken,

Your reply this morning confirms my experimentation.
After "exhaustive" trial and error prior to reading your
reply, I added the letter "a" at the top of all text
columns and a "zero" at the top of all numerical columns
in the spreadsheet. Yes, text columns had numerical
contents in the top cell or cells. The spreadsheet now
imports properly without generating an "error" table.
What is still puzzling is the fact that not all of the
many numerical fields in the "text" column generated
errors, only a few, scattered throughout the height of the
column. When I edited any "good" numerical cells in the
spreadsheet column, they then became bad cells and were
added to the error table on the next importation to
Access. Baffling! Originally I BOTH appended to an
exisitng table and also let the imported spreadsheet
generate its own table. This generated two
different "error" tables that included cells in the
appended version that were not included in the self-
generated version. Way over my head in understanding!

I have spent too much time on getting a solution to this.
Is my work-around the solution, or are there other
suggestions? In Access, I think I can get rid of the line
with "a"s and zeros by only including those lines with
values greater than zero. I will try this today. I thank
you very much for your reply.

JDM

 

[onedaywhen]

Another suggestion: look at the following registry key for your
machine:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows

I'm not sure what MS Access implicitly does with this setting, but
setting the value to 0 (zero) forces ADO to scan all column values
before choosing the appropriate data type, so it might be worth a try.

Another key that may be relevant to you is:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

--

Your approach is a workable workaround. Alternatively, you can insert a '
character in front of every value in each cell so that ACCESS will treat it
as text.

--
Ken Snell
<MS ACCESS MVP>

-----Original Message-----
Although your field is formatted as text and the spreadsheet column is
formatted as text, ACCESS may treat the data as numeric because of the
actual contents of the first 20 or so rows. Are you importing into an
existing table?

Give us some examples of the types of data values that are "failing".

--
Ken Snell
<MS ACCESS MVP>

Importing Excel spreadsheet into an Access table. After
import, a [Excel file name]$_ImportErrors table is
generated. Table lists errors, field and row. Error
is "Type Conversion Failure". The entire column in the
spreadsheet is formatted as "text". The database table
field is data type "text". Not all rows in the
spreadsheet column generate the error. No matter what I
do, re-entering text, etc., the error is generated for
these same cells. If I edit a cell that did not generate
an error initially, the next time the spreadsheet is
imported, that cell now generates the same "Type
Conversion Failure" error. At my wits end. Any
thoughts. Thanks.

JDM


.
Ken,

Your reply this morning confirms my experimentation.
After "exhaustive" trial and error prior to reading your
reply, I added the letter "a" at the top of all text
columns and a "zero" at the top of all numerical columns
in the spreadsheet. Yes, text columns had numerical
contents in the top cell or cells. The spreadsheet now
imports properly without generating an "error" table.
What is still puzzling is the fact that not all of the
many numerical fields in the "text" column generated
errors, only a few, scattered throughout the height of the
column. When I edited any "good" numerical cells in the
spreadsheet column, they then became bad cells and were
added to the error table on the next importation to
Access. Baffling! Originally I BOTH appended to an
exisitng table and also let the imported spreadsheet
generate its own table. This generated two
different "error" tables that included cells in the
appended version that were not included in the self-
generated version. Way over my head in understanding!

I have spent too much time on getting a solution to this.
Is my work-around the solution, or are there other
suggestions? In Access, I think I can get rid of the line
with "a"s and zeros by only including those lines with
values greater than zero. I will try this today. I thank
you very much for your reply.

JDM