Import Excell

[Guest]

How do you import a wrokbook that contains 7 sheets of data(Each sheet
represents a country).There are fields for customer names, Customer accounts,
Customer center Id, Dates, invoice numbers, amount paid. Do you create
several tables to import these 7 sheets?

 

[Joseph Meehan]

How do you import a wrokbook that contains 7 sheets of data(Each sheet
represents a country).There are fields for customer names, Customer
accounts, Customer center Id, Dates, invoice numbers, amount paid. Do
you create several tables to import these 7 sheets?

You should be normalizing the data after you get it in Access. I suggest
that you import it into a temporary table, add a field for country use an
update query to update that field and then copy the result to another table.
Delete the data from the first table and then go on to the next data using
the same procedure and use an append query to add to the second table you
created.

 

[Guest]

I am just getting my feet wet in this process so bear with me.In a new
database, i will use the file import into a table? I thought since these are
all different data types, they belong in different tables and importing 7
excel sheets of data into one table would cause more of a problem. Does
access allow to import into several tables from a workbook?

 

[John Vinson]

I am just getting my feet wet in this process so bear with me.In a new
database, i will use the file import into a table?

You can; or you can Link to the spreadsheet and run an Append query to
add the data to an existing table.

I thought since these are
all different data types, they belong in different tables and importing 7
excel sheets of data into one table would cause more of a problem.

Perhaps your description was incomplete, but it *sounded* like they
were seven tables with the same set of fields, just concerning
different countries. Does each table have its own distinct set of
fieldnames? Or, as you say in your original post is it in fact the
case that for each table "There are fields for customer names,
Customer accounts, Customer center Id, Dates, invoice numbers, amount
paid"? If so, in Access (or any relational database) the information
should all be in one table, with an additional Country field to allow
selecting the country you want to see.

Does access allow to import into several tables from a workbook?

Yes. You'ld probably be best to do so in VBA code, using the
TransferSpreadsheet method.

John W. Vinson[MVP]

 

[Arvin Meyer [MVP]]

It's not so much data types as entity information (attributes) that goes in
Access tables. For instance:

A customer order goes in an Orders table, not in the Customer table. The
link is accomplished by having the CustomerID as a field in the Orders
table. While not much better than a teaching database, the Northwind sample
database that comes with Access should be able to give you better insight
into database design. For a good book that is aimed at beginners, try:

Database Design for Mere Mortals, by Michael J. Hernandez
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

Sorry for the confusion in description. I have an excell workbook that
contains 7 sheets, each sheet has a name of a country to identify the
transactions orgin and the data on these sheets are the same with account
number,customer name etc. I have not created a datbase yet but i want tranfer
the data from these sheets into a database yet i know the different data
types do not belong in one table. Can the data from 7 sheets be imported
into several tables?

 

[John Vinson]

Sorry for the confusion in description. I have an excell workbook that
contains 7 sheets, each sheet has a name of a country to identify the
transactions orgin and the data on these sheets are the same with account
number,customer name etc. I have not created a datbase yet but i want tranfer
the data from these sheets into a database yet i know the different data
types do not belong in one table. Can the data from 7 sheets be imported
into several tables?

All seven datasheets have an account number.
All seven datasheets have a customer name.
All seven datasheets have... etc.

These are the SAME data types and they belong in ONE table... or
perhaps we're just not communicating!

Again... yes, data from 7 sheets *can* be imported into 7 tables. You
could have a table of account number, customer name, etc. etc. for
China, and a table of account number, customer name, etc. etc. for
Japan, and a table of account number, customer name, etc. etc. for
France. This design makes sense for Excel but it is *simply wrong* for
Access!

WHat do you mean by "data types"? Do you mean that data which
originated in China is of a different type than data (with all the
same fieldnames, text/number/date datatype, etc) that originated in
Japan? If so, please explain!


John W. Vinson[MVP]

 

[Guest]

The excel sheets have the same amount of named columns for account, customer
name, invoice number, etc. I believe that customer details should be in a
customer table and invoice details should be in another table.... etc. I
would like to import the sheets into the respective tables but when i link to
the workbook to do an import, it displays the separate sheets. I would like
to import all information and avoid doing a copy and paste of information
into each table from excel.

 

[John Vinson]

The excel sheets have the same amount of named columns for account, customer
name, invoice number, etc. I believe that customer details should be in a
customer table and invoice details should be in another table.... etc. I
would like to import the sheets into the respective tables but when i link to
the workbook to do an import, it displays the separate sheets. I would like
to import all information and avoid doing a copy and paste of information
into each table from excel.

Ok... that clarifies matters. Sorry I wasn't interpreting your posts
correctly! You're definitely on the right track.

I would suggest a two step process: Create your normalized tables,
empty. Be sure to put unique Indexes on them to prevent addition of
duplicate records - e.g. a unique index on customer LastName,
FirstName, MiddleName, Suffix, and Address (*NOT* just the name!)
would prevent adding the same person with two different new
CustomerID's.

Then - seven times - use File... Get External Data... Link to link to
each spreadsheet in turn. Create Append queries to migrate the data
from the spreadsheet into the normalized tables, and run them in
sequence (you can set up a VBA procedure using the Querydef Execute
method to do so; this will let you trap errors). Run the whole
sequence of append queries for each spreadsheet in turn.

It's going to be tedious, but not so bad as copy and paste!

John W. Vinson[MVP]

 

[Guest]

I have not tried append queries before so before i can do anything i will
have to practice on that. This whole process will take time since i have been
assigned to migrate excel usage to access and i have about three workbooks. I
appreciate your detailed steps and will practice them.Thanks for the support!