Introduction

The Import Wizard enables flexible importing and manipulation of data from a file. You can find the Import Wizard by opening the Home Menu (⌨️Alt+H), and navigating to Other > Import Wizard. Here you can search for, create, and delete imports that concern everything from Items to Account reconciliations. See also our article for Import Wizard - Account Reconciliation and Bank Reconciliation.

Table of Contents

• Creating new imports
• Column Setup
  • Creating a setup
  • Column selector for the Delimiters, XLS, and XLSX formats
  • Multiple column setup
  • Column selector for Custom data format
  • Column properties
    • Overwrite
    • Virtual Columns
    • Ignored Columns
• Transformations
  • If (text) and If (number)
  • Excl. Row
  • Parameter lists
  • Date formats
• Advanced Columns
  • Allow discount
• Saving your column mapping

Creating new imports

Click the Plus Icon in the Import Wizard window. You will see the following dialogue box:

Description

Type in a short description of what you're importing.

Type

Defines which type of data you wish to import. Here you'll find the following options:

• Items
  
• Customers
• Suppliers
• GL Accounts
• Item Groups
• Stock
• Entity
• GL Journal
• Advanced Shipping Notice
• Sales
• Account Reconciliation

Data Format
Defines the format of the file you wish to import data from. The following formats are supported:

• Delimiters: Columns are separated by semicolons or some other character. Remember to specify your delimitor when you select this data format.
• Custom: Used with files that have set column widths.
• XLS: Excel spreadsheet, old format.
• XLSX: Excel spreadsheet, new format.
• Delimiters (Tab): Columns are separated by tabulators instead of specific characters.

Your choice of data format will impact your Column Setup options later in the process, so picking the correct one is important.

First row contains headers
When this option is ticked, the system will interpret the first row of the file to be headers in a table, and thereby bypass the information in this row while importing and previewing.

Column Setup

Your column setup determines:

1. The location in your file where data should be copied from
   This field is also known as the "Column selector" or "Source selector".
   
   
2. The part of the database which that data is copied to
   Also known as the "To" field.
   
   
3. Any transformations or roundings that are to be applied to that data during import

Creating a setup

There are two ways to create your column setup:

1. Click the button for adding a new column setup manually.
   • 
2. Or you can generate your column setup automatically by simply dropping your file into the window and letting the system attempt to interpret the data for you. Note that this can only be done before adding any columns manually, and that the auto setup is not compatible with the Custom data format.

Column selector for the Delimiters, XLS, and XLSX formats

Here you can define the column in your file which your data should be copied from. You can enter this as digits and/or characters (like in an Excel spreadsheet). Remember to also define the "To" field to make sure your data is imported to the right part of the database.

Multiple column setup

This setup is used to collect data from several source columns, and then combining it. Simply click the Plus Sign in order to add another source. In the example below, this extracts data from the first and second column, and is then merged together in that order. In addition, we've defined the delimitor "-" to separate between them.

Note that the columns are merged before any transformations occur, and that this type of setup is not available when using the Custom data format.

Multiple column setup can also be used to add data from several columns in your file into a single collection field (table/array). These fields are marked with ([]), and delimiters are not available for this type.

Column selector for Custom data format

Here you can define which characters (to and from) in your source which data should be extracted from. Remember to select your "To" field to define the database field which you want the data copied into.

Column properties

Overwrite

When importing or updating against existing data, you will manually be able to select which "To" fields that are to be overwritten. Note that this is only available for certain "To" fields, and that these fields can change over time.

Virtual Columns

For static data which you don't want imported from your file, you can use virtual columns. These can be combined with the transformer "Constant value" to define a static value for reach row that's being imported. This method could for example be used to import items into a specific item group.

Ignored Columns

Ignored columns will not be included during import.

Transformations

Before data is copied into the database, it can be transformed in a variety of different ways:

• Prefix
  Adds value of parameter before the column value.
  
  
• Suffix
  Adds value of parameter after the column value.
  
  
• Constant value
  Replaces value in column with value from parameter. This is mostly meant for Virtual Columns.
  
  
• Select letters
  Specifies a substring of characters that you want to extract from the given column. If you define a negative number as your first parameter, it will be interpreted as starting the end of the data.
  
  
• Replace
  Replaces a value in one column with another. If the value appears several times, every instance will be replaced.
  
  
• Replace and abort
  If the value in the column is equal to the first parameter, it will be replaced with the value of the second parameter.
  
  
• Mathematical functions

• Multiplication
• Division
• Addition
• Subtraction
• Rounding
  
  

• Fall back to default
  The value of the parameter is only filled in if the field/value in the file is empty.
  
  
• If (text)
  Explained in more detail below.
  
  
• If (number)
  Explained in more detail below.
  
  
• Excl. Row
  Explained in more detail below.
  
  
• Date/Time format
  Explained in more detail below.
  
  
• Replace w/line break
  Replaces the parameter value with a line break in the given column.

If (text) and If (number)

It's possible to selectively perform transformations if the data meets certain criteria, using the If (number) and If (text) transformers. You should use If (Number) for data that only contains digits and delimiters. In the example above the system will add the suffix "ABCDE" but only if the existing value starts with "ABCDE" already.

Note that all transformations are cumulative, meaning the value of one transformation is fed into the next.

Excl. Row

Exclude Row is used to remove invalid rows from your import. Used in combination with the If transformers. Here's an example for filtering out all rows in an import where the price column has a value of zero:

The preview will show the value as stricken out like this if it has been excluded.

Parameter lists

To run If transformations against several different values at once, you can use parameter lists by defining parameters separated by a semicolon, like "Value1;Value2;Example;1;2;3". As in the example below, If (number) = 1;10;30 > Multiply value by 2:

This will run for all values in the column that are either 1, 10, or 30. Those values will then be imported as 2, 20, or 60.

Date formats

The following formats are readable without the need for transformations:

• yyyy-MM-dd
• yyyyMMdd
• dd.MM.yyyy
• yyyy-MM-dd hh:mm:ss
• yyyyMMdd hh:mm:ss
• dd.MM.yyyy hh:mm:ss
• yyyy-MM-ddThh:mm:ss

For other formats you can use the transformation type Date/time format, where you can define the date and time in the same manner as the examples above. Let's say if you have a date like 2013.14.04 (meaning April 14 2024), you could use the following transformer:

Advanced Columns

With advanced columns you can map even more types of data, like "Assembly time" which is used to add assembly time to items linked to specific models in the brand/model registry. To add a column like this, tick the "Include advanced columns" option in the header, create a new column, and choose one of the advanced options in the "To" field, like Assembly time.

Note that once you've added an advanced column, the Include advanced columns option can no longer be unticked.

Allow discount

The flag "Allow discount" can now be specified during the following import processes:

• Item import
  The flag will be applied to the created or updated item. If "Discount out" is specified for an item with "Allow discount" set to false, the discount will be set to zero during import.
  
  
• Price Hotel import
  The flag will be applied to the created or updated price in the Price Hotel, including prices based on amount and campaigns. Import or synchronization with Quick3 will also set this flag for Out prices. No discounts will be affected during import for In prices, as these are not eligible for the flag.

This function gives you deeper control of pricing. It is especially useful for campaign prices, as it lets you mark a price that is already discounted, avoiding further discounts being given.

Saving your column mapping

You can save your column mapping for later use. Once your setup is complete, open the hamburger menu in the top right, and click the "Save column mapping" option.

Once your setup has been saved, it can either be loaded into another import, or be reused from the main Import Wizard window through the "Create import from setup" button on the right. This will open a list of all your saved setups, which you can sort and filter.