Versions Compared

Version Old Version 12 New Version Current
Changes made by

Raffael Limberger

Urs Mathis

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Introduction

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

...

Col3=Ende_Anstellung DateTime(yyyy.mm.dd) means that column 3 should be read out as DateTime and is present in the original file in the format yyyy.mm.dd.

Important: You cannot specify a single column. If specification of the DataType for a column is needed, all columns must be defined.

Important: When using csv files AND if one of the column headers contain a dot (e.g. “Acad. Title”) in the column header name, this dot has to be replaced in the SQL query in the C# code with a hash # sign and it is recommended to use then the “as” statement to define a proper column name for further processing.

Examples:

Column name in CSV

SQL query in C#

Acad. Title

"SELECT [Acad# Title] as Akad_Titel FROM [my.csv] WHERE [foreign_id] = '{0}'

Geb.Datum

"SELECT [Geb#Datum] as Geb_Datum FROM [my.csv] WHERE [foreign_id] = '{0}'

Table1.Column4

"SELECT [Table1#Column4] as DateOfBirth FROM [my.csv] WHERE [foreign_id] = '{0}'

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:

...

For quickly checking if your SQL and the results are right, you can use this the Powershell script located in C:\Git\DevOps\Scripts\PowerShellScripts\Test_OLEDB_Excel_CSV_Import.ps1 (copy below).

Code Block
languagepowershell
cls
########################################################################################################
# Script to locally test an OLEDB connection to a file (e.g. CSV)                                      #
########################################################################################################
$conn = New-Object System.Data.OleDb.OleDbConnection
$conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\XXXX\Import\TempExternal_Employees;Extended Properties=""Text;HDR=YES;Format=Delimited(;)"""
$conn.Open()
$command

#Start of variable section --> adapt the SQL query to your needs
#Consult Confluence page "How-to OleDB Dataprovider" for hints and tricks
$command1 = New-Object System.Data.OleDb.OleDbCommand("SELECT [Personalnummer] FROM [my.csv] AS [externeMitarbeiter] WHERE [externeMitarbeiter].[Personalnummer] is not null")
$command2 = New-Object System.Data.OleDb.OleDbCommand("SELECT " +
                                                                  "[Tel-ErreichbarkeitPersonalnummer], " +
                                                                  "[Tel-DirektwahlPKI], " +
                                                                  "[Tel-NebenstelleAnrede],  " +
                                                                  "[Tel-AbteilungBriefanrede], " +
                                                    "[Tel-Sekretariat]," +              "[Akad# Titel] as Akad_Titel, " +
                                                                  "[Tel-FaxVorname], " +
                                                                  "[Provisioning-TemplateNachname], " +
                                                                  "[AnzeigeNameDECT],Geb# Nachname] as Geb_Nachname, " +
                                                                  "[Caesar-SyncBenutzername], " +
                                                                  "[Caesar-FreeSeatingOffice] " +
                                                      "FROM [prov_Masterliste Telefonie ACM_2021-07-07.csv]")
$command            "FROM [my.csv] " +
                                                                  "WHERE [Personalnummer] = '123'")
#End of variable section

Write-Host ""
Write-Host ""
Write-Host "------ (SQL Query 1) ------"
Write-Host $command1.CommandText
Write-Host ""
Write-Host ""
Write-Host "------ (SQL Query 2) ------"
Write-Host $command2.CommandText
Write-Host ""
Write-Host ""

$command1.Connection = $conn
$reader1 = $command1.ExecuteReader()
$currentDs = 1

Write-Host ""
Write-Host "------ (SQL Import Query 1 - List foreign_id's) ------"
Write-Host ""

while($reader1.Read())
{
    for($i = 0; $i -lt $reader1.FieldCount; $i++)
    {
        $fieldName = $reader1.GetName($i)
        Write-Host "Record: $($currentDs) - $($fieldName): $($reader1.GetValue($i))"
    }
    $currentDs++
}


$command2.Connection = $conn

$reader$reader2 = $command$command2.ExecuteReader()

$currentDs = 1
$column1Width = 25
$column2Width = 40
while($reader$reader2.Read())
{
    Write-Host ""
    Write-Host ""
    Write-Host "------ (Current row: $currentDs) Required row according WHERE clause in SQL Query 2 ------"
    Write-Host ""
    Write-Host ("{0,-$column1Width} {1,-$column2Width}" -f "Attribute", "Value")
    Write-Host ("{0,-$column1Width} {1,-$column2Width}" -f "----------------------", "----------------------")

    for($i = 0; $i -lt $reader$reader2.FieldCount; $i++)
    {
        $fieldName = $reader$reader2.GetName($i)
        Write-Host ("{0,-$column1Width} "$($fieldName): $($reader{1,-$column2Width}" -f $fieldName, $reader2.GetValue($i))"
    }
    $currentDs++
}

$reader1.Close()
$reader2.Close()
$conn.Close()