Importing records from one database into another
7 posts by 2 authors in: Forums > CMS Builder
Last Post: November 2, 2011 (RSS)
By gkornbluth - October 27, 2011 - edited: October 27, 2011
Hi all,
Just want to find out if anyone has an elegant approach to this before I go about spinning my very meager MySQL wheels. I looked through the forum but didn’t come up with anything that seemed to help.
A client of mine holds meetings and exhibitions in venues where they don't have (reliable) internet access. At those events they collect email addresses in an off-line CMSB installation using Wampserver. Those sign-ups are saved in a database on the local server.
They have an identical database on-line (with a different database name) where they collect email addresses on their web site. (same version of CMSB)
Up to now they’ve been managing these email lists separately, but they’d like to be able to import the records from their off-line database into their on-line database and manage them as one.
Of course, since this is an ongoing process, they’d like to check for email addresses that are already in the on-line database and skip importing those records.
I’m assuming that the basic steps involved would be:
1) Backup the off-line database.
2) Upload the backed up off-line database to the on-line server.
3) Check for duplicate e-mails and import the remaining records into the on-line database. (MySQL Console?)
4) Delete the uploaded off-line database backup.
Any coding suggestions would be appreciated.
Thanks,
Jerry Kornbluth
Just want to find out if anyone has an elegant approach to this before I go about spinning my very meager MySQL wheels. I looked through the forum but didn’t come up with anything that seemed to help.
A client of mine holds meetings and exhibitions in venues where they don't have (reliable) internet access. At those events they collect email addresses in an off-line CMSB installation using Wampserver. Those sign-ups are saved in a database on the local server.
They have an identical database on-line (with a different database name) where they collect email addresses on their web site. (same version of CMSB)
Up to now they’ve been managing these email lists separately, but they’d like to be able to import the records from their off-line database into their on-line database and manage them as one.
Of course, since this is an ongoing process, they’d like to check for email addresses that are already in the on-line database and skip importing those records.
I’m assuming that the basic steps involved would be:
1) Backup the off-line database.
2) Upload the backed up off-line database to the on-line server.
3) Check for duplicate e-mails and import the remaining records into the on-line database. (MySQL Console?)
4) Delete the uploaded off-line database backup.
Any coding suggestions would be appreciated.
Thanks,
Jerry Kornbluth
The first CMS Builder reference book is now available on-line!
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [gkornbluth] Importing records from one database into another
By Jason - October 28, 2011
Hi Jerry,
There would be a few ways of doing this. Is the offline database always the most up to date? If so, you could just do a backup and a restore from the offline to the online installation. Of course, this would only work if no other changes were made to the online list (otherwise those changes would be wiped out).
Probably the best solution would be to use the CSV export and Import plugins.
You would export your offline list to a CSV file, then use the importer to import it into the online installation. You can set a unique field (probably the email address) and skip over duplicates without having to worry about any coding or SQL.
Hope this helps
There would be a few ways of doing this. Is the offline database always the most up to date? If so, you could just do a backup and a restore from the offline to the online installation. Of course, this would only work if no other changes were made to the online list (otherwise those changes would be wiped out).
Probably the best solution would be to use the CSV export and Import plugins.
You would export your offline list to a CSV file, then use the importer to import it into the online installation. You can set a unique field (probably the email address) and skip over duplicates without having to worry about any coding or SQL.
Hope this helps
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] Importing records from one database into another
Hi Jason,
Thanks for looking this over.
No, the off line database is not the most up to date. It only has the new sign-ups from recent events, not the complete list.
I thought of the CSV export and import plugins briefly, but thought that there might be a more client friendly, "non-techie" approach that I could employ.
I don't mind trying to pursue that myself but if it starts getting too complex, I'd probably turn to consulting for a quote.
Any suggestions?
Jerry Kornbluth
Thanks for looking this over.
No, the off line database is not the most up to date. It only has the new sign-ups from recent events, not the complete list.
I thought of the CSV export and import plugins briefly, but thought that there might be a more client friendly, "non-techie" approach that I could employ.
I don't mind trying to pursue that myself but if it starts getting too complex, I'd probably turn to consulting for a quote.
Any suggestions?
Jerry Kornbluth
The first CMS Builder reference book is now available on-line!
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [gkornbluth] Importing records from one database into another
By Jason - November 1, 2011
Hi Jerry,
exporting and then importing a file is probably the most straight forward method. Ultimately, the client will probably have to move a file from one place to another in order to complete this.
Another option would be to create a single record section with an upload field. The client can upload the .csv file to that record and save it. You can then write a plugin that will parse the CSV file and import it into the target directory. This is essentially the same process as using the CSV importer, except you don't have to get the client to map fields or anything. The downside to this solution is that the CSV file will always need to be in the same format, the addition of new fields would require a change in the plugin that parses the CSV.
Hope this helps.
exporting and then importing a file is probably the most straight forward method. Ultimately, the client will probably have to move a file from one place to another in order to complete this.
Another option would be to create a single record section with an upload field. The client can upload the .csv file to that record and save it. You can then write a plugin that will parse the CSV file and import it into the target directory. This is essentially the same process as using the CSV importer, except you don't have to get the client to map fields or anything. The downside to this solution is that the CSV file will always need to be in the same format, the addition of new fields would require a change in the plugin that parses the CSV.
Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] Importing records from one database into another
Thanks Jason,
Sounds like a total "one click" solution won't be easy.
I just don't want to give the client the chance to mess things up.
According to Murphy's Law, which I learned many years ago... If anything can go wrong chances are that it will, in the worst possible way, and at the worst possible time.
The simpler (and safer) the better.
If you think that it's possible to figure this out through the forum, I'd prefer it, but if consulting is the better way, let me know and I'll send Ross the specs.
Jerry Kornbluth
Sounds like a total "one click" solution won't be easy.
I just don't want to give the client the chance to mess things up.
According to Murphy's Law, which I learned many years ago... If anything can go wrong chances are that it will, in the worst possible way, and at the worst possible time.
The simpler (and safer) the better.
If you think that it's possible to figure this out through the forum, I'd prefer it, but if consulting is the better way, let me know and I'll send Ross the specs.
Jerry Kornbluth
The first CMS Builder reference book is now available on-line!
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [gkornbluth] Importing records from one database into another
By Jason - November 2, 2011
Hi Jerry,
The "Single Record Section" solution would require the programming of a custom plugin. If you'd like to try it yourself, the idea is to have a plugin with a "record_postsave" hook that would fetch the CSV file uploaded by the client, and parse it into a set of MySQL statements.
If you'd like us to create this for you, please send an email to consulting@interactivetools.com and we can go over the specifics with you.
Thanks
The "Single Record Section" solution would require the programming of a custom plugin. If you'd like to try it yourself, the idea is to have a plugin with a "record_postsave" hook that would fetch the CSV file uploaded by the client, and parse it into a set of MySQL statements.
If you'd like us to create this for you, please send an email to consulting@interactivetools.com and we can go over the specifics with you.
Thanks
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] Importing records from one database into another
Thanks Jason,
I think I'll talk to the client and see if I can go the consulting route
Jerry Kornbluth
I think I'll talk to the client and see if I can go the consulting route
Jerry Kornbluth
The first CMS Builder reference book is now available on-line!
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php