Zeros drop when exporting into Access

E

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!
 
J

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.
 
E

EdiebAZ

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

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;
 
P

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.

John W. Vinson said:
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 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top