Import Wizard

It is used in Katmandoo to import data from a source file into the database. The first page of the wizard allows selection of the source file and mapping of  column names in the source file to existing names in the database. The second page is used to preview data before importing. The import options of the wizard allows data to be imported in different file formats.

While importing data, the wizard uses the alternate key (not the primary key which is an auto-generated Id) to find existing data. If exist, then it retrieves the information from the database matching with the alternate key. Otherwise it treats the row in the source file as a new record and insert into the respective table.

If the existing data found in the database, then only the values of the mapped column (excluding the alternate keys) are updated.

Following tasks should be done in the First page of the wizard

1. Select the source file containing the data to import.
2. Set mapping of the column names in the source file with the column names in the database.
3. Click on [ Preview ] to load data based on column mapping information and display in the datagrid before user can save into database.

Illustrative snapshot:

1. Source file information.
2. Column name mapping.
3. Preview data from the source file before saving into the database.
4. Import Wizard Toolbar
   • Important Notes: Click to show important information about importing data.
   • Quick Search: Use it to search data in the database.

data from either Excel or CSV file can be imported into the database. When an Excel source file is selected, the user needs to select the worksheet of the excel file containing the data to import.

Illustrative snapshot:

Note: Import options (ie to import data from either Excel or CSV file) has been moved to Open file dialog which can be opened by clicking on [ Change source file ] button.

1. Folder (directory) of the selected source file.
2. Name source file to import data.
3. Click [ Change Source File ] to activate the Open File Dialog to select the source file to import.
   • If the file is already opened by another application (eg Excel), then you will be prompted to close the file so that it can be opened exclusively to import data.
4. Selected worksheet name in the source spreadsheet file to import data. Click on the Worksheet drop down list box to select different worksheet in the source spreasheet.. 
5. Check this option if the source file has rows with duplicate data and you want to import unique values only (in this example unique Organisation names only). 

Before continuing, click here to read a very important note on importing data.

Illustrative snapshot:

1. Click it to show Open File dialog (2)
2. Open file dialog
3. Click it to open once a file is selected.
   
4. Excel 95-2003: This is the preferred and default option for importing data. If the source is a Excel spreadsheet, then this option should be used to import data. See "How is the data type determined while importing data?" for more information.
   • If Katmandoo can update Windows's Registry, then Katmandoo scans all the rows in the source file to determine datatype of each column in the source file.
   • If Katmandoo does not have permission to update Windows's Registry, then Katmandoo scans only the top eight (8) rows of the source file to determine datatype of each column in the source file.
     
5. Excel 2007 (With Macro): If the source file is a Excel 2007 spreadsheet with Macro enabled, then this option should be used to import data. See "How is the data type determined while importing data?" for more information. Please note that this option is visible only when the computer has Excel 2007 installed.
   
6. Excel 2007 (Without Macro): If the source file is a Excel 2007 spreadsheet without Macro, then this option should be used to import data. See "How is the data type determined while importing data?" for more information. Please note that this option is visible only when the computer has Excel 2007 installed.
   
7. Comma-Separated Value (CSV): If the source is a CSV file then this option should be used to import data.    

When the source file (or worsheet for Excel file) is selected, the system loads the column headings from the source file and maps them to  the column names (or aliases of column names) that are used in the database as shown in the snapshot below.  If a matching name is not found or it is matched incorrectly, the user can select from the corresponding drop down list box to map the column in the source file.

The column name in the source file (used to import data) can have following characters (which are case-insensitive):

• A-Z
• 0-9
• &   %   $   #
• *   -   =   +    /   \
• (   )   
• {   }
• >   <
• _ (underscore)
• ~ (tilda)
• SPACE

Examples:

• "Site Name", "Trial#", "Phone 1" are valid column names that can be used in the source file to import data.
• But "Site.Name" or "var!" are invalid since the column names contain DOT and EXPLANATION respectively which can not be used for the column name to import data into Katmandoo.

Illustrative snapshot:

1. Column names in the source file that is mapped to a column name in the database.
2. This column name (in this example "Address") from the source file has not been mapped with any column name used in the database. So, unless the column is manually mapped to an existing column name in the database, the data of "Address" column will NOT be imported.
3. Click on this combobox to list all the column names and aliases. 
   • Please note that only the column names related to the data category currently being imported and their aliases will be listed. In this snapshot, only the column names and aliases related to Locations and Sites are listed.
   • Tips: Press a letter to jump to the near-matching in the list (eg press "L" to jump to item starting from "L").
4. Click this button to remove mapping of the selected Column name which means you do not want to import data of the column.

The second page displays the data that will be imported into the database.

Important Note for importing MET, Trial AOV, Raw data and Trial Design: The first column in preview datagrid will have be SourceFileRowIndex that can be used to find the row in source file.

Illustrative snapshot:

 


Note: "Do not preview" option (which is not displayed in the snapshot) becomes visible when there are more than 8192 rows in the preview datagrid (2). "Preview only rows that have error" option (which is not shown in the snapshot) becomes visible when there is data error while importing data from the source file. When the "Preview only rows that have error" is selected, the preview data grid displays only those rows that have errors.

1. The "Preview New only" option displays only those rows from the source file that don't exist in the database. 
2. The "Preview Update only" option displays only those rows from the source file that already exist in the database but some of its data are different to the existing data.. 
3. The "Preview All" option (default) displays all the data rows from the source file. Preview Datagrid displays the data that will be imported from the source file.
4. Selected trial unit data row for which trait data is displayed in another datagrid (7).
5. The "Back" button allows the user to return to the first page where file information can be changed.
6. Clicking the "Save into database" button saves the imported data into the database.
7. In this snapshot, this datagrid that displays trait values for the selected trial unit (4). This datagrid is displayed only when required for the situation like this.
8. Displays the number of rows imported from the source file.

When import process is running, caption of the [ Close ] command button is changed to [ Cancel ]. In that stage, you can click on the [ Cancel ] button to cancel the import process and return to the the first page of the Import Wizard.