Problem Importing from an Excel spread sheet to an Access 2003 table.

  • Thread starter Thread starter Casey
  • Start date Start date
C

Casey

Hello,

I am not sure if I should be posting this question here!
Or in Tables and Database Design. I will start here since
this is a question about importing data.

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.

Thank you,

Casey,
 
i put a few zip codes into an Excel 2003 spreadsheet, to test an import. i
was unable to get Excel to show leading zeros, unless i formatted the column
as Text. once i did that, it imported into Access 2003 as text and was fine.
so i'm not sure why it's not working for you, but here's one possible
solution:
open your Excel spreadsheet and Save As a text file, .txt. when you import
the text file into Access, you'll be able to control the data type from the
dialog box. as text; all characters in the field should import as is.
(personally, i always prefer to import .txt rather than .xls whenever
possible, because you can control a .txt import so much better.)
as for adding -0000 later, you can do that with an update query. or perhaps
format the data at the form/report level when you need to see the
extensions, instead of adding all that hard data to the table.

hth
 
Hello Tina,

Thank you for your responce.

Unfortunately changing the .xls to .txt is not an option.
This is part of a process that takes the spread sheets and
imports directly to the tables. I need to make the
changes with in Access.

Casey,
 
Hi Casey

Prefix your first zipcode with an apostrophe ( ' ). Hopefully this tells
Access it's a text instead of a number.

Immanuel Sibero
 
I have not controll over the spread sheets.
Editing the spread sheet in any way is not an option.
From my side of things. I only have controll over the
database.

If it cannot be done from Access that is ok I will have to
go back to project development and tell them. I just need
to know.

And from all of the posts I have received in responce to
this issue. Every one points to editing the excel spread
sheet instead of having control of what access does to
data when access imports data.

Doesn't seem like a very efficient method to change the
spread sheet since it is supposedly in a format that the
database will allow to be imported as long as the field
type in the table for zip code is text. Which it is. And
access is the peace that is making the changes by dropping
the zero's.

Thank you for your responce.

Casey,
 
Based on your requirements you cannot use Excel.
The spreadsheet MUST be properly formatted otherwise it will fail to import
correctly.
Use ASCII delimited files instead. (I prefer | delimited myself.)

Here is some info on Access and Excel to clarify my first comment:
Since Excel is not a database, using "mixed" data types in a column causes a
problem when Access has to "guess" which one to use. Access examines the
first 15 rows (or so) and makes a guess. If you want to "force" Access to
guess a text data type then make the first entry in that column a text
value. The reason you get errors is that the first 15 rows are all numeric
and then the text data shows up after Access has already guessed the data
type to be Numeric.

The way to make a text value in your data of numbers is to include a
character!
e.g. 123A

========================================================
Other good advice from John Nurick:
The Access routine that imports Excel data doesn't allow direct control
over the types of the fields it creates, and often runs into trouble
with Excel columns that contain a mix of numeric and text values.

You can work round this in any of the following ways:

1) create the table yourself with the field types you need, then import
the spreadsheet data. The field names in the table must exactly match
the column headings in Excel.

2) make sure that at least one row near the top of the Excel table
contains values that can only be interpreted as the data types you need
(e.g. text that cannot be interpreted as a number if you want the column
to become a text field). Sometimes the simplest way to do this is to
insert a first row of "dummy" data into Excel just for this, and then
delete it from the Access table once the data has been imported.

3) Access assigns field types on the basis of the data it finds in the
first dozen or so rows of the spreadsheet table. It pays no attention to
cell formats. Sometimes a useful trick is to put an apostrophe ' in
front of numeric values in the cells (e.g. '999): this forces Excel and
Access to treat them as text, but the apostrophe is not displayed in
Excel or imported into Access.

(Just to make things more confusing, the Access applies different rules
when you're linking Excel data rather than importing it. Simplifying
somewhat: when importing, any text value in the first few rows will
cause a field to be imported as text. When linking, any *numeric* value
in the first few rows will cause a field to be linked as numeric even if
all the other values are non-numeric.)

3) Write your own import code using Automation to get the values direct
from the worksheet cells and recordset operations or queries to append
them into your table.

--
Joe Fallon
Access MVP



"I don't have access to the spread sheets!"
 
Hi Casey,

As others have said, it's not possible to do this in one step. It is
possible in two stages or by writing custom VBA code, in any of these
ways:

1) Modify the Excel sheet before import by prefixing an apostrophe ' to
the Zip code field. This can be done manually or under program control
from Access via Automation.

2) Import the data to a temporary table, losing the leading zeros. Then
use an append query to move the data into a "permanent" table, with a
text field for the Zip codes. Use an expression or custom VBA function
in a calculated field in the query to restore the leading zeros.

3) Write VBA code that uses Automation to extract the data from
individual cells in the Excel sheet and append it a record at a time to
the Access table.

Of these, (2) is probably the simplest.
 
Hello Joe,

Your post's in both areas of the formums have been
helpfull.

I will go the route of creating my own import code to make
these match up when importing.

Thank you,

Casey,
 
Hi Joe,

One thing I wanted to know.

Is it possible in Access to make all zip codes display 5
digits with a single command?

See if I import them and they are messed up ok, the
import process it self I don't have much I can do with.

But if I could after the fact change all of the zip codes
to display 5 digits this should work for what I need.

Casey,
 
Hello John,

Is it possible in Access to format all zip codes to
display 5 digits with a single command?

This would get me away from the problem of importing it
correctly. I would just have to apply a change
afterwards.

If there is a way to format a field with in Access after
the fact would work as well. To have the output change
from what ever is in the Zip code field for data to now be
displayed as 5 digits and 5 digits only.

I have the field property data type currently set to text.
I can change it to what ever it needs to be.

Casey,
-----Original Message-----
Hi Casey,

As others have said, it's not possible to do this in one step. It is
possible in two stages or by writing custom VBA code, in any of these
ways:

1) Modify the Excel sheet before import by prefixing an apostrophe ' to
the Zip code field. This can be done manually or under program control
from Access via Automation.

2) Import the data to a temporary table, losing the leading zeros. Then
use an append query to move the data into a "permanent" table, with a
text field for the Zip codes. Use an expression or custom VBA function
in a calculated field in the query to restore the leading zeros.

3) Write VBA code that uses Automation to extract the data from
individual cells in the Excel sheet and append it a record at a time to
the Access table.

Of these, (2) is probably the simplest.



Hello,

I am not sure if I should be posting this question here!
Or in Tables and Database Design. I will start here since
this is a question about importing data.

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.

Thank you,

Casey,
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Hi Casey,

You can format a numeric field to display 5 digits by setting it's
Format property to
00000
, but that won't work on a text field. And if there are any Zip+4 codes
in the text field you won't be able to convert it to a numeric field.



Hello John,

Is it possible in Access to format all zip codes to
display 5 digits with a single command?

This would get me away from the problem of importing it
correctly. I would just have to apply a change
afterwards.

If there is a way to format a field with in Access after
the fact would work as well. To have the output change
from what ever is in the Zip code field for data to now be
displayed as 5 digits and 5 digits only.

I have the field property data type currently set to text.
I can change it to what ever it needs to be.

Casey,
-----Original Message-----
Hi Casey,

As others have said, it's not possible to do this in one step. It is
possible in two stages or by writing custom VBA code, in any of these
ways:

1) Modify the Excel sheet before import by prefixing an apostrophe ' to
the Zip code field. This can be done manually or under program control
from Access via Automation.

2) Import the data to a temporary table, losing the leading zeros. Then
use an append query to move the data into a "permanent" table, with a
text field for the Zip codes. Use an expression or custom VBA function
in a calculated field in the query to restore the leading zeros.

3) Write VBA code that uses Automation to extract the data from
individual cells in the Excel sheet and append it a record at a time to
the Access table.

Of these, (2) is probably the simplest.



Hello,

I am not sure if I should be posting this question here!
Or in Tables and Database Design. I will start here since
this is a question about importing data.

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.

Thank you,

Casey,
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Hi John,

That works for me. None of these are comming in with
anything more than the first five digits of the zip code.

So I don't have to be concerned with the dash and the
trailing zip codes.

Thank you very much.

Casey,

-----Original Message-----
Hi Casey,

You can format a numeric field to display 5 digits by setting it's
Format property to
00000
, but that won't work on a text field. And if there are any Zip+4 codes
in the text field you won't be able to convert it to a numeric field.



Hello John,

Is it possible in Access to format all zip codes to
display 5 digits with a single command?

This would get me away from the problem of importing it
correctly. I would just have to apply a change
afterwards.

If there is a way to format a field with in Access after
the fact would work as well. To have the output change
from what ever is in the Zip code field for data to now be
displayed as 5 digits and 5 digits only.

I have the field property data type currently set to text.
I can change it to what ever it needs to be.

Casey,
-----Original Message-----
Hi Casey,

As others have said, it's not possible to do this in
Click to expand...
one
step. It is
possible in two stages or by writing custom VBA code,
Click to expand...
in
any of these
ways:

1) Modify the Excel sheet before import by prefixing an apostrophe ' to
the Zip code field. This can be done manually or under program control
from Access via Automation.

2) Import the data to a temporary table, losing the leading zeros. Then
use an append query to move the data into a "permanent" table, with a
text field for the Zip codes. Use an expression or
Click to expand...
custom
VBA function
in a calculated field in the query to restore the
Click to expand...
leading
zeros.
3) Write VBA code that uses Automation to extract the data from
individual cells in the Excel sheet and append it a record at a time to
the Access table.

Of these, (2) is probably the simplest.



On Wed, 17 Mar 2004 09:38:36 -0800, "Casey"


Hello,

I am not sure if I should be posting this question here!
Or in Tables and Database Design. I will start here since
this is a question about importing data.

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.

Thank you,

Casey,

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top