Import from an Excel File

Guide to importing issues (tickets) from an Excel file.

This is still in draft.

We will assume that the Excel file (or files) is obtained from another Helpdesk, or Ticketing system. It is not possible for us to know in advance exactly what format the data in the Excel file will be in and indeed whether each 'issue' is composed of data containing in one or more than one file.

It is common for helpdesk (or ticketing) systems to contain their information spread over a number of difference database tables. Indeed it is good database practice to normalise the data in this way. Each database table would contain information specific to the purpose of the table. For example a table about projects, (or categories) would only contain information about these items. A people (or user) table would contain only information about people. Some tables however would contain information that contains data that would fit into other classification, such as a 'Problem' or 'Issue tale, which would contain not only details of a 'problem' but also possibly details of people who may be working the 'problem' or who identified the problem etc. Rather than store the 'people' information together with the problem record, the 'problem' table would contain 'pointers', often known as foreign keys to a row in the 'people' table.

The Issue Tracker component is a normalised structured tables and uses the 'child' or subs diary tables to contain other types of information accessible via foreign keys.

Databases vary considerably in their design and implementation and each has certain features (or restrictions depending upon ones viewpoint) which they implement in different ways. The most common database that Joomla uses in known as MySQL. and it itself has one specific feature which we are particularly interested in. This is the way in which foreign keys are implemented. Once a foreign key is created the data in the references tables has to exist to maintain database integrity. Unfortunately MySQL does not enable one to temporarily disable the foreign keys, instead requiring one to 'delete' and then 're-create' the foreign keys. This may sound a little technical for those not familiar with database design, but the implication as far as importing data is concerned can be very large.

Most web administrators will not be totally familiar with loading data into a database, and would therefore be unaware of how to delete and recreate database foreign keys so it is necessary to load data into the Issue Tracker tables in a specific order.

The Issue Tracker Design Guide, available on the web site provides details of the table structure and it is this structure that determines the precise order in which data has to be loaded into the different tables.

To load the tables the following order should be used:

`#__it_triggers`
`#__it_chistory`
`#__it_meta`
`#__it_emails`
`#__it_types`
`#__it_priority`
`#__it_roles`
`#__it_status`
`#__it_projects`
`#__it_people`
`#__it_issues`
`#__it_custom_field_group`
`#__it_custom_field`
`#__it_progress`
`#__it_issues_log`
`#__it_attachment`

[See earlier reasons why, since we are assuming that it is not possible, or that one does not know how to disable Foreign keys and then recreate them of course as an alternative.

Some of these tables can safely be ignored as they are not concerned with the general issues. For example we can ignore the #__it_meta table since it contains details of the installed component itself. The #__it_chistory table can be ignored since it contains details of record changes in the database 'captured' by the database triggers, detailed in the #__it_triggers table.

Other tables may be ignored depending upon the data it is desired to import. If one is not interested in loading attachments, the #__it_attachments table does not need to be loaded with data.

Details of the various tables and the database design is provided in the Issue Tracker Design Guide and this article assumes that the reader is familiar with this document.

Issue Types
The first table to populate is the Issue Types table. This table contains the various 'types' of issues that can be recorded. i.e. 'Defect'. 'Documentation', etc. The source system will have (probably)_ several different types and if any of the types is not current present (several are preloaded when the component is installed), then it will be necessary to add them into this table. It is probably best to manually enter the types into the table, since the number of types is likely to be small. Make a note of the issue type id, since this will need to be used in the Excel (CSV) file instead of any current used text.

Issue Priority
This is another effectively 'stand alone table' which contains the various 'priorities' that an issue may assume. i.e. High, Critical, Low etc. Again it the Excel source file there will be some field that records the problem priority. We again need to add any additional entries that are not currently available in the priorities table. Also note the 'id' value since we will need to substitute the id in place of the value in the Excel file.

Issue Status

As with the Issue Types and Issue Priority tables described above, the Status table contains details of the different status values that an issue may use. It is the 'id' value that is stored in the Issue record. If there are additional status types used by the 'originating' system, then these need to be created in this table. Then noting the specific 'id' of the status types used, in the Issue record, these need to be changed/altered in the Issue Excel file record.

Issue Roles

Roles are specific to 'people' and as such do not impact the contents of the 'Issue' record. They do however impact and 'people' records than it may be desirous of loading. Again it is the unique 'id' of the Role records that are used in the 'People record' and Projects table.

Issue Projects

The Projects table (which might be considered a Category equivalent) is a table that may be populated separately. It contains details of the different Projects/Categories that an Issue will belong to. Each project may have a 'default person' to whom any issues in the project are assigned, if specified. If the records in the Excel file has 'assignee' type criteria, then one may choose to populate the required field in the Project record. In many cases it will be easier to leave this particular field blank and populate it after the other records have been loaded.

Issue People

The 'People' table contains the named and details of the people known to the component, whether as specific registered Joomla users, or as 'non-registered' users known only to the component. Populated initially from the Joomla users table, non-registered users may be added manually or automatically when and issue is opened on the front end of the site. If it is desired to add users via the Excel file, then care had to be used to ensure that the 'user names' are unique and do not clash with any current Joomla users or 'non-registered Issue Tracker users. This is effectively a 'stand alone table so should be populated prior to the Issue records from an Excel file.

The "trick" here is to correctly map the record IDs of any existing users in the Joomla user table(s) to the user ids in the migrated issues that will live in #__it_issues (assigned_to-person_id is referencing the userid field, and identified_by is using the id of the #__it_people table field) and #__it_projects (default_assignee) tables. If any 'non-registered' users exist then these need to be created first and them mapped in the same way.

Issue Records

This is by far the most important table. It contains some text fields, but the majority of the fields in this table are 'integer' values which refer to records in other tables to define specific values, expressed in text form. Much has been said earlier in this document about noting the 'id' values of items such as Roles,' Status', 'Types' etc. and it will necessary to edit the Excel Issue records to ensure that integer values are loaded in the appropriate fields so that the records 'import' successfully.

Issue Progress

Progress records are the various steps associated with a recorded 'Issue record'. They are separate from the 'Issue Record' but reference the 'Issue Record' via an internal id record. It is important that the actual Issue records are stored first before any progress records are added.

What about loading attachments?
Attachments involve two separate steps. The first is we need to manually place any attached files into the correct directory upon the web server. Once this is done, we can then populate the 'attachments' table with the required information. This is the last task to perform since the attachments reference the 'issues' table, so provided the issue exists the linkages will be correct.

The attachment "references" live in the #__it_attachments table while the files, themselves, reside separately as files within folders identified by the issue id of the issue record to which they belong. By default attachments are stored in the folder /media/kbase/attachments/</issue_id>.

Note that depending upon the component configuration option this directory may be different.

Go To Top

Joomla! Debug Console

Session

Profile Information

Memory Usage

Database Queries