473,320 Members | 2,020 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
9 1899
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
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
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
Hi5
Many thanks
I will do that and get back to you if any problem.
Cheers!!

Nov 13 '05 #5
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Niyazi | last post by:
Hi, I have to retrieve a data from AS400 DB2 and after working with data I have to export into one of existing Excel file. I can connect into specific library in AS400 DB2 using AS400...
2
by: james cox via SQLMonster.com | last post by:
I don't know if this is possible. However, what i am attempting to do is using C#'s window forms. I open up an excell sheet stored in my windows form. The excel sheet stores names of the stored...
1
by: Steven Stewart | last post by:
I have a user who has been using Excel for a while to keep statistics and print reports. She finds using it cumbersome because of long formulas and a lot of copying and pasting. I have designed...
0
by: franjorge | last post by:
Hi, I have created two stored procedures via VB using this code: sql = "CREATE PROC " & nombre_proc & " AS SELECT *" & _ " From MBM_PUNTOS_SCE_SIN_COINCIDIR_SIEGE_FALTA_PM_NE_" & mes & _ "...
2
seshu
by: seshu | last post by:
hi every body This is seshu i have doubt in mysql i have some date in excell sheet to export that data i have copied all the data into a text file and the wrote this code in...
2
by: glibo | last post by:
I am trying to change the tab name when i open a excell spread sheet on the run time in c#. I changed it in the code: Response.AddHeader("content-disposition","filename=" + sFileName + ".xls");...
1
by: thripurari | last post by:
Hi ! Dear all! I am getting a problem while binding the data source to the ultragrid . pls can any one could help me to bind the excell file to my ultra grid. My Question is: How to bind the...
0
by: beebelbrox | last post by:
Greetings all. Once more I must dip into the font of your collective wisdom and request help: I have been given the task of taking an exsisting Access Query and exporting it to excel. There...
1
Ali Rizwan
by: Ali Rizwan | last post by:
Hi all, I m creating a database. The data for database will fetched from an excell sheet. Now how can i read an excell sheet and update my database with that excell sheet. Or I want to show...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.