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. It also provides flexibility for exporting existing Hansoft data, modifying it in Excel, and then reimporting it to 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.
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.
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.
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
- Backlog item
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.
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.
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.
The following column types are supported for imports.
|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.
|Date and time||YYYY-MM-DD HH:MM (using a 24 hour clock)||2020-02-26 14:20|
|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
Robert Wells; Hans Andersson
The following built-in columns are not supported for imports.
- Duration—To set the duration of an item, add the Start and Finish columns instead.
- Last updated by
- Link to item
- Originally created by
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.
The Import spreadsheet dialog box opens. The successful and unsuccessful column mappings between the Excel sheet and Hansoft are displayed.
4. 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.