By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

Is there any known procedure to move the data from an excell sheet to a well designed DB in Access ?

P: n/a
Hi5
Hi,
I have designed a databasewhich is now able to store all data from my
client's Excel sheet.Now I am looking for a good way to move their data
into this Db.

If I want to do this myself will it be enough to remove referentiol
itegrities from all tables or I need to delete all raltionships?

If I removed the relationships and entered data into the tables then
how should I put back the relationship?

If I don't delete the realtionships, Then access gives me an error some
times key violation and sometimes data integrity, any Idea how to
overcome these?

Is there any known procedure to move the data from an excell sheet to a
well designed DB in Access ,If there is I would love to know the
procedure.

Are there any good tools to do this?

I mean it is like tearing a shirt and changing its size and buttons and
probabley the looks of it but it would be still doing the same job
there must be an automated way to so!!?
Many thanks
Kindest regards

Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
You can include the spreadsheet as a linked table. From there you can
use append queries to copy the data into the corect tables. If your
primary keys are based on autonumbers there should be no problem doing
this.

without seeing your ralationship window and knowing exactly what data
you are trying to move into which tables it is hard to say why you are
getting these errors - can you post a jpg of it and say more about what
you are trying to do?

if you are going to remove relationships, etc make a backup first
because when you get into re-defining primary keys and relationships
things can get messy - specially when autonumbers are involved

Nov 13 '05 #2

P: n/a
Hi5
HI,
I have done exactly what you suggested ,I backed up every thing and I
changed all table so that they all have a autonumber primery key and
data is there now.
The problem now is how to make access understand the fact that clients
are just 10 and there is noneed to keep all 2000 clients names in its
data.
Also It seems now impossible to try and make all the relationships as
they should be because I need a mechanism to make sure which key is
related to which and the keys I am deleteing and/or replacing are
correctly related.
I doubt that it is allowed to post relationships view of the DB in the
groups if it is I will do so.
looking forward to hear from you
Many Thanks

BillCo wrote:
You can include the spreadsheet as a linked table. From there you can
use append queries to copy the data into the corect tables. If your
primary keys are based on autonumbers there should be no problem doing
this.

without seeing your ralationship window and knowing exactly what data
you are trying to move into which tables it is hard to say why you are
getting these errors - can you post a jpg of it and say more about what
you are trying to do?

if you are going to remove relationships, etc make a backup first
because when you get into re-defining primary keys and relationships
things can get messy - specially when autonumbers are involved


Nov 13 '05 #3

P: n/a
if i understand correctly what you are saying, you have a table with
2000 records, and in this table the same 10 client names are repeated?
If this is the case then:
1. Insert a new number field (ClientID) into your table (let's call it
tblMain)
2. Create a client table (tblClients) with the 10 client names and a
primary key (ClientID - autonumber)
3. Use a update queries to update the ClientID number field records in
the table to match the appropriate primary key in the reference table.
-
Update tblMain SET ClientID = 3 WHERE ClientName = "Bob"
Update tblMain SET ClientID = 4 WHERE ClientName = "Andy"
etc.
4. In the relationships window, create a one-to-many join from the
ClientID field in the client table to the ClientID field in the
original table.

Now you have a table with client names, where each client can have
multiple entries in your main table.

Hope I havent completely misunderstood your question!

Nov 13 '05 #4

P: n/a
Hi5
Many thanks
I will do that and get back to you if any problem.
Cheers!!

Nov 13 '05 #5

P: n/a
Hi5
Hi,
Sorry I forgot to tell you that I already have all normalised tables
with data in them including Table client.
and all data are now in the normalised tables except that there are
some autonumber Keys added to those tables.
Many thanks
Kind regards

Nov 13 '05 #6

P: n/a
Hi5
Hi,
Sorry I just looked at my messages and nitced that they ae not that
clear that they should be.
Just to clarify what I meant:
1-Yes you have understood my question very vell thank you.
2-I have done the steps as follows :
back up the DB, Use an append query to get the data into tables,
In oreder for this to work I had to add autonumber Pkeys to all tables.
now I have repated lines of data in the tables in data base.
Is there any thing that I can do now?
Because it seems that you were thinking of the whole data still stored
in one lage table.
Many thanks
Kindest regards

BillCo wrote:
if i understand correctly what you are saying, you have a table with
2000 records, and in this table the same 10 client names are repeated?
If this is the case then:
1. Insert a new number field (ClientID) into your table (let's call it
tblMain)
2. Create a client table (tblClients) with the 10 client names and a
primary key (ClientID - autonumber)
3. Use a update queries to update the ClientID number field records in
the table to match the appropriate primary key in the reference table.
-
Update tblMain SET ClientID = 3 WHERE ClientName = "Bob"
Update tblMain SET ClientID = 4 WHERE ClientName = "Andy"
etc.
4. In the relationships window, create a one-to-many join from the
ClientID field in the client table to the ClientID field in the
original table.

Now you have a table with client names, where each client can have
multiple entries in your main table.

Hope I havent completely misunderstood your question!


Nov 13 '05 #7

P: n/a
You wrote
Hi,
I have designed a databasewhich is now able to store all data from my
client's Excel sheet.Now I am looking for a good way to move their data
into this Db.

If I want to do this myself will it be enough to remove referentiol
itegrities from all tables or I need to delete all raltionships?

I do this sort of thing for a living -- I specialise in data migration
into *big* enterprise systems such as SAP and PeopleSoft. The source
data can be anything from an Excel spreadsheet to an Oracle or DB2
database. Each time I have to design a staging environment in Access and
build all the processes from scratch. It ain't trivial.

The overall process I use is

a) Create a new, separate .mdb
b) Link to the target database tables
c) Create local copies of those tables
d) Turn off all validation (required, zero length string ...) and
relationships
e) Link to or import (depends on circumstances) the data in the source
f) Build a set of queries and or VBA routines to load the data
into the local tables, converting data types as needed
g) Build more queries to validate the data, until I'm sure it obeys
all the rules in the target system
h) Build queries to move the data into the target tables.

The last step needs to be done in referential integrity order, but if
you do everything else correctly the data should just flow straight in.

I ALWAYS set up a VBA routine to run everything in the correct sequence.
This, in turn, usually runs a series of other routines for each step to
make testing easier and the whole process repeatable.

All this said, there are tools out there that make this easier, but they
are expensive and have limitations.
--
Albert Marshall
Marshall Le Botmel Ltd
01242 222017
Nov 13 '05 #8

P: n/a
I'm not sure - what else do you need to do? It seems that you have
successfully imported the information and incorporated it into the
database...

Nov 13 '05 #9

P: n/a
Hi5
Hi,
Many thanks for your sulotions, What I have done is exactly Albert has
described up to: g) but after the f),
I am kind of lost in my thinking of the database datasets,Because I
can't see any binding in the data sets after the duplicates are
gone,How I will be able to make the data base to keep a track of the
related data in other table?
E.G.I have about 2000 rows of data sampled from a bigger dataset
(200000) in eXcel spreadsheet format.In that 2000 rows of data theres
are 10 client names repeated and each of them has a number of
categories connected to it in each row and in the other table I have
each client has a number of characteristics which each of these are to
be realated to a number of codes in the code table.
I would be grateful if you could let me know of any good Source ,Code
or Guide availabe to do this in MSAccess?
As you See there are some good advice in this Posting but there are
always some problems, when I try to follow them,
this might be because I am used to My sql and oracle inwhich everything
is straight forward in code.
I would be grateful for any advise.
Many Thanks to all people who spent time to help
Kindest regards

Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.