Configure the generic data synchronization connector

SmartOSH has an import and synchronization module that allows bulk data loading via an exchange file (Excel or CSV) placed on an FTP server. This document describes the format, naming, and configuration required to use the generic connector.

Supported file formats

Excel (XLS / XLSX)

The data file can be provided in Microsoft Excel format (.xls or .xlsx). In this format, each table is represented as a tab (sheet) within the same workbook. The names and order of the tabs must match those described in the field specification.

CSV

CSV can also be used, with one file per table. Mandatory rules:

• Delimiter: ;
• Escape character for literal texts: double quote "
• Encoding: UTF-8
• First row: column names

Example:

Codigo;Nombre;TipoIdentificacion;Identificador
001;ACME S.A.;0;A554668987
002;"LUSIANA S.A";0;A00046879

Employee photographs (optional)

The optional photographs file must be provided as a ZIP with the images in the root directory, with .jpg, .gif or .png extensions.

File naming

The name must always start with KB020 and include, separated by hyphens, the basic file data.

Excel (XLS/XLSX)

Pattern: KB020-ORDER-Descriptive name.xlsx

Example: KB020-1000-Data load MyCompany 20170622.xlsx

CSV

Since there is one file per table, the name must include the table name instead of the descriptive name. The numeric prefix ensures they are loaded in the correct order.

Examples:

• KB020-100-Empresas.csv
• KB020-200-Centros.csv
• KB020-300-Secciones.csv
• KB020-400-Puestos.csv
• KB020-500-Empleados.csv
• KB020-600-Asignaciones.csv
• KB020-700-InvestigacionAccidente.csv
• KB020-800-Formaciones.csv
• KB020-900-Convocatorias.csv
• KB020-1000-Convalidaciones.csv
• KB020-1100-SesionesFormativas.csv
• KB020-1200-AccionesFormativas.csv
• KB020-1300-Cargo.csv
• KB020-1400-AsignacionCargo.csv

Legacy systems with short names

If, due to technical limitations, the client cannot use long names, they may provide the files with short names and add an index.txt file in the root folder with the equivalences.

Format of each line: [shortname];[longname]

Example:

emp.csv;KB020-100-Empresas.csv
cen.csv;KB020-200-Centros.csv
secci.csv;KB020-300-Secciones.csv

File location (FTP)

The client must place the files on an FTP server. There are two options:

• Client’s own secure FTP (FTP, FTPS or SFTP).
• Secure FTP provided by Smart OSH (by requesting it from the consultancy team).

If using the Smart OSH service, the connection details are:

Parameter	Value
Server	sync.smartosh.com
Type	FTP / FTPS
User	Provided by consultancy
Password	Provided by consultancy

Configure the connector in Smart OSH

1. Access the corresponding tenant with an Administrator user.
2. Go to Options → Data connection and add a new connector with the following values:

Field	Value
Import code	GENERIC
Type	FTP / FTPS (or SFTP if provided by the client)
URL	sync.smartosh.com (or the client’s server)
Path	Leave blank unless instructed by support
User	Provided by consultancy
Password	Provided by consultancy
Active	Yes
Overwrite	Yes

3. Save the configuration.
4. The import can be forced at any time with the Synchronise now action.

Client FTP server

If the client provides their own server, the type (FTP / FTPS / SFTP) must be specified accordingly. For SFTP, it is allowed for the files to be in a specific subfolder, which should be indicated in the Path field.

File contents

• In Excel, the tabs must be named and ordered as indicated in the specification.
• In CSV, each table goes in a file, with a numeric prefix to fix the loading order.
• In any case, only columns with data to be transferred are included. It is not necessary to create all columns for all fields.

Detailed field specification

The complete list of tabs, table names, and available fields is published at smart.smartosh.com/kb020 (authentication required).

Annex: controlled errors

During processing, the connector may emit the following errors:

Message	Explanation
El formato no está correctamente definido y no se dispone de información sobre el mapeo '{0}'	The Operation column is missing in the Excel.
No hay valor en la columna '{0}' y este es necesario, {1}	Mandatory fields are missing or empty.
Se ha encontrado más de un registro a actualizar basándose en el identificador. La operación no puede continuar.	Records identified by code must be unique.
No existe la {1} con Código {0} ni tampoco en los códigos equivalentes...	Data error: cannot determine whether to insert or update.
Error al asignar el valor '{0}' al campo '{1}'	Incorrect value format (depends on the field).
Error al EscribirFTP en la conexión	Connection or permission problem when writing to the FTP.

Caution

If a synchronization finishes with errors, always check the connector’s history before retrying the operation. Forcing Synchronise now with defective data will only repeat the error.

---

Source: KB020 «Generic connector for data synchronization via exchange files», Consultancy module, version 1.5, 19-04-2021.