Mapping data in a csv file to the Remote Control database.

About this task

To ensure that the data in your csv file is added to the correct tables in the database, you must map the columns in your file to specific columns and tables. Create an import template to import the data. Use the template to define the correct format to be used for reading your file. You can select which columns of data in your file are to be added to the database and where the data is added to in the database. If the data that you are adding does not refer to an item already in the database, you can create a new item in the database. For example, if you are adding user data and the user data is not already in the database, select to create a new user with the data. A knowledge of the database tables and their structure is important for creating import templates. For more information about the database tables, see Database table and column descriptions.

Note: When user or target data is being imported, you must supply at least one of the following columns for import from the csv file
Targets
From the ASSET table, SERIAL_NO, UUID, or COMPUTERNAME.
Users
From the USERS table, USERID, EMAIL, or EMPLOYEEID .
Note: USERKEY is not the same as USERID, it is USERID that must be used.

To create a new Import Template, complete the following steps:

Procedure

  1. Click Admin > Import Data > Create new import template.
    The Edit Data Import Template screen is displayed.
  2. Type the relevant information.
    Name
    Type a name for your template.
    File Header
    This field is used if the file that you are importing has a set of column headings that correspond to specific database table column names. Type a comma-separated set of column names. If there is no header in the file, this field is left blank. 
    for example:  USERID,FORENAME,SURNAME
    Number of Columns
    Type the number of columns of data that is in your csv file. If you decide to change this value, click Update to change the numbers that are shown in the Column Number list.
    Note: If you click Update after you select the file encoding, you must check that the required encoding is still selected. If it is not, select the encoding value.
    File Delimiter
    Type the character that separates the columns in the file. 
    for example:  , or
    File Encoding
    Used to select the file encoding that applies to your CSV file so that it can be interpreted correctly. Choose the appropriate method for selecting the file encoding.
    • Select the required file encoding from the list.
    • Type in all or part of the file encoding name and click Search.
    • Leave the field with no selection and the ASCII UTF-8 file encoding is used.
    Date Format
    If you require dates to be imported, follow the instructions on screen for determining the format.
    Create Assets?
    true
    If the data that you are importing applies to a target that is not already in the ASSET table, create a target. The ASSET table contains the details of already registered targets.
    false
    If the data that you are importing applies to a target that is not already in the ASSET table, do not import the data into the database.
    Create Users?
    true
    If the data that you are importing applies to a user that is not already in the database, create a user.
    false
    If the data that you are importing applies to a user that is not already in the database, do not import the data into the database.
    Column Number / Table / Column
    The list of input fields under the column headings are used to determine where the data in your import file is placed in the database. Follow the on screen instructions for the database column types that must be specified for importing target and user data.
    • From the Column Number list, select the number of the column in your file that contains the data to import.
    • Click the ? icon next to the Table field.
    • Select the relevant table from the tables list.
    • Select the relevant column from the column list.
    • Click OK
    • Repeat these steps for each column in the file that you are importing.
    Note: Select only the columns that you want to import the data for, you do not have to import every column.

    For example: If your file contained the following data

    USERID, FORENAME,SURNAME,,LOCATION
    awilson,Alan,Wilson,Greenock

    and you only wanted to import the FORENAME and LOCATION
    you would select only 2 and 4 for Column Number.

    Test �?� Browse
    You can use this function to check that a test csv file, similar to the one to be uploaded, is correctly read and mapped by the import template. The result of the test shows whether the columns and header are mapped and read correctly and if the chosen file encoding reads the characters correctly.

    To use this function, complete the following steps:

    1. Create a test csv file similar in layout to the file to be uploaded, including the header if your file has one.
    2. Click Browse and select the test csv file.
    3. Click Test .
    4. The results of the test are shown in a new window and provide the following details.
      • If you include a header in your file, a message about the header is shown.
      • A table that shows the database columns that are defined for each column in the file.
      • The data that is mapped from the csv file.
      From the results, you can see whether the import template handles the data correctly. If not, you can change the template before you save it.
      Note:
      1. You have not imported any data at this stage. Complete step 3 to save the import template.
      2. Check that the required encoding is still selected. If not, select the encoding before you save the template.
  3. On the Edit Data Import Template screen click Submit

Results

The import template is created. Use the template to import a csv file and map the data in the csv file correctly to the relevant tables in the database. For more information about importing a csv file, see Importing a csv file.