Getting started with CSV Integration

Overview

Imports Schema

CSV integration works with the Admin Hub, which has the responsability to send, manually or automatically, an import order to targeted apps. The integration steps are as follows:

  1. CSV Files are uploaded to the server into the directory corresponding to the import

  2. Admin Hub sends an import order via its scheduler to the targeted app.

  3. Once the execution is started, the import line in Admin Hub is tagged by a blue status pellet.

  4. Before import the file, the targeted app analyzes it and applies a data transformation to be able to read it properly.

  5. At the end of the import, a report is sent to Admin Hub with a success status. Here, the import status can be tagged by :

  • A red pellet if the import failed (no message sent)

  • An orange pellet in case of partial success of the import (sent messages but some have caused errors)

  • A green pellet in case of successful import

How to upload CSV files using SFTP

Before running imports jobs in Admin Hub, CSV files must be provided, for that purpose, a deposit folder is accessible using an SFTP connection (FTP over SSH). After depositing CSV files, imports jobs can be run in Admin Hub manually or scheduled.

Many tools support SFTP connections, for instance on Microsoft Windows, WinSCP and FileZilla are free of charge and easy to use. For automated scheduled deposits, other tools may be used.

Configuring a connection in these tools usually follows the sames steps :

Start by selecting the SFTP protocol, then enter the credentials that were sent to you. (most times, there is no need to fill in the port, which is 22 as is by default for SSH/SFTP) :

  • Hostname (Ex : biscuiteriedupond-prod.cloud.vif.fr)

  • Username (Ex : biscuiteriedupond_prod-ftp)

  • Password

Upon successfully connecting, a tree-based directory structure can be seen in the graphical user interface. CSV files should be uploaded in subdirectories of the data folder :

Imports Schema

Each file uploaded is then moved to a .done folder and renamed, appending the date of processing. The 10 newer files that have been uploaded are kept there, older files are automatically deleted.

Main rules

Target

All imports must explicitly target the instance of the application with a target parameter.

Headers and columns order

CSV files must include a first header line indicating the columns codes. The columns order does not matter.

Mandatory fields management

Before importing the file, the validation step checks that all required fields are present in the headers. Furthermore, every single line goes through a validation step, including the column type.

Files encoding

Files must be encoded in UTF-8 without BOM before being properly analyzed.

Column separator

Fields are separated by ;

Format rules

Fields must be in international notation, ie :

  • the separator . is used as a decimal separator of a number

  • ISO standards are used to transfer special type data (including dates in ISO-8601)