Importing data

[chaznsc]

Hello,
We have an address list that someone in the office has compiled over
the years. It is currently in excel consisting of

name
address
city state

name
address
city state

name
address
city state

name
address
city state

Yes, all in a single column. I would like to get this into a database
for all future entries, but can this be done?

Thanks for any guidance.

chaz

 

[Guest]

Hi Chaz,

Is the address and city/state information entered into the same cell as the
name by using the <Alt> <Enter> technique, or is this data in different rows
in the spreadsheet. The method you will need to use depends, in part, on how
your data is currently entered.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[access databaes]

Hi Chaz,

Is the address and city/state information entered into the same cell as the
name by using the <Alt> <Enter> technique, or is this data in different rows
in the spreadsheet. The method you will need to use depends, in part, on how
your data is currently entered.

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________












- Show quoted text -

Hi Tom,
Thanks for the response and my apologies for the double post. My
initial posting took over 12 hours to show up, I thought it had bombed
out on me.

Anyhow, I can have the items on different rows in the excel table:

Name
Company
Address
City
State
Zip

I know this would have been easier to have all this in linear fashion,
but this is where it is today,

chaz

 

[Guest]

Hi Chaz,

Okay, here is how I would approach this situation. There is likely a more
elegant VBA solution that you might get someone from the Excel newsgroup to
post, but the method I outline below will work--it just may be a bit tedious
if you have hundreds of names entered. Do this on a copy of the spreadsheet
only.

Lets suppose that all of this data is in Column A. For example:

A1: Fred Flintstone
A2: Bedrock Mining
A3: 1000 Bedrock Way
A4: Bedrock
A5: State (I don't my Flintstones history good enough!)
A6: 12345
A7:
A8: Barney Rubble
A9: Bedrock Mining
A10: etc.

I'm showing a blank row, 7, in-between names, which you may or may not have.

Enter the following equations into the indicated cells:
B1: =(A2)
C1: =(A3)
D1: =(A4)
E1: =(A5)
F1: =(A6)

Select cells B1:F1 and click on Edit > Copy (or Ctrl C). Place your cursor
into cell B8 and click on Edit > Paste (or Ctrl V). Repeat this process of
pasting into the appropriate target cells, for example B15, B22, B29...

Now, select the entire columns B through F. Copy the contents into the
clipboard. Click into Cell G1. Then click on Edit > Paste Special...
Select Values as the paste option. You should get the same data as shown in
columns B through F, but without the equations. Delete columns B through F.
Delete rows that do not include data in columns B:F. You should now be ready
to import this data into Access. I recommend adding a single quote to a few
of the zip code values, in the first few rows, to help insure that the Access
import wizard treats this column as text.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[access databaes]

Hi Chaz,

Okay, here is how I would approach this situation. There is likely a more
elegant VBA solution that you might get someone from the Excel newsgroup to
post, but the method I outline below will work--it just may be a bit tedious
if you have hundreds of names entered. Do this on a copy of the spreadsheet
only.

Lets suppose that all of this data is in Column A. For example:

A1: Fred Flintstone
A2: Bedrock Mining
A3: 1000 Bedrock Way
A4: Bedrock
A5: State (I don't my Flintstones history good enough!)
A6: 12345
A7:
A8: Barney Rubble
A9: Bedrock Mining
A10: etc.

I'm showing a blank row, 7, in-between names, which you may or may not have.

Enter the following equations into the indicated cells:
B1: =(A2)
C1: =(A3)
D1: =(A4)
E1: =(A5)
F1: =(A6)

Select cells B1:F1 and click on Edit > Copy (or Ctrl C). Place your cursor
into cell B8 and click on Edit > Paste (or Ctrl V). Repeat this process of
pasting into the appropriate target cells, for example B15, B22, B29...

Now, select the entire columns B through F. Copy the contents into the
clipboard. Click into Cell G1. Then click on Edit > Paste Special...
Select Values as the paste option. You should get the same data as shown in
columns B through F, but without the equations. Delete columns B through F.
Delete rows that do not include data in columns B:F. You should now be ready
to import this data intoAccess. I recommend adding a single quote to a few
of the zip code values, in the first few rows, to help insure that theAccess
import wizard treats this column as text.

Tom Wickerath
MicrosoftAccessMVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________







- Show quoted text -

Hello again,
I have since come up with some help that allowed me to rotate my cells
in linear fashion. So now my excel file contains:

First Name, Last Name, Position, Company, Address1, Address2.......so
on and so forth.

I am stuck with a database. Should I use the contacts template in
OFFICE or come up with my own database? The problem is, we will likely
change the features of the database as time goes on. for instance,
adding email address fields, and other assorted uses. So I am unsure
if ACcess is friendly enough to allow us to change or add these
features.

In any event, I really dont know how to import data INTO the contacts
db, assuming we use the contacts template.

chaz

 

[access databaes]

Hi Chaz,

Okay, here is how I would approach this situation. There is likely a more
elegant VBA solution that you might get someone from the Excel newsgroup to
post, but the method I outline below will work--it just may be a bit tedious
if you have hundreds of names entered. Do this on a copy of the spreadsheet
only.

Lets suppose that all of this data is in Column A. For example:

A1: Fred Flintstone
A2: Bedrock Mining
A3: 1000 Bedrock Way
A4: Bedrock
A5: State (I don't my Flintstones history good enough!)
A6: 12345
A7:
A8: Barney Rubble
A9: Bedrock Mining
A10: etc.

I'm showing a blank row, 7, in-between names, which you may or may not have.

Enter the following equations into the indicated cells:
B1: =(A2)
C1: =(A3)
D1: =(A4)
E1: =(A5)
F1: =(A6)

Select cells B1:F1 and click on Edit > Copy (or Ctrl C). Place your cursor
into cell B8 and click on Edit > Paste (or Ctrl V). Repeat this process of
pasting into the appropriate target cells, for example B15, B22, B29...

Now, select the entire columns B through F. Copy the contents into the
clipboard. Click into Cell G1. Then click on Edit > Paste Special...
Select Values as the paste option. You should get the same data as shown in
columns B through F, but without the equations. Delete columns B through F.
Delete rows that do not include data in columns B:F. You should now be ready
to import this data into Access. I recommend adding a single quote to a few
of the zip code values, in the first few rows, to help insure that the Access
import wizard treats this column as text.

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________







- Show quoted text -

Hello again,
I have since come up with some help that allowed me to rotate my cells
in linear fashion. So now my excel file contains:

First Name, Last Name, Position, Company, Address1, Address2.......so
on and so forth.

I am stuck with a database. Should I use the contacts template in
OFFICE or come up with my own database? The problem is, we will likely
change the features of the database as time goes on. for instance,
adding email address fields, and other assorted uses. So I am unsure
if ACcess is friendly enough to allow us to change or add these
features.

In any event, I really dont know how to import data INTO the contacts
db, assuming we use the contacts template.

chaz

 

[access databaes]

Hi Chaz,

Okay, here is how I would approach this situation. There is likely a more
elegant VBA solution that you might get someone from the Excel newsgroup to
post, but the method I outline below will work--it just may be a bit tedious
if you have hundreds of names entered. Do this on a copy of the spreadsheet
only.

Lets suppose that all of this data is in Column A. For example:

A1: Fred Flintstone
A2: Bedrock Mining
A3: 1000 Bedrock Way
A4: Bedrock
A5: State (I don't my Flintstones history good enough!)
A6: 12345
A7:
A8: Barney Rubble
A9: Bedrock Mining
A10: etc.

I'm showing a blank row, 7, in-between names, which you may or may not have.

Enter the following equations into the indicated cells:
B1: =(A2)
C1: =(A3)
D1: =(A4)
E1: =(A5)
F1: =(A6)

Select cells B1:F1 and click on Edit > Copy (or Ctrl C). Place your cursor
into cell B8 and click on Edit > Paste (or Ctrl V). Repeat this process of
pasting into the appropriate target cells, for example B15, B22, B29...

Now, select the entire columns B through F. Copy the contents into the
clipboard. Click into Cell G1. Then click on Edit > Paste Special...
Select Values as the paste option. You should get the same data as shown in
columns B through F, but without the equations. Delete columns B through F.
Delete rows that do not include data in columns B:F. You should now be ready
to import this data into Access. I recommend adding a single quote to a few
of the zip code values, in the first few rows, to help insure that the Access
import wizard treats this column as text.

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________







- Show quoted text -

Hello again,
I have since come up with some help that allowed me to rotate my cells
in linear fashion. So now my excel file contains:

First Name, Last Name, Position, Company, Address1, Address2.......so
on and so forth.

I am stuck with a database. Should I use the contacts template in
OFFICE or come up with my own database? The problem is, we will likely
change the features of the database as time goes on. for instance,
adding email address fields, and other assorted uses. So I am unsure
if ACcess is friendly enough to allow us to change or add these
features.

In any event, I really dont know how to import data INTO the contacts
db, assuming we use the contacts template.

chaz

 

[Guest]

Hi Chaz,

I have since come up with some help that allowed me to rotate my cells
in linear fashion. So now my excel file contains:
First Name, Last Name, Position, Company, Address1, Address2.......

Okay, great. Now might be a good time to decide just how much you want to
normalize your database design. Do you want to include attributes such as
Position and Company in the same table as the people? If the term
normalization is foreign to you, then head on over to this link and start
reading all about database normalization:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

You should spend some time gaining an understanding of database design and
normalization before attempting to build something in Access (or any RDBMS
software for that matter). Don't underestimate the importance of gaining a
good understanding of database design. Brew a good pot of tea or coffee and
enjoy reading!

As a start, you may want to import all of this data into one table, and then
work on splitting it up. Or, you may want to keep it all in one table (not
normalized). You would then have what is commonly known as an "Access
spreadsheet" (not generally a good thing, but certainly easier for beginners
to work with).

Should I use the contacts template in OFFICE or come up with my own database?

That's kind of a loaded question! <smile>. First, I'm not sure which
template you are working with. Here are three that I just found:

Contacts (Access 2007
http://office.microsoft.com/en-us/templates/TC012253431033.aspx?pid=CT101428241033

Contact management database (Access 2003
http://office.microsoft.com/en-us/templates/TC010178391033.aspx?pid=CT101426031033

Personal contact manager (Access 2007
http://office.microsoft.com/en-us/templates/TC102220951033.aspx?pid=CT101428511033

The 2003 version is not exactly normalized all that well, but the Contacts
table appears to map pretty well to the fields that you have indicated in
your spreadsheet. The title field looks to be the same as your position
field. The templates available at Microsoft are not exactly known for great
DB designs. They're intended to get you up and running with something
quickly, and, yes, you can modify them. But, it is very helpful to spend time
understanding DB design before attempting too many modifications.

I have a Word document that you are welcome to download:
http://home.comcast.net/~tutorme2/samples/accesslinks.zip

The first two pages includes lots of information that should be useful to
you as a beginner.
Note: I will be posting a slightly updated copy later on tonight. The
current .zip file includes a Word document last edited on 4/12/2007.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________