In this article I will be focussing on two areas of the system that users most commonly require data to be exported from and imported to at one time or another, namely User Records & Groups, and Content Items & Categories. Perhaps you're having trouble importing a list of existing clients into the system in order to send out your monthly newsletter, or other business communications to. Similarly, you may be wondering how to import a list of all your staff members, each of which require various levels of access to the system. In these instances the data would need to be imported to the user group and user record tables.
Alternatively, you're possibly uncertain of the correct process to follow in order to update the content structure of your website by adding or removing content categories and content items displayed on its pages. In this circumstance the updated content would need to be imported to the content category and content item tables. It should be noted that, despite my focus on these two data types, every data table in BB2 can be output and input in much the same way, and thus the principles covered here apply to every system module and can be used as a model.
File Format
BB2 data is exported as a CSV file and must be imported in the same file format. CSV stands for Comma Separated Values and is a simple, plain text format that stores database information one record per line, separating each field within that record with a comma. It shall be assumed that you have an editor capable of reading and manipulating .csv files, such as Microsoft Excel or OpenOffice.org Calc.
Where do I Start?
Data tables are imported and exported by means of the 'Data Functions' top-level menu option. The starting point in each case is the same: navigate to the section that corresponds with the data you intend to export or import, click the 'Data Functions' menu link, and then select either 'Export' or 'Import' from the menu list that appears. For instance, if you are intending to import/export user records then go to the user records section by hovering over 'Admin' in the main left-hand menu, and clicking the 'Users' sub-menu link. If you want to import/export content categories then go to the content categories list by hovering over 'Publishing' in the main left-hand menu, clicking the 'Content System' sub-menu link, and clicking the 'Categories List' link under 'Tools'.
Data Table Basics:
Although a basic understanding of how BB2 data is structured and the logic behind this design is not necessary in order to successfully export and import data, it may nonetheless prove useful in achieving first-time, accurate results and avoid painful, time-consuming errors.
Don't get confused between 3 types of table prolific in BB2:
- Data tables: these contain the actual data items such as the user records and content items. Each record/ item is assigned a unique number (id).
Examples: bb_users and bb_content
- Group Tables: these contain the different categories that the data items listed in the data tables can be grouped by. They could be content categories that separate the content displayed on your website into different sections, such 'Home', 'About us', 'News' etc. They could also be user groups ('Sales', 'Marketing', 'Management' etc.) which user records can then be linked to. Each category or group record is also assigned a unique id.
Examples: bb_users_groups and bb_content_categories
- Link Tables: the purpose of these tables is merely to create a relationship between data items found in data tables, and the data item groups found in group tables. This is achieved simply by matching a unique data item id with a unique data item group id. This is what makes it possible for users to link to one or more user groups, and for content items to be linked to one or more content categories.
Examples: bb_users_groups_link and bb_content_categories_link
The most important thing to remember here is that although these three tables work closely together in each section, each table contains a different set of data with its own unique set of id's. As a result you should always be aware which table type your import data is targeted at and never mistakenly import it to one of the other table types.
Exporting
There is no real risk involved in exporting data. In the worst case scenario you export the wrong data and try again. Having said that, getting the correct data out of the system can often be just as important to you as it was once to get it in correctly. The first step is to navigate to the section of the system that correlates most accurately with the data you are after. Exporting user records requires that you go to the 'Users' section (bb_users), whereas exporting user groups requires that you go to the 'Users Tree' view (bb_users_groups). In the same way, to export content items go to the Content List (bb_content), and to export content categories go to the Categories List (bb_content_categories). Once you have found the section of the system related to the data to be exported, click the 'Data Functions' top-level menu option, and then click the 'Export' option in the menu list that appears.
Export Structure
Your first option is to customise the structure of your export, or to export the default structure which will include all fields. The data export screen will display the fields to be exported. To stop a field from being exported uncheck the corresponding check-box located to the right of that field's name.
It is also possible to change the order of the fields/ columns in your export. Fields can be repositioned (this will move the columns in the actual csv file) by clicking and dragging the field to the position that you would like it to appear in.
Field column names can be changed by clicking the name shown after the field name (this will default to the field name). For instance, next to the 'Name' field the text “name as name” appears. To change the header for this column from 'name' to 'Surname', click the text that appears after 'as' and type in the word 'Surname'. This can always be done afterwards when editing the .csv file.
Any field that has linked data can also be exported in the same csv file by clicking on the drop down and selecting one of the linked data fields. Note that additional fields can be added to the export by clicking the [+] icon.
Export Data
Once you are happy with the structure of your export file you can choose which data to export within that chosen structure. In order to have only a defined set or group of data included in the export, provide the criteria necessary to identify/filter that data in the data input boxes found below the field check list. For example, supply the id range of the desired user records or content items etc. If you do not provide any data filter criteria the system will include all data available for the chosen fields.
Saving your Custom Exports
No matter what section you export data from there is the option to save your export field selection as a custom export. This custom export can then be selected in future, instead of having to define all the fields you wish to include/exclude from your export each time you wish to export the same information in a particular structure. This can prove very useful and save a lot of time for regular exports. It should be noted, however, that the custom export only saves the export field structure and not the data criteria specified (Id range, Name, description etc.).
Once you are happy with the field structure of a particular export, click the link at the top of the page, directly above the export field list that says, “Click Here to add a New Ordered List Filter”. Provide a name for your custom export selection in the 'Name' input box and click the 'ADD' button to save it. Once saved, continue with your export by scrolling to the bottom of the page and clicking the 'EXPORT' button.
Whenever you need to use this custom export again, navigate back to the same section you last exported data from and click the 'Export' link in the 'Data Functions' menu list. Then simply select the custom export from the 'New Ordered List Filter' drop down at the top of the export page.
Importing
The stakes are a lot higher when it comes to importing. You want to take all the precautions necessary to help insure current data is not overwritten for the wrong reasons, and/or data is not imported to the incorrect location. In other words, you want to avoid importing the wrong data to the right place, the right data to the wrong place, and, of course, importing the wrong data to the wrong place (which doesn't help much either).
Location, Location, Location.
When importing data it is crucial that you do so from the correct location since the location determines where the data is imported to. So, for instance, attempting to import users from the 'User Groups' section will result in each user record being imported as a user group. Furthermore, if a user record id was specified for any of the records in your import file then the system will treat these as the user group id's and update/overwrite user groups matching those id numbers with the user record details. Please refer again to the section above titled 'Data Table Basics'.
In other words, the system is not going to examine your import file and make a decision based on its content as to which system table or section the data should be imported to. On the contrary, you make the decision yourself by choosing the section from within which to click the 'Data Functions' top-level menu option and run through the import process via the 'Import' link.
Use the source, Luke.
There are a number of good reasons why you should always export the data table you intend to import before attempting the import. To begin with, you may well be anxious to import your data from the correct location but remain uncertain of whether the location you have chosen is indeed the one that corresponds directly with the data you intend to import. Exporting data from this location will immediately show whether it is correct or not, because if the type of data found in the export matches the data you are trying to import then you're in the right place. For example, if you're about to import content items, and, upon performing an export from your chosen location, you find that the exported data lists content categories then you know that you are in the wrong section.
The second reason why it is best practise to model your import file on an exported file of the same data table, is that it insures the structure and column names used in your import are accurate. This eliminates a number of unnecessary errors by relying on a file generated and used by the system itself, rather than a file compiled by human “hands”. Remove the columns you do not intend to use (save for the '_id' column which is always required), and use the shell of the export file as the structure for your import by replacing the export data with your own.
Finally, working from an export is absolutely critical when updating records because the correct id no. for each record must by supplied under the '_id' column for the system to find and update those records with the correct data. An export will list these id's for you next to each record and therefore insure your import matches and updates the correct data.
_id's Please
It is very important to distinguish between two different types of records in an import file. The first record type is one that does not yet exist on the system and which is therefore being imported/added for the first time. The other type of record in an import is one that does already exist on the system and which is intended to edit/update the matching record on the system. One import file may consist entirely of one of these record types, or, more commonly, a mixture of both.
The golden rule to remember is that records in the import file aimed at updating pre-existing records in a system table must have the correct id's provided in the '_id' column. The system uses each unique id number to find the matching records in the table you are importing to and to update those records with the information supplied for each in your import file. Wherever an id number is specified in the '_id' column the system will first attempt to find the matching record and update it. If a record with that id number does not exist then a new record is created with the id specified.
If, on the other hand, you are importing new records that do not yet exist on the system then make sure no id numbers are specified for each of those records in the '_Id' column of your import file. The system will assign each of these records the next available id number when it adds each of the new records to the system table. If, as is most often the case, your import file includes both record types then ensure the above rules are followed according to these types.
Stay cool, Murray. What's the hurry?
One last faithful habit to cultivate; a ritual that has saved many a desperate soul in the dark hours of need that so often follow a reckless moment, is that of backing up data before attempting to manipulate it by import.
BB2 is equipped with a very user-friendly backup module, accessible via the 'Admin' menu item on the system's left-hand menu. Once your system's data has been backed up, it can easily be restored to its former state in the event of an unforeseen import “complication”. For more information on how to use the backup module please read the article titled 'System Backup Module' which can be found in the Bluebox knowledge base (
www.bluebox.co.za/?km).
Another way of creating a backup is to export and save all the data for each of the tables you are intending to manipulate via the import. This will allow you to simply reimport these 'master' copies if the import yields any mildly catastrophic results. Be sure to export all three data table types (data, group, and link tables) for the relevant section just to be entirely certain that you have everything you need to find your way back to the pre-import state of affairs. Refer back to the section above called 'Data Table Basics' if you can't recall the significance of each of these data table types.
The LINKTABLE bypass
This section outlines a very handy short-cut to importing data items (such as user records or content items) and linking them to data groups (such as user groups and content categories) in one import. This is an alternative to the typical two step process of 1) importing the data table, and then 2) importing the link table to link the data records in the data table to the correct groups in the data groups table.
Please note that this short-cut works on the assumption that the relevant groups have already been imported/added to the groups table so that they can merely be linked to the data in the data table via the short-cut process.
The short-cut requires only that you add an additional column to the end of your import file structure called '
LINKTABLE_bb_data_groups_link', where '
bb_data_groups' refers to the relevant group table associated with the data being imported. So, if you are importing user records then the column would be called '
LINKTABLE_bb_users_groups_link', and if you are importing content items then it would be '
LINKTABLE_bb_content_categories_link'.
Below this new column, and for each data record (i.e. user record or content item for example), provide the id no. or the name of the group/category you wish to link that data record to. If you are importing a user called 'Bob' and want him to be linked to the 'Management' user group, then you would put either the text “Management” or the id no. of the management user group (e.g. “10”) beneath the new link table column and on the same line as Bob's user record. The very same logic would apply when attempting to link a content item to a pre-existing content category.
Lastly, data items can also be linked to multiple content categories at once by adding another link table column (e.g. '
LINKTABLE_bb_users_groups_link') for each group link required. In each case a number must be added before the column name corresponding with the number of link table columns used. E.g. '
1LINKTABLE_bb_users_groups_link' for the first link table column, '
2LINKTABLE_bb_users_groups_link' for the second link table column. Remember that these examples are specific to user records, so change them according to the section you are importing to.
