...
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 | ||
---|---|---|
| ||
$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() |