Excel Spreadsheet for Managing Large Helpdesk Category Systems

Helpdesk Solution comes with a built-in administrative tool that allows administrators to create and edit Categories. When just a few categories need to be added or adjusted, the built-in Helpdesk edit tool works well; however, when large lists of Categories need to be added or updated, it is sometimes easier to use an Excel spreadsheet to make all of the required changes.

Figure 1

Click to view.

Data can be easily copied between the Excel spreadsheet and the Helpdesk Solution through the Admin\Import Admin Data and the Admin\Export Admin Data menu options.

The attached spreadsheet is an example of an external tool that can help you quickly make changes to Helpdesk categories. This spreadsheet allows for the entry and editing of categories within the white background area and automatically builds the XML file in the blue section on the right. Once the changes are completed, you simply select the entries in the blue column, copy the information to the clipboard and then paste them into the Admin\Import Admin Data screen in Helpdesk Solution.

Figure 2

Click to view.

Categories use a hierarchical structure following a parent-child relationship. Main topic categories can be created with similar or same topics created as levels below.

The example shows a category parent main topic called Break-Fix, followed by two additional child topic categories: Copier and Desktop. The Desktop category needed additional subtopics to define all the elements of the desktop; thus, child subtopics were created. There are several rules that you must follow in order for the updated categories to properly load into the Helpdesk Solution.

1. Each line in the spreadsheet must contain either the word 'active' or 'inactive' for each catalog entry. An 'active' category is one in which new tickets entered into Helpdesk can be selected. An 'inactive' category status will not allow a new ticket to be assigned to the category because the inactive categories are not presented to the user as a selection option. The user will be forced to pick only active categories. Note that it is not possible to delete categories because there is a chance that the category has been used in the past, and, therefore, it still needs to be available for historical reporting purposes. Also note that if you were to delete a line in the spreadsheet, it will have no effect to the Helpdesk category listing when the update list is moved back. The reason is that the Admin\Import Admin Data process will only update category status or add new categories that you have added to this spreadsheet. If you have a spelling error in an existing category, the only action that can be taken is to set the status to 'inactive' for the incorrect category and add a new category with the correct spelling.
2. Note that each major category must be listed in the spreadsheet once by itself. (No sub-categories are listed on the same line. See line 7 in the spreadsheet example.) In a similar fashion, as sub-categories are added, the major category along with the sub-category 1 must be listed alone (see lines 8, 9, 15, 20, 23, 26, 28, etc.) before the sub-category 2 and sub-category 3 entries are made. Also, the same pattern must be followed by setting up a sub-category 2 entry by itself before setting up a sub-category level 3 entry. (See lines 42 and 43 as an example.)

Figure 3

Click to view.

It is possible to enter up to 2,000 categories into Helpdesk. This spreadsheet is set up to go to 500, but if you want to add more categories, simply copy the formulas into all of the cells that you need to use.

Once you have completed adding or editing this list, one final step is needed before you can copy and paste the XML output into Helpdesk. There are two XML commands that need to be added to the end of the XML Output. In this example, cell F54 must have the value </Categories> added and cell F55 must have the value </Helpdesk> added. See below.

Figure 4

Click to view.

Now, it's really a matter of selecting Cell F5 through Cell F55 and copying the XML Output from the spreadsheet.

Next, go to the Helpdesk Admin\Import Admin Data menu where you will see the following screen.

Note that in the example the spreadsheet was sorted by column B as the primary, with C, D, and E as the secondary sorts using the Excel Data\Sort capabilities. Sorting this list will help you see the overall structure of the Category tree, spot errors, and make changes. However, because of the way that Helpdesk works, the sorting order of the spreadsheet will not carry over to Helpdesk. The sorting order used during ticket entry or editing is based on the order that the categories were originally saved in the Helpdesk database.

Figure 5

Click to view.

Now, paste the clipboard contents into the Import Admin Data screen.

Figure 6

Click to view.

Now, click the Import button. You should see the following screen if the process executed properly. Notice under the detail heading that the process updated successfully. If any errors were encountered, an error message would be displayed in red text with supporting information to help you determine what went wrong.

Figure 7

Click to view.

We have found that this process is helpful to new customers who are just starting out and who have a relatively large number of categories that need to be added to Helpdesk. However, there are cases where there are a lot of categories already in Helpdesk and the Administrator is looking for a better way to change the status of a category or to add additional categories. Now that you have this first version of the spreadsheet mastered, the second spreadsheet will provide a way to start with the categories that are already in your system, rather than when you are first starting out. Click on the orange tab at the bottom of the spreadsheet labeled Helpdesk Master.

Figure 8

Click to view.

This spreadsheet is similar to the first, except there are two new columns on the left side of the screen. This part of the screen is used to paste in the XML category output from Helpdesk and the spreadsheet will automatically convert the XML into the same white background working area that was shown in the first example.

To do this, in Helpdesk, select Admin\Export Admin Data and change the Helpdesk Output Type to Category. You should then see the following screen.

Figure 9

Click to view.

Now, click the Check All button in the middle of the screen and you will see the following result.

Figure 10

Click to view.

Now click the Add button.

Figure 11

Click to view.

And, finally, click Next. This will produce the XML file that we will copy and paste into the spreadsheet.

Figure 12

Click to view.

Now select all of the data by either placing the cursor in the white working area and typing Control+A, or by using the mouse to highlight the entire file starting before the '<' in the <Helpdesk> statement, all the way to the bottom of the data including all of the </Helpdesk> command statement.

Now, copy the selected text (Control+C).

Go to the spreadsheet and position the cursor in cell A5.

Figure 13

Click to view.

Now, paste the clipboard into Cell A5 (Control+P).

Figure 14

Click to view.

Now, make the changes in the white space as you would with the first spreadsheet example. All of the same rules apply that were mentioned above, and, when completed, the copy and paste back into the Helpdesk is exactly the same as before, except that the XML Output text is not in Column H of this spreadsheet.

Over time, the Helpdesk user interface for category management will be improved so that this spreadsheet tool will no longer be necessary. But, in the meantime, the tool reduces the effort needed to create categories and increases the overall productivity of the system.