Solved

Importing data from Access to MySQL

Posted on 2012-03-29
7
289 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
ID: 37782683
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
ID: 37783586
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
ID: 37784622
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37787532
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
ID: 37788178
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
ID: 37788245
@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
ID: 37788482
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

685 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