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.

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!

Hi Dave, 

I am looking to start on this import/merge next so just want to clarify I have the whole procedure straight in my head.

I have 5 tables that I want to merge into 1 new table. and the User Accounts table which holds the details of which user is responsible for which listing in each table

I have downloaded the uploads table and can see that there are just short of 6,000 entries so I assume that my renumbering of the num field in each table to be merged would be best started at say 10,000. My intention is as follows

Existing Business Directory table, prefix num field with 10,000 and change the _tableName field to business_advertising
Existing Camp and Caravan Park table, prefix num with 11,000 and change the _tableName field to business_advertising
Existing holiday property table, prefix num with 12,000 and change the _tableName field to business_advertising
Existing self catering table, prefix num with 13,000 and change the _tableName field to business_advertising
Existing serviced accommodation table, prefix num with 14,000 and change the _tableName field to business_advertising

Once done I import all the edited csv files into the new table called Business Advertising

Now the images/uploads table.

As the uploads table contains images from many other tables not just those being imported I was planning to create separate tables for each of the accommodation sections so I end up with 5 upload tables consisting of data cut and pasted from the original uploads file. 

1) I will amend each table by prefixing both the num and recordnum fields with the values outlined above.

2) replace the tablename value in each table with business_advertising (the name of the new table)

 And this is where I become stuck,

a) where do I upload the newly amended uploads tables? Do I import then into the same business_advertising table and if so how/where do I map the fields or do I create a new uploads table.

b) providing the user accounts table was imported with num mapped to num will the records in the business_advertising table be automatically associated with the correct user account? ie the createdBy field value in each table will still match the num field in the user accounts.

c) When I upload the image folder contents, If I dont upload the Thumbs folders will that allow me to re-create the thumbs to the sizes required for the new site design without causing any issues.

d) would I be better when importing the new uploads table to not map the "thumbs" fields if we intend to recreate those to a new format?

e) once we have the new site design finished with all the databases fully populated we will need to transfer everything to the normal host site, will any of what I do above cause any potential issues when moving everything across to a different domain name?

Hopefully clarification to the above will mean that I can not just start but successfully complete the import process.

Thanks in advance for any help provided.

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.