Importing from Microsoft Excel
You can import data from a Microsoft Excel file into a Hansoft project. This helps you quickly add existing data and start working it in Hansoft.
During an import, Hansoft automatically maps columns in the Excel sheet with Hansoft columns and adds an item for each valid row in the sheet.
Keep in mind that actions that automatically occur in Hansoft do not occur as the result of imports. For example, normally, when you assign an item, the user is added as a watcher and gets email notifications when the item is updated. This does not occur when importing Excel files.
Preparing the Excel file
Before you import an Excel file, you may need to add additional information to it to adjust the indentation of items and the type used for items added to Hansoft.
By default, items imported from an Excel file are added as scheduled tasks at the bottom of the current view and placed at the same indentation level as the last item in the view. For example, if the current item is at the first indentation level (level 1), all imported items are added as scheduled tasks at level 1.
To override this behavior and use different indentation and item types, add Indentation and Type columns to the Excel file before importing.
Indentation
To control the indentation of data imported from an Excel file in a tree in Hansoft, add a column named Indentation to the sheet. For each item, indicate the indentation level in the column, starting with 0. Items at the top level of the tree have an indentation value of 0. Items one level under the top level 0 have an indentation value of 1, and so on.
Items cannot be indented more than one level under the parent item. For example, an item at level 1 cannot have a direct child with indentation at level 3 or higher. Hansoft automatically adjusts any incorrect indentation. For example, if a level 1 item is followed by a level 6 item, the level 6 item is automatically added as a level 2 item and added as a child to the level 1 item.
Note: Indentation is not applied if the Type column is used to add a release item or scheduled task. These items are added at the same level as the sprint.
Item types
When importing to the Planning view, you can control the item type of imported items. Add a column to the Excel file named Type. For each item, indicate the name of the type in the column. The following item types are supported:
- Sub project
- Scheduled task
- Sprint
- Backlog item
- Release
Keep the following in mind:
- In the Planning view, by default, all items are added as scheduled tasks. You can override this behavior using the Type column.
- In the Backlog view, all items are added as backlog items and the Type column is ignored.
- To import backlog items into the schedule, you must add a new sprint to contain the items. You cannot import backlog items into an existing sprint.
- In the QA view, all items are added as defects and the Type column is ignored.
Column mappings
The first row of content in the Excel sheet is interpreted as a header row and used to map columns in Excel with Hansoft columns. Columns are successfully mapped if the values in the first row match a Hansoft column name. Case is ignored. Hansoft only maps Excel data to columns active in the current view.
Empty rows at the top of the sheet and between rows are ignored. All other rows are imported to Hansoft as items to the current view. Items are added under any selected items in the current view or at the bottom if no items are selected.
Note: Mappings may no longer work if you change between Agile and Scheduled mode, change the project template, or rename a column between imports.
Data mappings
During the import, data is added to a column only if the it matches the column format. If the format does not match, the data is not imported.
Supported columns
The following column types are supported for imports.
| Column type | Format | Example |
|---|---|---|
| Text | Text string with no formatting | There are 10 different types of bananas |
| Multiline text | Text string with no formatting | There are 10 different types of bananas |
| Date | YYYY/MM/DD, DD MM YYYY, DD/MM/YYYY, DD.MM.YYYY / YYYY-MM-DD MM/DD/YYYY is not supported. Dates are automatically converted to the format selected in your personal options when importing. |
2020-02-26 |
| Date and time | YYYY-MM-DD HH:MM (using a 24 hour clock) | 2020-02-26 14:20 |
| Number | Integer | -200 |
| Number with decimals | Decimal number | -2.2 |
| Drop list with single choice | Text string matching a value in the drop list | Option A |
| Drop list with multiple choices | Text string matching a list of values in the drop list, separated by comma or semicolon. | Option A |
| Time spent | Decimal number | 2.0 |
| People | Text string matching a Hansoft user. For multiple users, separate names with a comma or semicolon. | Robert Wells Robert Wells, Hans Andersson -or- Robert Wells; Hans Andersson |
Unsupported columns
The following built-in columns are not supported for imports.
- Comments
- Duration—To set the duration of an item, add the Start and Finish columns instead.
- Function
- Hyperlink
- Last updated by
- Link to item
- Originally created by
- Predecessor/Successor
- Rank
- Workflow status
When the import is performed, Hansoft can check the Excel file against the project to identify existing items. Before the import starts, you can be prompted to choose how to handle existing items. You can either update existing items in the project or create new items in the project, which results in duplicate items.
Hansoft can only identify existing items if the Excel file contains a Database ID column and at least one of the items has a value in this column that matches an existing item's database ID in Hansoft. You can manually add a Database ID column in the Excel file. Or, to automatically include the Database ID column when you export to Excel from Hansoft, make sure the column is visible.
Performing an import
If the Excel file has multiple sheets, only the sheet that was active last time the file was saved is imported. Items are added under any selected items in the current view or at the bottom if no items are selected.
1. Go to the project view (Planning, Backlog, or QA) to import the data to.
2. Choose More > Export and import > Import from Excel.
3. Select the file to import. Only .xlsx format is supported.
4. Click Open.
The Import spreadsheet dialog box opens. The successful and unsuccessful column mappings between the Excel sheet and Hansoft are displayed.
5. If the column mappings are correct, click Import to start the import. If you need to make changes to the Excel sheet to correct column mappings, click Cancel, make any changes, and start the import again.
If the import file does not include items that are already in the project, the import is performed and no other steps are required.
If the import file includes items that are already in the project, you are prompted to update existing items or create new items. Continue with step 6.
6. Choose how to import the existing items. See Updates to existing items for information about how Hansoft matches existing items in the project with the Excel file and requirements for the Excel file.
- Click Update existing items to only update the existing items in the project without creating new items.
- Click Create new items to ignore the existing items in the project and create new items, which results in duplicate items.
