Importing data from Excel

Hi people!

Can anyone please help me with this problem:

I want to import data from MS Excel which looks like this:

SITE P1 P2 P3
Site1 1 2 3

Into table in Access that would look like this:

SITE P
Site1 1
Site1 2
Site1 3

Help!

OK there are a couple of options.

First are there just 3 columns or did you just list 3 for the example?

How often do you need to do this. Is it something that happens daily, or is this a one time only, or????

The 3 columns were just for example. I will have up to 8 columns is Excel. When I import it to Access I would like to have a table with only 2 colums, for example:

Table in Excel looks like this:

PERSON PET1 PET2 PET3
Person1 Dog Cat Snake
Person2 Hamster
Person3 Bird Dog
...

And I would like for a table in Access to look like this:

PERSON PET
Person1 Dog
Person1 Cat
Person1 Snake
Person2 Hamster
Person3 Bird
Person3 Dog ...

I need this procedure one time only.

P.S. Thank you for your reply

Alright that makes it easier.

First you can't do it as just a simple import. But it's not complicated either.

1. Create a table that has the 2 fields you want.
2. Link or import the excel data
3. Create an insert query like this

INSERT INTO tblPeoplePet (Person, Pet)
SELECT Person, Pet1
FROM tblImport
WHERE Pet1 <> ""

This will insert the people and their first pet into your new table.

4. modify the query for each other pet/column:

INSERT INTO tblPeoplePet (Person, Pet)
SELECT Person, Pet2
FROM tblImport
WHERE Pet2 <> ""

INSERT INTO tblPeoplePet (Person, Pet)
SELECT Person, Pet3
FROM tblImport
WHERE Pet3 <> ""

... etc.

Remember to change the pet/column in BOTH places!

This is a bit brute force, but it's simple and since you only need to do it once it is actually the easiest.

Make sense?

Yep - That makes sense!

Thank you very much! I will try this procedure tomorrow and I'll let you know how it went :-)

I tried the code and it works smoothly!