Merging and transferring databases and content from V2 to a new V3 setup

Hi there.

Thanks for posting.

What I am getting is you have several installations of CMS Builder (varying versions) and want to combine them all into one installation of the current version

Right off the bat, there isn't going to be an "easy" way to do this and we may end up continuing the conversation in our consulting department.

Let me give you a general idea of the work involved, though. This will let you gauge what parts you want to handle on your own and what parts we can help with via consulting.

The general idea will be to install one brand new copy of CMS Builder 3.06, then start copying over the data from your other installations.

There will be two phases.

Phase one will start with using CMS Builder's database backup tool to create backups of each table in each of your installations.

Next, using FTP, download each backup file along with each table's schema file to your local computer.

Next, using FTP again, upload all those files to your new installation and import all the data.

Note: Depending on what versions you are working with, you may need to upgrade them first so you actually have the database backup tool.

And that will be it for the first phase. 

Phase two will involve working with your file uploads. We can discuss that after completing phase one.

Does that all make sense? Let me know any questions.

Hi Ross,

Thanks for that, I think you have the gist of things although I probably didnt explain it well.  My need is not to merge different databases, I should have referred to them as tables. For example I have a single database with around 20 different tables which includes serviced accommodation, a table for self catering accommodation, one for holiday parks one for holiday cottages and a general business table.

As all the tables contain very similar information what I want to do is merge those tables into one new table and separate the various business types via search queries where needed. Unfortunately fields which contain the same type of data across the tables may have different field names such as one may have  Business name, another table may call the same thing company name and perhaps another establishment name.

What I am anticipating I will need to do is some form of import into the new table with field mapping such as importing one table mapping "establishment name" to the new "business name" field. I am sure this will be a straightforward job if not rushed and I assume that all the uploads are contained within a single uploads folder and just need copying over.

Does my understanding come close to the reality?

Thanks in advance.

Thanks for the update.

What I am getting now is you want to take all your existing tables and create one master table with all the data.

That's going to simplify things a bit.

You would still be best to start with a brand new installation of CMS Builder.

Next, you can use our CSV Export plugin on each table to get a CSV of your data.

Then, you would use our CSV Import plugin on your new installation to get the data imported.

The CSV import plugin will let you map fields from the CSV to fields in your database as well as create new fields in your database.

The main thing is that this process will still be time intensive if you have 20+ tables per website.

The learning curve is definitely lower, though.

Once that's all done, we'll need to have a separate conversation on how to import file uploads; that's going to be quite advanced.

I recommend focusing on the data first and then we'll circle back for the uploads

How does that all sound? Let me know any questions.

Thanks!

Hi Ross,

I purchased the two plugins and have managed to merge some of the databases although there will be some tidying up to do its a lot less work then re-entering all the data manually.

As you probably  suspected I noticed that there wasnt a matching field for the uploads section. I did notice that there seems to be a separate table for uploads so assumed I would need to go through each exported table and import again but this time import just the uploads field into the uploads table but there seems to be a number of fields in the new uploads table so came to an abrupt stop :-) 

So you are aware the original  cmsb is a version 2 and the new site is a version 3 however I want to avoid doing an update on the original (live) site in case it breaks anything as there is a lot of bespoke scripting and functionality that I dont want to risk causing a problem with when we are in the middle of a complete rewrite anyway.

If you could advise how we transfer the uploads from the old v2 and import into the v3 with all the images correctly allocated to the correct account that would be great.

Thanks in advance.

Hi.

Thanks for the update.

Glad you got the plugins going.

Getting the images imported next is going to be much more involved.

What I have for you on that is a basic outline of the steps involved. There will likely be changes required and there will be some trial and error.

I imagine the process will take several hours or more depending on the complexity.

Keep in mind that in the forum here, we will be focused on discussion how to do this work.

If you want to move our conversation over to consulting, we can get much more indepth and even do the work ourselves.

Here are the basic outline for now:

1. Using the CSV export plugin, create another export of the first database. At the same time, create a CSV for the upload table of this database.

Note: there isn't a link to the upload section in your interface so you'll need to manually go there via: http://yoursite.com/cmsb/admin.php?menu=uploads

2. Of the two exports you just created, open the one that isn't for the uploads and look for the "num" column. What you need to do here is add "100" in front of each value.

For example, if your nums are:

3
5
6
7
10
122

After you add "100", you'll have:

1003
1005
1006
1007
10010
100122

3. Now open the upload table CSV and add the same "100" to the "num" and "recordNum" column.

4. Still in the upload table CSV, change the value in "tableName" for every record to the name of the table you created in your new installation (the one that you previously just imported everything into).

5. Repeat the first 4 steps for every database you want to import.

6. You can re-import each of the main CSV's (the ones that aren't for your upload tables) into your new CMS Builder again.

7. Create a new text file called "masterUploadsCSV.csv"

8. Open the first upload CSV you created and copy everything into the new "masterUploadsCSV.csv"

9. Open the second upload CSV and copy everything but the very first line. Paste that all into "masterUploadsCSV.csv" at the bottom.

10. Repeat steps 8 and 9 for each of the upload table CSV.

11. When you have completed "masterUploadsCSV.csv" import that into your new CMS Builder.

12. Download all the upload files from your various installations and upload them all to the new CMS Builder.

13. Note: if your sites have any uploads with the same file name, there is going to be an overwriting issue and one of the files will need replaced.

And there we go. Like I said, there will be some trial and error and we can move into consulting if you like.

Let me know any questions or feedback.

Hi Ross,

Thanks for the info, before I start can I clarify something. Because I am merging multiple tables, lets say 4, when I imported and merged the data from the old tables into the single new table in the v3 database I didn't include the num field as I thought that if numbering say started at 01 in each table then I would end up with 4 records with the same num value which I assumed would cause all sorts of issues. I thought that the records would receive a new and unique  num value created by the new table when imported.  Should I have included the num field when importing/merging the old data? as it looks as though the original num value will be used to match the uploads/images to the correct account.

At this stage removing all the imported entries and re-importing would not be a problem as we havent done anything with the new table yet.

Thanks

Steve

HI Steve, 

Yes, the record 'num' is important for associating the records to the uploads.  It can be a bit tricky but one method when merging multiple tables into one with conflicting record numbers is to add a base number to each table.   

So say you have: 

CitiesTableA with records: 1, 2, 3
CitiesTableB with records: 1, 2, 3

You can add 1000 to all the the A table records and 2000 to the B table records so you end up with: 

CitiesTableMerged with records 1001, 1002, 1003, 2001, 2002, 2003.

Note that you need to make sure the number you add is greater than the highest number in all the source tables.  Also you'll need to increment the record numbers for that table in the uploads table as well so everything matches up.

Hope that helps!

You might want to consider renaming the num field to old_num and let the import assign a new num - then, add the prefix (1000, 2000 etc) to the old_num.

This will allow you to get a clean new num and still be able to reference the original num via old_num.