$siteUrl = "[SiteURL]"
$listName = "[List NAME]"
$excelFilePath = "C:\Users\user254\Downloads\ObservationsImport1.xlsx"
$lookupColumns = @{
"Location_ID" = "WS_Locations"
"Line_ID" = "WS_Line"
"Area_ID" = "WS_Area"
"Sub_Area_ID" = "WS_Sub_Area"
"Category_ID" = "WS_Category"
"Sub_Category_ID" = "WS_Sub_Category"
}
$nonLookupColumns = @("OBSERVATION_DATE", "SHIFT", "TYPE", "BEHAVIOR", "AT_RISK_BY_UNSAFE_COND", "COMMENTS", "TIME_ZONE", "ROLE")
Connect-PnPOnline -Url $siteUrl -UseWebLogin
$excelData = Import-Excel -Path $excelFilePath
$excelData.Count
$lookupDataCache = @{}
function Get-LookupId {
param (
[string]$lookupColumn,
[string]$lookupValue
)
$itemId=0
$lookupListName = $lookupColumns[$lookupColumn]
$lookupItems = Get-PnPListItem -List $lookupListName -Fields "Id",$lookupColumn
$lookupDataCache[$lookupColumn] = @{}
foreach ($item in $lookupItems) {
if($item.FieldValues[$lookupColumn] -eq $lookupValue) {
$itemId=$item.Id
}
}
return $itemId
}
foreach ($row in $excelData) {
$itemProperties = @{}
foreach ($column in $nonLookupColumns) {
if (($column -eq "OBSERVATION_DATE")) {
$excelDate = $row.$column
$baseDate = [datetime]"1900-01-01"
$convertedDate = $baseDate.AddDays($excelDate - 2)
$itemProperties[$column] = $convertedDate
}else {
$itemProperties[$column] = $row.$column
}
}
foreach ($lookupColumn in $lookupColumns.Keys) {
$lookupValue = $row.$lookupColumn
if ($lookupValue) {
$lookupId = Get-LookupId -lookupColumn $lookupColumn -lookupValue $lookupValue
$itemProperties[$lookupColumn] = $lookupId
}
}
try {
Add-PnPListItem -List $listName -Values $itemProperties
#Write-Host "Item added successfully!" -ForegroundColor Green
} catch {
Write-Host "An error occurred while adding the item: $($_.Exception.Message)" -ForegroundColor Red
}
}
Disconnect-PnPOnline
