Introduction
...
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.
...
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}' |
...
For quickly checking if your SQL and the results are right, you can use the Powershell script located in C:\Git\DevOps\Scripts\PowerShellScripts\Test_OLEDB_Excel_CSV_Import.ps1.
| Code Block | ||
|---|---|---|
| ||
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:\ |
...
Git\DevOps\Customer Files\MDBA\Import\External_Employees;Extended Properties=""Text;HDR=YES;Format=Delimited(;)""" $conn.Open() #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 [MDKExterneMitarbeiter.csv] AS [externeMitarbeiter] WHERE [externeMitarbeiter].[Personalnummer] is not null") $command2 = New-Object System.Data.OleDb.OleDbCommand("SELECT " + "[ |
...
Personalnummer], " + "[ |
...
PKI], " + "[ |
...
Anrede], " + "[ |
...
Briefanrede], " + |
...
"[Akad# Titel] as Akad_Titel, " + "[ |
...
Vorname], " + "[ |
...
Nachname], " + "[ |
...
Geb# Nachname] as Geb_Nachname, " + "[ |
...
Benutzername], " + "[ |
...
Office] " + "FROM [MDKExterneMitarbeiter.csv] " + |
...
"WHERE [Personalnummer] = '55505262'") #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 $reader2 |
...
= |
...
$command2.ExecuteReader() |
...
$currentDs = 1 $column1Width = 25 $column2Width = 40 while( |
...
$reader2.Read()) { Write-Host "" Write-Host "" Write-Host "------ ( |
...
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 |
...
$reader2.FieldCount; $i++) { $fieldName = |
...
$reader2.GetName($i) Write-Host |
...
("{0,-$column1Width} {1,-$column2Width}" -f $fieldName, $reader2.GetValue($i)) |
...
} $currentDs++ } $reader1.Close() $reader2.Close() $conn.Close() |