Importing Issues

Enterprise HelpDesk stores most of its issue data in the table tblDts of the issue database (<project>01.dat for Microsoft Access, <project>01_DAT for SQL Server). To import issue data into this table, you use the Import Issues command (File menu) in HelpDesk Admin.

Import Issues

Source table

You import issues from a source table, which could be a table in a Microsoft Access database, a table in a SQL Server database, a delimited text file, or a number of other formats.

Source fields

Destination fields

Destination fields are the fields in the Enterprise HelpDesk database table tblDts.

Mapped fields

You map source fields to destination fields. When you click Start, the mapping determines how the source fields are copied to the destination fields.

To import issues:

Prepare your issue data and the Enterprise HelpDesk database.

Import issue data into a source table.

A source table is an intermediate table that holds the issue data you want to import into tblDts.

Map the fields in the source table to the fields in tblDts.

Click Start to create new records in tblDts and to copy the imported issues from the source table into these new records.

What Can You Import?

Enterprise HelpDesk uses Microsoft Access functionality to import data, so you can import from any of the formats and applications supported by Microsoft Access:

Microsoft Access (databases other than the open database)

Delimited text (values separated by commas, tabs, or other characters)

Fixed-width text (values arranged so that each field has a defined width)

Microsoft Excel (versions 2.x, 3.0, 4.0, and 5.0)

Lotus 1-2-3 or 1-2-3/W (.WKS, .WK1, and .WK3 files)

Paradox (version 3.x or 4.x .DB files)

FoxPro (version 2.0 and 2.5 .DBF files)

dBASE III and dBASE IV (.DBF files)

Btrieve (with the data definition files FILE.DDF and FIELD.DDF)

SQL databases, using ODBC drivers

HTML

With Enterprise HelpDesk, you can import data into any of the fields in the tblDts table. This table contains most of the issue data.

You must use Microsoft Access (or SQL Server) to import data into the following tables:

tblUser, which contains user information such as the user's logon name, full name, e-mail address, and telephone number.

tblAttachment, which contains the names of the files attached to different issues.

Any of the tables that contain the possible values for choice lists.

Before You Import

How New Records are Created

When you click the Start button, Enterprise HelpDesk creates new records in tblDts, and copies the data from the source table into the new records.

In general, if there is no mapping for a field or the imported data is invalid, Enterprise HelpDesk leaves the field blank. However, there are exceptions. The following summarizes how Enterprise HelpDesk handles fields when it creates new issues.

Issue Number

You cannot import issue numbers. Enterprise HelpDesk automatically assigns issue numbers to issues.

Owner (nUserID)

If there is no mapping or the user name is not found in tblUsr, the field is left blank.

Submitter (nSubmitterID)

If there is no mapping or the user name is not found in tblUsr, the name of the current user is stored in the field.

Contact (nOriginatorID)

If there is no mapping or the user name is not found in tblUsr, the name of the current user is stored in the field.

Progress

You can import the ID of a choice value or the choice text. If there is no mapping for a choice list field, Enterprise HelpDesk sets the Progress field to the choice with ID = 0 (by default, New).

State

You cannot import State values. Enterprise HelpDesk sets the State field based on the Progress field.

Time and Date fields

You can import time and date values. If you do not map a date or time field, Enterprise HelpDesk sets the field to the current date or time. See the tblSubstate choice table for the mapping of Progress values to State values.

The Closed Date field value is imported only if the State is Closed.

Imported time values should use the time format specified in the Regional Options of Windows Control Panel.

Adding Fields for Imported Data

Compare the issue data you want to import against the fields in tblDts. If you want to import additional issue data for which there are no corresponding fields in these two tables, use the Field Editor to add the required fields.

Importing IDs or Record Numbers

If you want to import the IDs or record numbers of your data records, create a numeric field in Enterprise HelpDesk. You can then map your record IDs to this field when you import your data.

Adding Users and Contacts

Add users and contacts in Enterprise HelpDesk before importing data that contains user names.

If you want to import user names into the Owner, Submitter, and Contact fields, the names you import must match the full names assigned to the users in Enterprise HelpDesk. You must use the users' full names, not their logon names.

For example, if you import a set of issues owned by "Erick Yanez" and there is no such user defined in Enterprise HelpDesk, the Owner field is left blank.

Note that you can import the numeric ID for a user in the tblUser table (Users.mdb file or USERS_MDB database).

Importing Choice Lists

Customize choice lists to match the values you want to import. You could also change the values in the imported data to match the Enterprise HelpDesk choice lists. You could do this in the original issue data. Or you could use Microsoft Access to modify the source table created when you imported the original issue data.

Importing Text Files

If your issue data is in a text file, the first data record should contain the field names. Otherwise, Enterprise HelpDesk assigns the fields the names 1, 2, 3, and so on. This makes it harder to map the fields.

Also, the first data record should have no blank fields. Microsoft Access uses the first data record to determine the data types of the fields. For example, if a date field is blank, Microsoft Access will import it as a text value, and you won't be able to get this value into a Enterprise HelpDesk date field.

Backing Up Projects

Make a backup copy of the issue database before you import any data. If you make a mistake mapping the fields, having a backup copy allows you to change the mapping and try again.

Logging Off Users

Make sure no one is using the project database while you import issues. Ask all users to exit Enterprise HelpDesk before you start to import issues.

Importing Issues into a Source Table

You don't import issues directly into the Enterprise HelpDesk tables. Instead, you import the issues into a source table. Enterprise HelpDesk copies the imported data into its tables after you define a mapping between the fields in the source table and the Enterprise HelpDesk fields.

To import issues from a file:

On the File menu, click Import Issues.

Click New Source Table to import data from a text file, spreadsheet, or database table into a Microsoft Access table (the source table).

In the Files of Type list, click the file format you want to import.

To import issues from another Enterprise HelpDesk database (or from any other Microsoft Access database), click Microsoft Access.

Click the file you want to import, and then click Import.

To import issues from an Enterprise HelpDesk Access database, select the project.dat file.

In the Import Objects dialog box, select the import options you want.

If you are importing from a text file or spreadsheet, select the First Row Contains Field Names check box.

If you are importing from a Microsoft Access database, select the tables you want to import.

Click OK to import the data.

Enterprise HelpDesk creates a source table named after the file you selected.

If you want to import another file, repeat steps 4 and 5. When you've finished importing, click Close.

To import issues from a SQL database:

On the File menu, click Import Issues.

Click New Source Table.

In the Files of type list, click ODBC.

In the Select Data Source dialog box, click the Machine Data Source tab.

Find the SQL Server data source and click OK.

In the SQL Server Login dialog box, type your SQL Server login ID and password.

Click Options, and in the Database list, click a database.

To import issues from an Enterprise HelpDesk SQL Server database, click the _DAT database.

Click OK.

In the Import Objects dialog box, select that tables you want to import.

For Enterprise HelpDesk databases, the table to import is dbo.tblDts.

Click OK.

Combining Multiple Source Tables

When you import issues into Enterprise HelpDesk, you work with one source table at a time. If your issue data is stored in two or more tables, you'll need to combine those tables into a single source table before you can map the source fields to the destination fields.

First, use the Import Issues dialog box to import each file into a separate source table. Then use Microsoft Access to define a query that joins these source tables into a single table.

Creating Queries to Merge Source Tables

The qryMergeCensusTables query merges two source tables named tblDts and tblFixInformation. If the source tables you want to merge have different names, you need to create a new query based on qryMergeCensusTables.

To create a new query for merging source tables:

Exit HelpDesk Admin and open Program Files\HelpDeskTools\Admin.mdb in Microsoft Access.

In the Objects list, click Queries. Click qryMergeCensusTables and click Design.

In the View menu, click SQL View.

In the SQL statements, replace all occurrences of tblDts and tblFixInformation with the names of the source tables you want to merge.

In the File menu, click Save As and type a name for the new query.

Close Admin.mdb.

Open Program Files\HelpDeskTools\Admin.mde.

Open the table tblImport and add a new record. In the tName column, type the name of the query. In the nID column, type a unique, numeric ID for the query (use the next available nID).

Close Admin.mde.

The new query will now be available in the Source Table list of the Import Issues dialog box.

Mapping Fields

After you create a source table, you need to define a mapping between the fields in the source table and the fields in tblDts. Enterprise HelpDesk uses this mapping to copy the imported data into the new records it creates in tblDts.

To map a source field to a destination field:

Click a field in the Source Fields list.

Click a field in the Destination Fields list. The data in the source field is copied to this field when you click the Start button.

Click the right arrow button to map the fields.

To remove a mapping:

In the Mapped Fields list, click a mapping.

Click the left arrow button.

Mapped Fields

Mapping Text Fields

Text fields, such as tBriefDescription, have a limit of 255 characters. Memo fields store large numbers of characters (in Access, up to 64000 characters; in SQL Server, up to maximum length of 2³¹-1 (2,147,483,647) characters).

Mapping Memo Fields

Import data into the mDetailedDescription and Notes$Log fields, not into the Description$Input and Notes$Input fields

Importing Choice Lists

Import either the choice text or the numeric ID of the choice in the choice table.

Importing Users, Submitters, and Contacts

Import either the full name of a user, or the numeric ID for that user in the tblUser table (Users.mdb file or USERS_MDB database).