Import Wizard

Modified on Tue, 17 Nov at 2:58 PM

Introduction


The Import Wizard enables flexible import and manipulation of data from files. Open the main menu and navigate to Other > Import Wizard. Here, you can search for, create, and delete imports for everything from Items to Bank Reconciliations.





Table of Contents





Create a new import


Click the plus sign in the Import Wizard view. The following dialog box will appear:



Description
Enter a short description of what you are importing.

Type
The type of data you want to import. The following options are available:

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


Data format
The format of the file you want to import data from:

  • Delimited: Columns are separated by semicolons or some other character. Specify the delimiter used in your file here.
  • Custom: Used for files with fixed column widths.
  • XLS: Excel spreadsheet, older format.
  • XLSX: Excel spreadsheet, newer format.
  • Text qualifier: Helps the file parser determine where a column starts and ends. This allows you to use delimiters within the text.
  • Escape character: Helps the file parser ignore certain characters in the text. This can be useful if a text value also contains a text qualifier.


The data format you select will affect the column setup later in the process, so it is important to choose the correct format.


First row contains headers
When this option is checked, the system will interpret the first row as table headings and skip the information in that row during import and preview.




Column setup



Your column setup determines:


  1. Where in the file the data will be taken from
    Also known as the "Column selector" or "Source column".

  2. Which database field the data will be copied into
    Also known as the "To" column.

  3. Which transformations or rounding operations will be applied to the data during import



Create a setup


There are two ways to create a column setup:

  1. Click the New column setup button.
  2. Generate a column setup automatically by simply dropping the file into the window and letting the system try to interpret the data for you. This can only be done before adding other column setups, and is not compatible with the Custom data format.


If the "First row contains headers" option is enabled, the wizard will automatically try to match the headers to "To" columns with the same name. The Note field will also be filled in with the text from the header.



Column selector for Delimited, XLS, and XLSX data formats


Specify which column in the file the data should be taken from. You can enter a number or a letter. Remember to specify the "To" column to define which field in the database the data should be imported into. In the example below, column 1 / A is set to be imported as "Item number".




Multi-column setup


This setup is used to retrieve data from multiple columns in the file and combine it. Click the plus sign to add a new source column. In the example below, data is retrieved from the first and second columns and then combined in that order. A "-" is also configured as the delimiter between the data.



Note that the columns are combined before transformations are run, and that this setup is not available for the Custom data format.


Multi-column setups can also be used to put data from multiple columns in the file into a single collection field (table/array). These fields are marked with ([]), and a delimiter is not available for this type.



Column selector for Custom data format


Here you can specify which characters (from and to) in the file the data should be taken from. Remember to specify the "To" column.




Column properties


Overwrite

When importing/updating existing data, you can manually select which "To" columns will be overwritten. Note that this is only available for certain "To" columns, and that the selected columns may change over time.



Virtual columns

Virtual columns can be used for static data that should not be retrieved from the file. Use virtual columns with the "Constant value" transformation to specify a static value for each row being imported. For example, this can be used to import items into a specific item group.



Ignored columns

Ignored columns will not be included in the import.





Save column setup


You can save your column setup to reuse later. Once you are happy with a setup, open the hamburger menu in the upper-right corner and click "Save column setup".



Once your setup has been saved, it can either be loaded into another import you have created, or used directly to create a new import. In the main Import Wizard view, click the "Create import from setup" button in the upper-right corner.



Note that this function is only available to client administrators.




Transformations


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


  • Prefix
    Adds the value from Parameter before the column value.

  • Suffix
    Adds the value from Parameter after the column value.

  • Constant value
    Replaces the column with the value in Parameter. This is mostly relevant only for Virtual columns (more on this below).

  • Substring
    Specifies how many characters (from and to) to extract from a text or number. If you enter a negative number as the first parameter, the count starts from the end of the data.

  • Replace
    Replaces a value in the column with another value. If the value occurs multiple times, all occurrences will be replaced.

  • Arithmetic operations
    • Multiplication
    • Division
    • Addition
    • Subtraction
    • Rounding

  • If (number)
    Explained in detail below.

  • If (text)
    Explained in detail below.

  • Default value
    The parameter value is filled in only if the field/value in the file is empty.

  • Replace and abort
    If the value in the column matches the first parameter, it will be replaced with the value in the second parameter.

  • Exclude row
    Explained in detail below.

  • Date/time format
    Explained in detail below.

  • Replace with line break
    Replaces the parameter with a line break in the specified column.



If (number) and If (text)



With "If (number)" and "If (text)", you can run a transformation only if the data meets a given condition. Use "If (number)" for data containing only numbers and separators. In the example above with "If (text)", the system will add the suffix "ABCDE", but only if the existing value already starts with "ABCDE".


Note that all transformations, with the exception of "Replace and abort", are cumulative. This means the value from one transformation is passed to the next, but only within the same column setup.



Exclude row


Exclude row is used to remove invalid rows from the import. Use it in combination with "If" transformations. In the example below, we filter out all rows where the price column has the value "0":



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



Parameter lists


To run "If" transformations against multiple values at once, you can use parameter lists by entering parameters separated by semicolons, like this: "Value1;Value2;Example;1;2;3".


Example: If (number) = 1;10;30 -> multiply the value by 2;



This will run for all values in the column that are 1, 10, or 30. They will then become 2, 20, or 60.



Date formats


The following formats can be read without transformation:

(y = year, M = month, d = day, h = hour, m = minute, s = second)

  • 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, use the Date/time format transformation type, and specify the format in the same way as in the examples above. For example, if you have a date in the following format: 2023.14.04 for April 14, 2023, use the following transformation:





Advanced columns


Advanced columns let you map additional types of data, such as "assembly time", which is used to add assembly time to items for specific models in the make/model register. To add an advanced column, check "Include advanced columns" and select your desired column.



Note that once an advanced column has been added to the column setup, you cannot turn off the "Include advanced columns" option until all advanced columns have been removed.



Allow discount


The "Allow discount" flag in an advanced column can now be specified during import in the following 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 "off", the discount will be set to 0 during import.

  • Price Hotel import
    The flag will be set on the created or updated price in the Price Hotel, including quantity-based prices and promotions. Importing or synchronizing with Quick3 will also set the flag for sales prices. Discounts on purchase prices will not be affected during import, as they are not subject to this flag.


This feature gives you additional control over pricing. It is especially useful for promotional prices, as it allows you to mark a price as already discounted to prevent further discounts from being applied.



Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article