TransferSpreadsheet Type Conversion Failure

[snooka9]

~Office XP~
I've come across a problem when I try to import some Excel s/sheets with
social security numbers using DoCmd.TransferSpreadsheet. I have a table
created with various fields, one being the SS# as a Text field, and I first
run a delete query to 'clear' it before every import. Example:

DoCmd.SetWarnings False
DoCmd.OpenQuery "DELETE EE_Import_Table"
DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, "EE_Import_Table", fileName, True
DoCmd.SetWarnings True

I receive employee data from various sources in an Excel s/sheet which I
import into Access. 99% if the time it works fine but on occasion It will
create the "Sheet1$_ImportErrors" table with the error "Type Conversion
Failure" for some SS#s.

When I get these troublesome files I've tried formatting the SS# column in
Excel in various ways (other than 'Number' because then leading zeros are
dropped from some SS#s) and I can't seem to find a way to correct the issue.
Access will just keep importing those strange SS#s as blank and creating the
"Sheet1$_ImportErrors" table no matter what format I choose (or so it seems).

Any ideas on why this is happening and what I can do to alleviate the problem?

Thanks in advance.
..

 

[Alex Dybenko]

Hi,
try to import first in a temporary table, with all text fields with enough
length, then check data for consistency and then append to main table using
append query. BTW, SS# is not a good name for a field, better use SSNum

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[snooka9]

Sorry, I'm actually using SocialSecurityNumber for the field name, I was just
typing SS# for shorthand...

Its not really a problem with the import truncating data or anything like
that, its more that somehow the SS# field in Excel seem to hold different
data types in some but not all cells...like some are Numbers and some are
Text. I did create a table with 1 SSNum field that was Text(255) and then
copied the SSN column from Excel to a new s/sheet so that all I was importing
was the SSN column and I still get the same problem.

Like I said previously (in less detail), I get s/sheets from various
employers with their employee's data (name, ssn, address, etc...) so I can't
really control the initial creation of the file. Most of the time I get the
file and it imports fine. On some occasions though when I import there are a
few records (not all) that come in with blank SS# and the
"Sheet1$_ImportErrors" table is created with the error "Type Conversion
Failure" for those SS#s. When I open the Excel file and go to those
employees it will show the little green triangle in the upper left corner of
the cell and upon clicking the cell's warning sign it says its a "Number
Stored as Text"...like the user edited those SS#s for some reason or another.

I currently have one of these files where there are 121 employees and 5 of
them import with blank SS#. These 5 people coincidentally have a SS# that
starts with a 0 so I assume the original file creater converted the field to
text so that the leading zeros stayed intact. These 5 SS#s also have the
green triangle warning, which is in fact necessary. Even if I format the
entire column to Text I get the same results upon import.

I can't really think of anything else I can do to the Excel file to get the
SSN to import. Any help would be greatly appreciated.

Thanks.
..

==================================================