Populating Databse

[News]

I have just recently re-designed a Database that I inherited.
The old had Major problems, one table, 500 Fields.
It was too confusing, for me anyway, to fix.
I instead designed a new one, many of the same fields, but naturally, with
many more relating tables and different field names than the old one (i.e:
Old- [Last Name] New-[txtLastName]

My question is:
What is the best method to Import the data from the old DB to the new
database?
I was thinking exporting from old in csv file and then importing to new (am
I totally off?)

Issues:
Old was mostly Text format, even Date Fields were written in text.
New one using Date/Time (Medium Date). Same with SSN and so on.
Should expect a decent amount of errors?
Any Help would be appreciated.

 

[tina]

well, there's not much point in converting table data to a text file, then
re-importing it to a table. suggest you either link or import the "old"
table into the new database. then use Append queries to append the data into
the proper tables. since you're splitting 500 fields worth of data into
multiple tables, you'll need to put considerable thought into the process.
as a *very* general guide: in populating the tables, you have to follow the
relationship hierarchy - populate parent tables before child tables.

hth

 

[Tim Ferguson]

What is the best method to Import the data from the old DB to the new
database?

The Access query designer... there are not likely to be any off the shelf
converters for one random mess to another, ahem, less random offering.
You'll need lots of normalising queries like:

INSERT INTO Doctors(FName, LName, Address)
SELECT UNIQUE
Doctor_First_Name,
Doctor_Last_Name,
Doctor_Addr1 & CHR(13) & CHR(10) & Doctor_Addr2
FROM OldRandomMess

but even then you'll need to go through the new records weeding out
duplicates like

"Eric S.", "Hartley", "St John's Surgery"
"Eric S", "Hartley", "St John's Surgery"
"Eric", "Hartley", "St John's Surgery"
"Eric S", "Hartly", "St John's Surgery"
"Eric S", "Hartley", "St Johns Surgery"

....and those are just tbe obvious ones! After an hour or two of this,
you'll understand the roots of R theory (as if you didn't already).

I was thinking exporting from old in csv file and then importing to
new (am I totally off?)

Don't think the format matters at all. Know your data really well; clean
it as much as you can before doing any transfers; write the logic down on
a piece (or pieces) of paper before starting anything. If you get good at
this, there is a career in it -- there is a whole branch of IT
specialists that do nothing but data cleaning and migration.

Best of luck


Tim F

 

[accessquestions]

cool. i dont plan on migrating for a few more weeks. You will most likely
see me again !
thanks.

 

[John Vinson]

I have just recently re-designed a Database that I inherited.
The old had Major problems, one table, 500 Fields.

Well, that wasn't an Access database for sure! (limited to 255 fields
in any one table). And good on ya for redesigning it - that's just
what it needed.

It was too confusing, for me anyway, to fix.
I instead designed a new one, many of the same fields, but naturally, with
many more relating tables and different field names than the old one (i.e:
Old- [Last Name] New-[txtLastName]

My question is:
What is the best method to Import the data from the old DB to the new
database?
I was thinking exporting from old in csv file and then importing to new (am
I totally off?)

I'd suggest using File... Get External Data... Link (rather than
Import) to connect to the monster table (what was it, two one-to-one
related tables??), and running Append queries.

Issues:
Old was mostly Text format, even Date Fields were written in text.
New one using Date/Time (Medium Date). Same with SSN and so on.

SSN *SHOULD* be text, as should telephone numbers, zipcodes, and any
other identifiers which just happen to consist of digits. You'll never
ever be doing arithmatic with SSN or zipcode values - just use Text(9)
for SSN, etc.

Should expect a decent amount of errors?

Depends on whether you had angels, human beings, or demons doing data
entry in the old database. Angels will have made very few or no
errors. The only difference between the human and the demonic errors
is that most of the demonic errors will look perfectly REASONABLE and
be very hard to catch. <wry grin>

Yes. You'll have errors. You may want to (at least try to) fix some of
them up first. You can run a Query on your table such as

SELECT thisfield, thatfield, datefield1, datefield2, datefield3
FROM wideflat
WHERE IsDate([Datefield1])=False
OR IsDate([Datefield2])=False
OR IsDate([Datefield3])=False;

to identify records which will cause errors when appending data into a
date field. Alternatively, you can use the Append query; rather than
appending a dubious quality text date into your new datefield you
could use an expression like

AppDate: IIF(IsDate([datefield]), [datefield], #12/31/9999#)

to append some clearly unreasonable date into the datefield (which you
can then fix up manually).


John W. Vinson[MVP]

 

[accessquestions]

Awesome advice.......Thanks a bunch
When it comes time, im going to give each a go.
There was actually two mother tables, but the were no relationships.

VL

I have just recently re-designed a Database that I inherited.
The old had Major problems, one table, 500 Fields.

Well, that wasn't an Access database for sure! (limited to 255 fields
in any one table). And good on ya for redesigning it - that's just
what it needed.

It was too confusing, for me anyway, to fix.
I instead designed a new one, many of the same fields, but naturally, with
many more relating tables and different field names than the old one (i.e:
Old- [Last Name] New-[txtLastName]

My question is:
What is the best method to Import the data from the old DB to the new
database?
I was thinking exporting from old in csv file and then importing to new
(am
I totally off?)

I'd suggest using File... Get External Data... Link (rather than
Import) to connect to the monster table (what was it, two one-to-one
related tables??), and running Append queries.

Issues:
Old was mostly Text format, even Date Fields were written in text.
New one using Date/Time (Medium Date). Same with SSN and so on.

SSN *SHOULD* be text, as should telephone numbers, zipcodes, and any
other identifiers which just happen to consist of digits. You'll never
ever be doing arithmatic with SSN or zipcode values - just use Text(9)
for SSN, etc.

Should expect a decent amount of errors?

Depends on whether you had angels, human beings, or demons doing data
entry in the old database. Angels will have made very few or no
errors. The only difference between the human and the demonic errors
is that most of the demonic errors will look perfectly REASONABLE and
be very hard to catch. <wry grin>

Yes. You'll have errors. You may want to (at least try to) fix some of
them up first. You can run a Query on your table such as

SELECT thisfield, thatfield, datefield1, datefield2, datefield3
FROM wideflat
WHERE IsDate([Datefield1])=False
OR IsDate([Datefield2])=False
OR IsDate([Datefield3])=False;

to identify records which will cause errors when appending data into a
date field. Alternatively, you can use the Append query; rather than
appending a dubious quality text date into your new datefield you
could use an expression like

AppDate: IIF(IsDate([datefield]), [datefield], #12/31/9999#)

to append some clearly unreasonable date into the datefield (which you
can then fix up manually).


John W. Vinson[MVP]

 

[accessquestions]

Question:
How do go about using the GetExternalData when the table youre importing to
has a Primary Key set to AutoNumber?

I have just recently re-designed a Database that I inherited.
The old had Major problems, one table, 500 Fields.

Well, that wasn't an Access database for sure! (limited to 255 fields
in any one table). And good on ya for redesigning it - that's just
what it needed.

It was too confusing, for me anyway, to fix.
I instead designed a new one, many of the same fields, but naturally, with
many more relating tables and different field names than the old one (i.e:
Old- [Last Name] New-[txtLastName]

My question is:
What is the best method to Import the data from the old DB to the new
database?
I was thinking exporting from old in csv file and then importing to new
(am
I totally off?)

I'd suggest using File... Get External Data... Link (rather than
Import) to connect to the monster table (what was it, two one-to-one
related tables??), and running Append queries.

Issues:
Old was mostly Text format, even Date Fields were written in text.
New one using Date/Time (Medium Date). Same with SSN and so on.

SSN *SHOULD* be text, as should telephone numbers, zipcodes, and any
other identifiers which just happen to consist of digits. You'll never
ever be doing arithmatic with SSN or zipcode values - just use Text(9)
for SSN, etc.

Should expect a decent amount of errors?

Depends on whether you had angels, human beings, or demons doing data
entry in the old database. Angels will have made very few or no
errors. The only difference between the human and the demonic errors
is that most of the demonic errors will look perfectly REASONABLE and
be very hard to catch. <wry grin>

Yes. You'll have errors. You may want to (at least try to) fix some of
them up first. You can run a Query on your table such as

SELECT thisfield, thatfield, datefield1, datefield2, datefield3
FROM wideflat
WHERE IsDate([Datefield1])=False
OR IsDate([Datefield2])=False
OR IsDate([Datefield3])=False;

to identify records which will cause errors when appending data into a
date field. Alternatively, you can use the Append query; rather than
appending a dubious quality text date into your new datefield you
could use an expression like

AppDate: IIF(IsDate([datefield]), [datefield], #12/31/9999#)

to append some clearly unreasonable date into the datefield (which you
can then fix up manually).


John W. Vinson[MVP]

 

[John Vinson]

Question:
How do go about using the GetExternalData when the table youre importing to
has a Primary Key set to AutoNumber?

Just do Get External Data... Link to LINK to the existing table, as
is. Then run an Append query (or in this case, many!) appending all of
the fields EXCEPT the autonumber.

Do you have some sort of primary key (one or up to ten fields) in the
monster tables? This kind of data migration project can cause
excessive consumption of headache remedies; you must run the append
queries in the right order (lookup tables first, one-side tables next,
child tables after them, grandchild tables next... oops, left out a
critical lookup table!!!), and often must first populate a table with
an Autonumber primary key and then use an Append query joining the
original table to the new "one" side table to pick up the primary key
autonumber value, in order to populate the foreign key field.

I've done several such projects... and can't say that any of them were
enjoyable, in any way other than that it feels so good to be DONE with
it (sort of like it feels good to stop banging your head on the wall).

John W. Vinson[MVP]