Zeros drop when exporting into Access

[EdiebAZ]

I am importing SS# as text field from an Excel 2003 worksheet into Access
2007. I formated the column for SS# in Excell; I "Save-As" Excel 2007; all
column headings match data base; I import, I see the SS# in the correct
format; I append to table and the SS# that begin with zero are dropped. I've
tried formating the SS# field with format and imput mask in table design but
the zero keeps droping. There must be a simple solution. I've tried using the
zero to replace missing character but I made it add 10 numbers instead of
nine. HELP!

 

[John W. Vinson]

I am importing SS# as text field from an Excel 2003 worksheet into Access
2007. I formated the column for SS# in Excell; I "Save-As" Excel 2007; all
column headings match data base; I import, I see the SS# in the correct
format; I append to table and the SS# that begin with zero are dropped. I've
tried formating the SS# field with format and imput mask in table design but
the zero keeps droping. There must be a simple solution. I've tried using the
zero to replace missing character but I made it add 10 numbers instead of
nine. HELP!

What's the datatype of the table field in Access? It should be Text, not
Number.

Excel does not have "strong data typing" so an entry like 000555555 will be
treated as a number, numerically identical to 555555. Access must guess based
on the first few rows of data what datatype is intended, and if the SS# column
in your spreadsheet consists of a bunch of nine digit numbers, it will create
a Number field.

 

[EdiebAZ]

The data type in the Access field is 'text'. When I change the column in
Excel it drops the first zero.

 

[John W. Vinson]

The data type in the Access field is 'text'. When I change the column in
Excel it drops the first zero.

If this is a one-shot operation I'd just import the data, and let it lose the
zeros; then run an update query

UPDATE [tablename] SET [SSN] = Right("000000000" & [SSN], 9) WHERE Len([SSN])
< 9;

 

[Paul Shapiro]

While it doesn't always seem to work, I've had some luck by selecting the
column in Excel, before the import, and formatting the column as text.

Others have suggested that you can coerce the datatype by adding a first row
(beneath the field name row if that exists) that uses alphabetic characters
in any field you want imported as text. If a column should be a date, then
use a date in that "data-typing" row, etc. Then delete that row in Access
after the import.

The data type in the Access field is 'text'. When I change the column in
Excel it drops the first zero.

If this is a one-shot operation I'd just import the data, and let it lose
the
zeros; then run an update query

UPDATE [tablename] SET [SSN] = Right("000000000" & [SSN], 9) WHERE
Len([SSN])
< 9;

 

[a a r o n . k e m p f]

Access isn't a real ETL tool.. it doesn't have enough capabilties to
do what you want to do