How-to OleDB Dataprovider

Introduction

OleDB Dataprovider allows importing from different sources such as Excel or CSV files, databases and other systems.

Import from CSV files

File structure

The files from which you want to import are ideally located in one directory. This can look as follows, for example:

• Import

  • Personal data.csv

  • Function data.csv

  • Organization data.csv

  • Employment data.csv

Configuration file

So that the OleDB DataProvider or more exactly the OleDB connection can be established cleanly, a schema.ini file must be present in the directory, which contains the metadata over the individual files. In it e.g. the delimeter can be configured.

[Personendaten.csv]
Format=Delimited(|)
HDR=Yes

[Funktionsdaten.csv]
Format=Delimited(|)
HDR=Yes

[Organisationsdaten.csv]
Format=Delimited(|)
HDR=Yes

[Anstellungsdaten.csv]
Format=Delimited(|)
HDR=Yes

Connection String

Unlike an Excel file, for example, the connection string is not created on the file but on the folder. In the corresponding query, the file is then specified instead of the table name. Here is an example of a OleDbConnectionParameters:

CustomConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\temp;Extended Properties=\"Text;\""

The corresponding query call is then made as follows using an example for a OleDbCoreIdentityDataProviderConfiguration:

GetForeignIdsCommand = "SELECT Personalnummer FROM Personendaten.csv"