What are imports?
Imports are configurable objects that ensure data from other systems or files can be
repeatedly and consistently converted and imported into data sets in XLReporting. You can give
each import a name, define the source data, the target data set, the column mapping,
conversions, data filters, and set user permissions.
Once created, you can run imports as often as you want to update the data in XLReporting.
When you sign up, your account already has a number of predefined imports, aimed at
financial reporting. They contain example data of a fictitious company. You don't have to
start from scratch, you have working examples which you can start with, and you can add or
change imports at any point in time.
Some common examples of imports are:
- Financial data from an accounting system
- Invoice transactions from a database
- Product list from an Excel spreadsheet
- Customer data from a CSV file
You can also watch the instruction videos.
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:
You can also watch the instruction video.
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 to explain the use of this
import to users. The text can contain basic html tags to format your text (e.g. <br>
for a next line, <b> for bold text). The text can also contain functions to include dynamic information or images in the text.
- Active/locked - activate or lock an import. When locked, no data can be
- Target data - select how new data will 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
need to select 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 fields with a new value in existing rows in the
data set, preserve all other existing data, and ignore data in the import source
that does not 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.
- Upsert in - update the given columns in existing rows in the data
set (similar to "Update in") 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 need to select 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
- 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
Analyze 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 Analyze. 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.
- Sort - define an (optional) sort order (1-99999) that determines where this
import is displayed in the menu. If you leave this blank, the import is ordered
- 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 Edit script permission. Read more
Select source data
Click on Source Data to select your source data. You can choose from the
following data sources:
- Local file - select a file on your computer or on your office network. This
can be any Excel, CSV, XML, TXT, or JSON file.
- Cloud file - select a file that is stored with a cloud provider, such as
Dropbox, OneDrive, Box.com, Google Drive, Amazon S3, or a public URL. Users will need to
login to the selected service with their own credentials, and access is only granted within
their own browser. This can be any Excel, CSV, XML, TXT, or JSON file.
- Connector - select one of our import connectors to common accounting
systems, or manually define an API endpoint. Users will need to login to the
selected service with their own credentials, access is only granted within their own
browser, and remains active only until the browser is closed. Read more about import connectors.
- 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:
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 define the
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:
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
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:
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 defined 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
- 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 enter their name enclosed between square brackets. For example, to substract
the Credit column from the Debit column in your source
[Debit] - [Credit]
- Column names in the target data set - to reference the results of other
mapped columns in the target data set, you have to prefix them with "Target: " between the
square brackets. For example, to divide the Amount column in the
target data set by 1000:
[Target: 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:
IF([Unit] = "Actuals",
- Static values - you can use any static value in your expression. Text
values must be enclosed by " (double quote) characters. Some examples:
data / 1000
data + " some added text"
IF(data = "Sunny",
- Operators - you can use all common 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:
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 by using
the SELECT() function as a filter.
The below example uses a SELECT() in the filter field to present the user with a
list of company codes (using the values from another data set). By using
in the source column the selected company code is then imported into the target data set:
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:
Repeat empty values
The option when empty enables you to define the action when a field has no
source value: either skip the entire row, abort the entire import (nothing will be imported),
or repeat the value from the previous row (which is useful if your import data is grouped).
When you define an import, you can decide how newly imported data relates 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, or do you want to
update existing data? Or perhaps you want to selectively
replace or delete existing content based on certain criteria?
If you choose to replace or delete data, you need to select
one or more column(s) that will be used to decide which data to replace or delete. The import
will use the filter values on these column(s) (e.g. SELECT, SELECTED, or fixed values) if these
are defined, or else the realtime values in the source data, to replace or delete existing data.
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:
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.
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
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:
You can also use SELECT() to present the user with a dropdown list of values.
When defining an import, you can use the Save and Actions
buttons in the right-top of the screen:
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.
You can insert or delete imports at any time, and re-order them, using the dropdown menu in the
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.