Data import guide
If you want to work with your data in Exponea, you can import them into Exponea by following these steps:
Go to Data & Assets -> Imports -> New Import
Exponea works with 3 types of data:
For every type of data you have 5 options how to import data:
Copy & paste, Upload file, Database, URL and Exponea.
When importing table of customers, you have to specify unique identifier by drag and drop at least one ID so the customers can be identified.
If you consider to send emails by Exponea, your customers have to have the attribute called email filled with email address of the customer. Similarly if you want to send SMS messages, customers have to have the attribute phone filled with a customer’s telephone number.
You can import several catalogues into Exponea. When importing a catalogue you have to drag and drop item_id into column specifying unique item ID.
You can import several events into Exponea. When importing a event you have to drag and drop ID customer_id into column specifying customer and drag and drop timestamp specifying time of event. Timestamp has to be always in UNIX timestamp or ISO format.
Select one of 5 data sources containing your data.
- Copy & paste
- Upload file
Copy and paste
Just easily copy and paste your CSV data into text field in application, choose delimiter and preview your data by pressing the “Preview data” button. When you are satisfied with the preview, click the “Next” button and continue in the import.
This option allows you import your CSV file. Select Delimiter and Encoding, preview your data and when you are satisfied, click the “Next” button.
When you have your data saved online, you can just insert its URL link, choose Delimiter, Encoding and preview data. When satisfied, continue by clicking “Next” button. It is also possible to input username and password using FTP if required, using the format
After integrating one of the PostgreSQL, MySQL or BigQuery, these datasources can be used in Exponea, using the SELECT statement.
Data from one project can be used in another, but not as a whole database, only types of analyses. In order to import data from another project, you need to create an analysis (e.g. report) in source project, with data structure you would like to export.
In “Create data mapping” step you can define what kind of data each column consists of (text, value), edit name of column or delete column. Moreover, you can create new column (e.g. to define that these values that have been imported). There is also a possibility to create a column with constant values for all imported records.
In dropdown menu for each column name, you can map that attribute to already existing attributes. When mapped to existing attribute, the name of the column changes font to blue. Be sure, that imported data have the same structure and formatting of existing data.
When importing data to already existing customer database, you need to pair them with existing records via ID. However, you do not want to import column with IDs as a new attribute, so you need to untick the checkbox in column you are pairing with ID.
Automated import is available for import from these sources: Database, URL and Exponea. If you want to create automated import, in the last step select “Repeatedly” and choose preferred date and time. You can choose frequency and also specify time range. There is also a possibility to add multiple times, if you want the imports running more times per day.
Date and time support
Imports now support a new column type – datetime (named Date, but supports time as well). When selected, it will not only accept timestamps but also string date/datetime representations. Default timezone can be selected for datetimes without a timezone designator (see examples).
Date time imports recognise:
- all distinct supported formats of dates: 2017-02-28, 2017-2-8, 25-02-2017, 20170301, 2017/03/01, 1.3.2017, 01.03.2017, 03/01/2017 (US MDY), 01-03-2017 (EU DMY)
- all distinct formats of times (time is optional, some date must always be provided) – 13:45:01, 13:45, 13:45:01.123 – note the fractions of seconds
- all distinct formats of ISO 8601 timezone designators (optional, only when time is provided) – Z (UTC), +02:00, -02:30, +0200, +02
- combination of dates, times and timezones from above, e.g. YYYY-MM-DDThh:mm:ss.sTZD format (1997-07-16T19:20:30.45+01:00) is valid
Date time imports do not recognise:
- Times with a.m. / p.m. notation
- Non numeric months (e.g. Jan, February, etc.)
Importing list attribute
It is also possible to import multiple values into an attribute. To achieve this, you need to provide data in JSON format and also select JSON as type when importing. For example the input below
with following mapping
will result in attribute being set as a list attribute with multiple values