question

LawtonJon-4501 avatar image
0 Votes"
LawtonJon-4501 asked LawtonJon-4501 commented

Help With Importing CSV into SharePoint Online - Ignore Blank Values

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

office-sharepoint-onlinewindows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered LawtonJon-4501 commented

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
 }
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Absolute genuius. Thank you so much. Exactly what I needed. That's a very handy snippet of code! Take care. Jon

0 Votes 0 ·