Versions Compared

Version Old Version 6 New Version 7
Changes made by

Christina Burkhard

Roger Fankhauser

Saved on

Key

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

...

Code Block
[Personendaten.csv]
Format=Delimited(|)
HDR=Yes
MaxScanRows=0

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

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

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

Format=Delimited(|)

Defines the delimiter of the CSV file. Here it’s defined as the pipe |

HDR=Yes

If YES, get column names from the first column to reference them by their name. If NO, columns are simply numbered

MaxScanRows=0

Defines how many rows are considered for defining the type of a column. 0 means it considers all the rows which is recommended, because it increases the precision of defining the data type.

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:

...

Code Block
GetForeignIdsCommand = "SELECT Personalnummer FROM Personendaten.csv"

Check CSV import locally with Powershell

For quickly checking if your SQL and the results are right, you can use this Powershell script:

Code Block
languagepowershell
$conn = New-Object System.Data.OleDb.OleDbConnection
$conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Temp;Extended Properties=""Text;HDR=YES;Format=Delimited(;)"""
$conn.Open()
$command = New-Object System.Data.OleDb.OleDbCommand("SELECT " +
                                                    "[Tel-Erreichbarkeit]," +
                                                    "[Tel-Direktwahl]," +
                                                    "[Tel-Nebenstelle]," +
                                                    "[Tel-Abteilung]," +
                                                    "[Tel-Sekretariat]," +
                                                    "[Tel-Fax]," +
                                                    "[Provisioning-Template]," +
                                                    "[AnzeigeNameDECT]," +
                                                    "[Caesar-Sync]," +
                                                    "[Caesar-FreeSeating] " +
                                                    "FROM [prov_Masterliste Telefonie ACM_2021-07-07.csv]")
$command.Connection = $conn

$reader = $command.ExecuteReader()

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

$conn.Close()