CSV

Category: Connectors
Subcategory: Data

Overview

This module offers a connector-style usage of CSV for import and exports. Advantages of connector style: - Model reflects CSV structure. - Microflow can be optimized for importing / exporting (e.g. by caching). - Suitable for high volumes (from 100K's to M's) - Streaming approach for higher performance and less memory consumption. Features: - Import CSV line by line - Write CSV line by line - Export to a CSV based on an OQL query - Export to a CSV based on a SQL query - Import a CSV having a simple structure using a SQL approach

Documentation

Demo url

Description

This module offers a connector-style usage of CSV for import and exports.

Advantages of connector style:

 - Model reflects CSV structure. 

 - Microflow can be optimized for importing / exporting (e.g. by caching or type interpretation).

 - Suitable for high volumes.

Typical usage scenario

Imports or exports using the CSV file format.

Large volume export or import.

Features and limitations

 - Import CSV line by line 

 - Write CSV line by line 

 - Write a CSV based on an OQL query

 - Write a CSV based on a SQL query

Dependencies

  • OQL module >= 1.4

Installation

Download and install the OQL module >= 1.4 from the App Store.

Download and install the CSV module from the App Store.

Configuration

Create a (non-persistent) entity that reflects your CSV structure (columns).

The names of the attributes should be ordered alphabetical in respect to the CSV.

E.g. a CSV having the following contents:

ID,Username,FirstName,LastName

1,jdoe,John,Doe

Could have an entity with the following attributes to maintain an alphabetical order:

  • C01_ID
  • C02_Username
  • C03_FirstName
  • C04_LastName

 

To import or export a CSV using the connector:

  • Use the action Export CSV or Import CSV
  • Select a microflow which will be used to import or export the contents (open the file)
  • Within this microflow, to import
    • Keep using Read next line 
    • If no object has been returned, the end of the file has been reached.
  • Within this microflow, to expotr
    • Keep using Write next line until all data is written
  • The CSV files will be closed when the microflow has finished.

Known bugs

Mapping on entity is limited by the platform, which returns the declared entities in random order instead of modeled order, which requires the approach described in Configuration.

Use import and exports based on OQL or SQL for higher volume imports/exports using simple structures.

Releases

Version: 1.8.0
Framework Version: 8.12.0
Release Notes: Fix for import using SQL to insert data using UTC offset.
Version: 1.7.0
Framework Version: 8.12.0
Release Notes: Fixed bugs: - Microflow parameter wasn't passed correctly. - System context wasn't properly used in called microflow.
Version: 1.6.0
Framework Version: 8.12.0
Release Notes: Converted to 8.12. Adapted to new API. Should also be usable in 9.x. Imported atlas UI.
Version: 1.5.0
Framework Version: 7.23.0
Release Notes: Improvements to export OQL to CSV: - Optional header export - Handle MendixIdentifiers in the result set (one should not have a valid reason to do use it, but it can be queried) - Accepts an entity instead of instantiated object Improvements to export SQL to CSV: - Optional header export Added a import from CSV using SQL. This is an optimized method to import a full CSV into a single entity, capable of importing millions of records without memory issues; but has some limitations (see the method's docs). Test project includes some tests of the import and export actions.
Version: 1.4.0
Framework Version: 7.23.0
Release Notes: Fix: Handling no quote character if empty is given as parameter. Added: Handling escape characters. High overlap with merge req 2 from JordtenBulte-FC.