Solved

Importing data from Access to MySQL

Posted on 2012-03-29
7
280 Views
Last Modified: 2012-03-30
Hello experts!
At work we have been using an MS Access database to track employee hours. This database was build in Access 95 in 1996. I have been tasked with creating a new system that isn't inherently internal (meaning our other offices would not need to remote in to use it). I've decided, based on my strengths, to use PHP / MySQL to do this.

Now the first problem is trying to copy all the data from Access to MySQL so we don't have to continue to maintain the old system (if we can avoid it). I'm unsure the best way to do this as I'm not very versed in Access and the queries running it aren't following general SQL standards. To make matters worse, the Access database wasn't even developed to follow 1NF so I can't in good conscience use the same schema as Access. Even worse there are about 70 tables in the Access db, however some of them are empty, and there are series of TableName1 TableName2 etc so I'm not even sure which ones are being used! Information is duplicated in so many places I can't follow the logic of the schema (if there is any)

My question to you guys is this: What is the best way to copy this data into the new system? How would you go about analyzing an older DB like this to make sure I'm copying over the correct current data?
0
Comment
Question by:Derokorian
  • 3
  • 2
  • 2
7 Comments
 
LVL 53

Expert Comment

by:COBOLdinosaur
Comment Utility
You should be able to export the access to a .csv file.  That is importable by my sql.  Use phpmyadmin to experiment with the option settings and experiment.  


Cd&
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Well if the data is not normalized, then you can import it, but you still have to "Fix" it.

There is no set way to "fix" un-normalized data.
Fixing un-normalized data will require *You* to understand what this data was trying to represent.

For example, a separate custom system will be required to get the DB from "0NF" to 1NF.
Then another to go from 1NF to 2NF, then another for 2NF to 3NF...etc
Not counting all the exceptions and possible inconsistencies in the data.

In other words, with 70 tables, there are no easy answers here.

Let's see what other Experts may contribute.
0
 
LVL 10

Author Comment

by:Derokorian
Comment Utility
My apologies for not returning sooner, I got absolutely swamped with work today.

@COBOLdinosaur The problem isn't the simple export, and the import, but rather the step in between where I have to alter the data from 0NF to 3NF

I know its going to require figuring out the data, I'm just not sure how to do that. I can't seem to figure out what tables are actually being used. For example there are 7 different tables that hold the similar employee information, with as few as 16 rows to as many 78. The queries are just mind boggling puzzling.

Another question is should I just pretend Access doesn't exist until after I get the new application working and then try to figure out how to the Access data fit into the new system or should I keep the Access data in mind in developing the new system?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Did you read my post above...
http://www.experts-exchange.com/Database/MySQL/Q_27654283.html#a37783586
This will not be easy.

Also not that we don't have any access to your data so this makes it all the more difficult.

How much do you know about Normalization...?
0
 
LVL 53

Accepted Solution

by:
COBOLdinosaur earned 500 total points
Comment Utility
Considering the amount of duplicate data, I would develop the new application and the DB design to support it.  Treat the access tables a a data store.  Testing the new application an db will required a small sample of the data and in developing that test data from the data store you will have an understanding of what kind of conversions will be necessary, and with the testing you will be less likely to make a mistake for the major conversion.

You may find that in some cases you bring the raw data into temporary mysql tables and then use mysql facilities to do the conversion into the live database.  As already said this is not going to be an easy conversion.

Cd&
0
 
LVL 10

Author Comment

by:Derokorian
Comment Utility
@boag I know quite a bit about normalization I like to think. Its kind of a pet peeve of mine. In fact my DBA friend tells me all my projects have followed 3NF completely. And since I design my DBs before I start trying to work with any data, I'm just having a hard time following the so called "logic" of this older database. Was hoping there was kind of a general solution that would need modification to fit my needs (like analyze each table, and  fix columns with multiple values into their own tables, or whatever)

@COBOL So basically I shouldn't build a freestanding system and try to make the old data fit, but rather somewhere in between? I didn't think about using temporary tables in between. That will make it much easier.

Last question: I thought about duplicating the Access DB to test on, such that I remove tables with similar names and test if it still works or not. Does this sound like an ok approach to at least try to limit the amount of redundant data I work with? I worry I may not test every aspect and may lose real data.
0
 
LVL 53

Assisted Solution

by:COBOLdinosaur
COBOLdinosaur earned 500 total points
Comment Utility
Yes it makes some sense to run a test series on the old datasets, and see if you can remove some of the redundancy.  Anything that reduces the number of tables is worth trying.  The thing is you want to run a test series first to give you a baseline of results.  Columns that looks redundant might not have all the same same values; that is an inherent risk with duplicate data in any system.  Sometimes the same column in two different tables are out of synch.

Cd&
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now