Urgent: Is MySQL the best tool to integrate data?

[vg1]

Hi everyone,

I'm looking for a way to integrate data from different databases (Access, Goldmine) or spreadsheets but the data is going to come from different companies and they have used different fields.

Is there a way to set up a MySQL open source database to integrate data with different fields? Failing that, if I do the work to add the relevant data is there a way to set some security so that other people can view and copy the data but not amend a record?

It's basically to set up a way for information to be transferred from several smaller companies to us and vice versa so that we hold a central master database. Can anyone advise on a tool that is suitable for this or a file format that is best to work with. (Should we ask everyone to export their data into spreadsheets or csv files?) Is it possible to set up the database so that the smaller companies are restricted to what they see, for example, their own postcode region only?

Any advice welcomed...

Regards,

[wiebe81]

well I'm not that experienced, so don't take my advise for granted.

From what I know, It kind of depends really just how different the data is stored, if it's only goldmine and such, then PHP and SQL would suffice.

Having said that however, the best lingo that supports a huge variety is xml (though only accurate on windows related stuff, if you happen to have company that is unix based, even then you could be in some trouble).

There are a lot of xml tools, that can store small quantities of data and convert it. It is especially welcome if you're working with spreadsheets. I know there are some free tools on the net available that can convert CSV and DIF to XML and then with PHP you can get the XML to a database, if I'm correct. As you know with excel you can easily convert worksheets, simply by saving it otherwise, though you may get some problems with some formats. I know w3schools has a tutorial on how to php to xml. I don't know about the opposite way, so you have to check that.

It is a cumbersome process, but with a bit of luck on what kind of data you get and finding the right tools, it can save you a lot of time, especially if it avoids re-inputting some data manually, or developing a converter of your own.

But again like I said before, I have no experience on these things whatsoever, so let just say I hope this post helps, though I'm not too sure about it.