Data being lost when imported from Excel to Access tables.

[Guest]

Hello,

I attempted posting this in Importing, Exporting, Linking.
But this may be a question more suited for this thread.

My problem is that when I attempt to import data from an
Excel spread sheet into an Access 2003 table. The first
zeros that I have in a zip code field are not getting
carried over to the access table.

Now I would like to correct this in one shot. I tried
setting up a custom imput mask such as \00000\-00000

This added a zero to the front of all the zip codes.
Not all of my zip code entries start with zero.

I just want to copy over what is actually in my excel
spread sheet.

And perhaps latter automatically add all zeros for the
last five digits.

For right now how ever. All that there is are five
digits.

Some start with zero. Some do not.

I would really appreaciate some help on this. I keep
coming back to this issue over the past couple of weeks.

Currently I am no closer to figuring this out from my
refrence manual.

Making changes on the spread sheet side realy are not an
option. This is something if possible that I need to
correct with in Access.

Thank you,

Casey,

 

[John Vinson]

My problem is that when I attempt to import data from an
Excel spread sheet into an Access 2003 table. The first
zeros that I have in a zip code field are not getting
carried over to the access table.

Access has to guess the appropriate datatype when it imports data from
Excel. Since zipcodes *appear* to be numeric data, it will guess that
the field is of Number type and therefore truncate leading zeros.

Either create the table empty in Access, with a Text datatype for the
zip code, and use File... Get External Data... Link to connect to
Excel in order to run an Append query; or put a "dummy" row at the top
of the spreadsheet with a text value in the zipcode field (such as
"Zip" or "Text!"). You can then delete the dummy record.

 

[Casey]

Hello John,

When I imported the data from Excel I did it as you stated
in the first part of your responce. Still the zero's are
missing. The other option with editing the .xls files is
something i'm not going to be able to do. We have a
process that opens the excel file as it is being created
and then imports the data to the Access database. I cannot
interupt the process this is something that is high volume.

Casey,

And Access still trunkated

 

[John Vinson]

Hello John,

When I imported the data from Excel I did it as you stated
in the first part of your responce. Still the zero's are
missing. The other option with editing the .xls files is
something i'm not going to be able to do. We have a
process that opens the excel file as it is being created
and then imports the data to the Access database. I cannot
interupt the process this is something that is high volume.

Casey,

If the Access Zip field is a Text field, run an UPDATE query updating
the zip to

Right("00000" & [Zip], 5)

If Zip is numeric (not a good idea but if you're importing you may be
stuck with it), set the Format property of the field to

"00000"

This won't store leading zeros but it will display them.

It's vexing but if you can't edit the spreadsheets, that's about the
only ways to do it!

 

[Thank you John]

Thank you John,

This information has been helpfull.

Casey,

-----Original Message-----

Hello John,

When I imported the data from Excel I did it as you stated
in the first part of your responce. Still the zero's are
missing. The other option with editing the .xls files is
something i'm not going to be able to do. We have a
process that opens the excel file as it is being created
and then imports the data to the Access database. I cannot
interupt the process this is something that is high volume.

Casey,

If the Access Zip field is a Text field, run an UPDATE query updating
the zip to

Right("00000" & [Zip], 5)

If Zip is numeric (not a good idea but if you're importing you may be
stuck with it), set the Format property of the field to

"00000"

This won't store leading zeros but it will display them.

It's vexing but if you can't edit the spreadsheets, that's about the
only ways to do it!



.