PC Review


Reply
Thread Tools Rate Thread

Data being lost when imported from Excel to Access tables.

 
 
Guest
Posts: n/a
 
      17th Mar 2004
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,

 
Reply With Quote
 
 
 
 
John Vinson
Guest
Posts: n/a
 
      17th Mar 2004
On Wed, 17 Mar 2004 13:25:49 -0800,
<(E-Mail Removed)> wrote:

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

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
 
Reply With Quote
 
 
 
 
Casey
Guest
Posts: n/a
 
      18th Mar 2004

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

>-----Original Message-----
>On Wed, 17 Mar 2004 13:25:49 -0800,
><(E-Mail Removed)> wrote:
>
>>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.
>
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
>http://go.compuserve.com/msdevapps?loc=us&access=public
>.
>

 
Reply With Quote
 
John Vinson
Guest
Posts: n/a
 
      18th Mar 2004
On Wed, 17 Mar 2004 17:21:02 -0800, "Casey"
<(E-Mail Removed)> wrote:

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

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

 
Reply With Quote
 
Thank you John
Guest
Posts: n/a
 
      18th Mar 2004
Thank you John,

This information has been helpfull.

Casey,


>-----Original Message-----
>On Wed, 17 Mar 2004 17:21:02 -0800, "Casey"
><(E-Mail Removed)> wrote:
>
>>
>>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!
>
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
>http://go.compuserve.com/msdevapps?loc=us&access=public
>
>.
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Link imported contacts to imported accounts automatically Dools Microsoft Outlook BCM 0 3rd Jul 2008 09:55 AM
detect imported pictures vs. non-imported pictures =?Utf-8?B?VG9ueSBMb2dhbg==?= Microsoft Powerpoint 3 21st Oct 2006 02:36 AM
only a portion of my data is being imported to excel from access =?Utf-8?B?S2lyc3Rp?= Microsoft Excel Programming 3 22nd Sep 2006 12:10 AM
Shared workbook opened in Excel 2002 on a Wk2 server with terminal services & opened Excel 97 on a NT Terminal server - Excel 2002 data being lost (sometimes) =?Utf-8?B?aG5j?= Microsoft Excel Programming 0 13th Mar 2004 12:56 PM
Imported contacts are not imported matt Microsoft Outlook Contacts 1 8th Oct 2003 08:48 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:15 AM.