Help With Importing CSV into SharePoint Online - Ignore Blank Values

Question

Hi

I have a simple script that imports a CSV and then uses Add-PNPListItem to insert the rows into a SharePoint Online list. All works ok except for when it comes across a blank date/time field.

Having a blank date/time is acceptable in terms of the data structure and is valid but Powershell errors saying it is expecting a value. How can I wrap around an IF statement to ignore writing to that column if there is no value in the CSV?

My script is this:

$credentials = Get-Credential -Message "Please Enter SharePoint Online credentials"
$Site="https://my site collection"
Connect-PnPOnline -Url $Site -Credentials $credentials -UseWebLogin
$CustomerData = Import-CSV "C:\Users\my locaation\thefiletobeimported.csv" -Delimiter '|'
foreach ($Record in $CustomerData){
Add-PnPListItem -List "EDBreaches" -Values @{
"Title"= $Record.'Patient';
"Attend_UID"=$Record.'Attend_UID';
"LeftDepartment"= $Record.'LeftDepartment';
"Arrival"= $Record.'Arrival';
"Triage"= $Record. 'Triage';
"Seen"= $Record.'Seen';
"Departure"= $Record.'Departure';
"ModeOfArrival"= $Record.'ModeOfArrival';
"AllocatedStream"= $Record.'AllocatedStream';
"SourceOfAdmission"=$Record.'SourceOfAdmission';
"InitialComplaint"=$Record.'InitialComplaint';
"WaitMins"=$Record.'WaitMins';
"BreachStatus"=$Record.'BreachStatus';
"PresentingComplaint"=$Record.'PresentingComplaint';
"BreachNumber"=$Record.'BreachNumber';
"BreachReason"=$Record.'BreachReason';
"BedRequired"=$Record.'BedRequired';
"NHSNumber"=$Record.'NHSNumber';
"Disposal"=$Record.'AttendanceDisposalDescription';
"Validation"=$Record.'Validation';
"SecondaryReason"=$Record.'Secondary Reason';
"Comments"=$Record.'Comments';
"ValidatedBy"=$Record.'Validated By'

}
}

Left , Arrival, Triage, Seen and Departure are all date fields that may or may not have a date time. For those that do not have a date/time,. how do I ignore that column. I do not want to pass a default date into the column.

Can anyone help?

Thanks - Jon

Answer 1

Try something like this:

$IgnoreEmpty = "LeftDepartment,Arrival,Triage,Seen,Departure" -split ","

$credentials = Get-Credential -Message "Please Enter SharePoint Online credentials"
$Site = "https://my site collection"
Connect-PnPOnline -Url $Site -Credentials $credentials -UseWebLogin
$CustomerData = Import-Csv "C:\Users\my locaation\thefiletobeimported.csv" -Delimiter '|'
foreach ($Record in $CustomerData) {
    # always populate these keys with whatever value is in the CSV
    $val = @{
        "Title"               = $Record.'Patient';
        "Attend_UID"          = $Record.'Attend_UID';
        "ModeOfArrival"       = $Record.'ModeOfArrival';
        "AllocatedStream"     = $Record.'AllocatedStream';
        "SourceOfAdmission"   = $Record.'SourceOfAdmission';
        "InitialComplaint"    = $Record.'InitialComplaint';
        "WaitMins"            = $Record.'WaitMins';
        "BreachStatus"        = $Record.'BreachStatus';
        "PresentingComplaint" = $Record.'PresentingComplaint';
        "BreachNumber"        = $Record.'BreachNumber';
        "BreachReason"        = $Record.'BreachReason';
        "BedRequired"         = $Record.'BedRequired';
        "NHSNumber"           = $Record.'NHSNumber';
        "Disposal"            = $Record.'AttendanceDisposalDescription';
        "Validation"          = $Record.'Validation';
        "SecondaryReason"     = $Record.'Secondary Reason';
        "Comments"            = $Record.'Comments';
        "ValidatedBy"         = $Record.'Validated By'
    }
    # only populate these keys if the CSV contains a value
    $IgnoreEmpty |
        ForEach-Object{
            if ($record.$_.Length -gt 0){
                $val[$_] = $record.$_
            }
        }
   Add-PnPListItem -List "EDBreaches" -Values $val
}