What are imports?

Imports are configurable objects that define how your source data (i.e. data from another system or file) should be converted and imported into data sets in XLReporting. You can give an import a name, define the source data, the target data set, the column mapping, any (optional) lookups, conversions, and data filters, and set the user permissions. Once defined, you can run the import as often as you want to import new (or update existing) data.

Let's look again at our diagram (which we explained in the introduction) :

diagram imports

Imports are crucial to getting well-structured and validated data into XLReporting. They are usually the next thing you set up after you have created your data sets.

Imports provide very powerful functions to convert, validate, and filter the data. This enables you to define data sets that are optimal for reporting purposes, without being constrained to the structure or format of your existing source data.

When you sign up, your account already contains demo data with reports, imports, and data sets for common financial reporting. So you don't have to start from scratch, you'll have working examples which you can start to use, and amend or expand over time. You can create, change, and delete imports at any point in time.

Some common examples of imports are:

  • Financial data from an accounting system
  • Customer data from a CRM system
  • Invoice transactions from a database
  • Tabular data in Excel workbooks

Create an import

You can create an import in two ways:

  • Click on Define - New in the sidebar menu.
  • Click on the Actions - Create new button of an existing import. This also enables you to make a copy of an existing import.

Either way, the rest of the process is the same, and you can enter a name for your new import, set user permissions for it, and define its column mapping. The column mapping of an import is shown in Excel-style with a preview of the converted data:

screenshot define imports

Settings

You can define the settings for the import via these fields:

  • Name - enter a name. This must be unique within all your imports.
  • Description - enter an (optional) description. This is helpful to document your various imports.
  • Active/locked - activate or lock an import. When locked, no data can be imported.
  • Target data - select how new data should be imported into the data set:
    • Add to - add new data to the existing content of the data set.
    • Overwrite - overwrite the entire existing content of the data set.
    • Replace in - selectively replace existing content of the data set. For example, anytime you import a certain financial period, you might want to overwrite the previous content for that same period. If you select this option, you also need to indicate which column(s) will trigger the replacement. Read more here.
    • Update in - update the given columns in existing rows in the data set. This option will only update rows that already exist in the data set, and ignore any other data in the import source. The target data set must have a defined Key column, because that is used to determine whether a given row already exists or not.
    • Upsert in - update the given columns in existing rows in the data set, and insert rows that don't yet exist in the data set. The target data set must have a defined Key column, because that is used to determine whether a given row already exists or not.
    • Delete from - selectively delete existing content from the data set. Data that matches your data source will be deleted. No new data will be imported. If you select this option, you also need to indicate which column(s) will trigger the deletion. Read more here.
  • Data set - select the target data set. The converted and filtered source data will be imported into this data set.
  • Source data - select your source data. This will take you through a process where you can select the type and location of your source data. You will be able to preview that data.
  • Group - select how this import is to be shown in the menu navigation.
  • Normal/In batch - select whether you allow this import to be visible in My data so it can be run by itself, or whether this import can only be run as part of an import batch of another import. In the latter case, this import will not be visible in My data. This setting does not affect the actual function or permissions of this import.
  • User roles - select one or multiple user role(s) to restrict access to this import. If you leave this blank, all users have access.
  • Script - create a script to process the source data before it is being processed by the import definition. This option is for advanced use, and only visible if your user role has the relevant permissions. Read more about scripts.

Select source data

Click on Source Data to select your source data. You can choose from the following data sources:

  • Local file - a file on your computer or office network.
  • Cloud file - a file that is stored with a cloud provider, such as Dropbox, OneDrive, Box.com, GoogleDrive, or any web link.
  • Connector - use one of our predefined connectors to popular web-based accounting systems, or manually configure an API endpoint.
  • Currency rates - request currency exchange rates for any given date. We use http://fixer.io/ which provides current and historical foreign exchange rates published by the European Central Bank. The rates are updated daily around 4PM CET.
  • Data set - select one of your existing data sets. This enables you to copy or move data from one data set to another.
  • Query - define a report query based on your data sets. This enables you to transform, aggregate, and/or filter existing data and import the resulting data into another data set, for example if you want to summarize large data sets.

After you have selected your source data, you will see a data preview:

screenshot define imports source

If you selected an Excel workbook, you will be able to select which sheet within that workbook you want to import. For Excel workbooks or text files, you can also indicate at which row the data starts (enabling you to ignore empty or title rows in files) and whether your file contains column headers. It is preferable to use files that contain header names, because it is easier to work with column names when importing data. If your file does not have header names, the columns are referred to by letters (e.g. Excel style such as A-Z) or column numbers (1-99).

Once you have reviewed the data, click on Apply and you can start to work on the column mapping.

Transpose source data

When importing from Excel workbooks, you may often find that information is laid out in columns, for example period-by-period amounts. XLReporting enables you to transpose that column data into rows, so you can store it optimally in data sets. Simply select the column names in your source file that you want to transpose:

screenshot define imports transpose

This will transpose the selected column data and you can use the 2 special columns TRANSPOSE in the column mapping to your data set.

By default, the transpose operation will skip empty values and zeros. If you want to transpose all columns onto all rows even if they are empty, you can do so by selecting the Also include empty option.

Define column mapping

Once you have selected your target data set and your source data, XLReporting will try to automatically match the columns in your source data to those in your target data set. You can always edit this where required.

The column mapping is shown in Excel-style with a preview of its data (a sample of the first 100 rows):

screenshot define imports columns

If you want to change the order of the columns, just drag them across to the left or right. You can edit a column mapping by clicking on its Mapping header:

screenshot define imports columns edit

You can select the source or other content for each column in your target data set, by choosing from the following:

  • A source column - select a column in your source data. If your source contains column headers, the dropdown list will show those, otherwise the list will show letters (in Excel style: A-Z) or column numbers (1-99).
  • VALUE() - select this if you want to import a predefined static value. An extra field will appear where you can enter that value. This value will be used for all rows in the import.
  • SELECTED() - select this if you want to import a value selected by the user when they'll run this import, rather than a value from the source data. The user will be presented with a dropdown list of the current values in your data set, and will need to select a value. This value will be used for all rows in the import. Read more.
  • LOOKUP() - select this if you want to lookup values in another data set based on some information in your source data. You can create the lookup key by either using a column value or an expression. Read more.

Convert the source data

Once you have selected the source, you can optionally convert or recalculate the source data. You can enter a simple function or an expression with multiple functions and operators. An expression can contain the following elements:

  • The reserved word data - this is a placeholder for the current value of a column in your source data. Use this every time you want to refer to the current value in an expression. For example, to multiple the value in a column by 100:
    data * 100
  • Columns names in source data - to reference columns in your source data, you can simply type their name. For example, to substract the Credit column in your source data from the Debit column in your source data:
    Debit - Credit
  • Special column names in source data - if column names in your source data contain reserved words or spaces, or if are very short (3 characters or less), you might not get the desired result or even an error. In that case, you should enclose those column names with the [ and ] characters. For example, to substract 2 columns with very short names:
    [Dt] - [Cr]
  • Column names in the target data set - to reference other mapped columns in the target data set, you can enclose column names with the # character. This enables you to distinguish between columns in your source data and columns in your target data set, even when they have the same name. For example, to divide the Amount column in the target data set by 1000:
    #Amount# / 1000
  • Functions - you can choose from a large collection of functions with the same syntax as in Excel to perform a large variety of text, data, and number operations. Some examples:
    ROUND(Amount, 2)
    LEFT(data, 20)
    IF(Unit = "Actuals", Amount, 0)
  • Static values - you can use any static value in your expression. When using text values, these must be enclosed by " (= double quote) characters. Some examples:
    data / 1000
    data + " some added text"
    IF(data = "Sunny", "warm", "cold")
  • Operators - you can use all common mathematical operators, such as: + - * / and you can use brackets ( ) to control the sequence of operations. For example, to substract the Credit column in your source data from the Debit column in your source data, divide it by 1000, and then round the result to 0 decimals:
    ROUND(Debit - Credit) / 1000, 0)

Here is a practical example with a screenshot:

screenshot define imports columns convert

User selections

In many cases you might want the user to select some value when they start an import. For example, the company they're about to import, or the period. You can achieve that with the SELECT() function.

The below example uses a SELECT() to present the user with a dropdown list of company codes (using the values from another data set). The selected company code is then imported in the target data set:

screenshot define import columns select

Lookup data

Another common scenario is to look up some value in another data set, based on data in your import file or some other logic. Let's assume an import file contains local Chart of Account numbers that need to be converted to a central Chart of Accounts.

The below example uses a LOOKUP() to lookup the central account code based on the combined company code and local account code in your import file. The looked up account code is then stored in the target data set:

screenshot define import columns lookup

Repeat data

Another common scenario in imports is the need to "ungroup" grouped data, in other words repeat data from the row above where applicable. That will ensure every row has all the relevant data so it can be stored in a data set. The option Repeat previous values in the When empty field will do just that.

Replace data

When you create an import, you need to decide how new data correlates to existing content of the data set: do you want to simply add to the existing content, or do you want to completely overwrite all existing content?

There is a middle road as well, by selectively replacing existing content based on certain criteria. This option will delete any matching existing content in the data set, before new data is imported. This is achieved by looking at certain columns in the import file: if that same data already exists in the data set, it will be deleted.

A practical example is importing financial data for a given financial period: most users want to be able to import subsequent (updated) versions, but without duplicating anything. To achieve that, you should select Replace in and Replace existing data for the relevant column:

screenshot define import columns replace

You can select multiple columns, for example company and period. In that case, any existing data for the same company and period will be deleted, before the new data is imported.

The option Delete from is similar in that it deletes matching existing content in the data set, but without importing any new data.

Replace data using filter values

By default, existing data will be deleted if the fields that you selected to replace upon, match the new data that is being imported. For example, let's say you have defined your import to be replaced on the period column: if your data includes period 2017-01 and period 2017-02, then any existing data on those 2 periods will first be deleted. In most cases this will work fine as it ensures that you can re-import data at any time without duplicating it.

But in some cases, you may want to delete existing data even if no new data is available. You can do that by selecting the option Delete using filter values, which will delete data if it matches the results of filters on the fields that you have selected to replace upon, irrespective of the new data that you are importing. You have to ensure that your new data will still fall within the filters you defined, but using this option in combination with the filter values gives you a lot of control over the data that you want to replace.

Filter the source data

Once you have selected the source column, and optionally converted or recalculated its value, you can filter the data. Filtering means that you can exclude rows in your source data from the actual data import.

You can specify a static value, a simple function, or a complex expression and you can use all common operators. You can choose from various filter functions.

The below example filters the source data on the Account column: only rows with accounts between 30000 and 39999 will be imported into the data set:

screenshot define imports columns filter

You can also use SELECT() to present the user with a dropdown list of values.

Actions

When defining an import, you can use the Save and Actions buttons in the right-top of the screen:

screenshot define columns actions

These buttons enable you to do the following:

  • Save - save your changes
  • Actions - open a dropdown menu with further options:
    • Create a new import (or copy one)
    • Delete this import
    • Define import batch
    • Export data or print
    • Review this import
    • Import data now

Define import batch

Click on Actions - Define import batch to combine multiple imports into a batch which you can then run as one single action. This is useful if you want to process related or dependent data during, before, or after a given import.

screenshot define imports batch

You can insert or delete imports at any time, and re-order them, using the dropdown menu in the last column.

By default, imports will be started in the order that you define them, and processed in parallel (i.e. near simultaneously). This gives the fastest performance in general, but if you have certain imports that require other imports to be completed first, you can set "Wait in line". When this option is set for an import, it will only be started and processed once all its predecessor imports have completed.

Review this import

Click on Actions - Review this import to review a number of aspects of this import and its target data set:

  • Data diagram - shows a diagram with all linked data sets. This is derived from columns that are set to data type Linked values.
  • Linked data - shows the results of checks on the Key values and Linked values that are used to link data sets. Because linked values need to correspond with key values, this integrity check is important.
  • Define fields - shows a summary of all defined column mappings.
  • Data set use - shows all linked data sets, the imports, reports, and models that are using this data set, and the total number of data rows in this data set.
  • Recent activity - show all recent user activity relating to this import.

screenshot define datasets imports review

Recommended reading:
Back to top | Data sets | Reports | Models