Purple Purple - 3 months ago 28
PowerShell Question

CSV value being overwritten

I been trying to add an import-csv into a script from technet to create sub sites on SharePoint Online. End result would be having sub sites made from a CSV. E.G. Year 7, Year 8. Year 9.

I have done the following:

Import-Csv 'C:\sp\import.csv'|`
ForEach-Object{
$Urlsub = $_.Urlsub
$Title = $_.Title
}
$wci.Url = $Urlsub
$wci.Title = $Title


The CSV:

Urlsub Title
------ -----
Year7 Year 7
Year8 Year 8


This works fine for only the last row in the CSV. It seems to overwrite everything leaving me with the last row.

If I change it to:
$Urlsub += $_.Urlsub
It will add all the columns for $Urlsub to the same array.

How can I go about importing the CSV without it overwriting the previous value ?

Full script without csv:

#Credentials to connect to office 365 site collection url
$url ="xxx"
$username="xxx"
$password="xxx"
$Password = $password |ConvertTo-SecureString -AsPlainText -force

Write-Host "Load CSOM libraries" -foregroundcolor black -backgroundcolor yellow
Set-Location $PSScriptRoot
Add-Type -Path (Resolve-Path "Microsoft.SharePoint.Client.dll")
Add-Type -Path (Resolve-Path "Microsoft.SharePoint.Client.Runtime.dll")


Write-Host " CSOM libraries" -foregroundcolor black -backgroundcolor yellow
Set-Location $PSScriptRoot
Add-Type -Path (Resolve-Path "Microsoft.SharePoint.Client.dll")
Add-Type -Path (Resolve-Path "Microsoft.SharePoint.Client.Runtime.dll")
Write-Host "CSOM libraries loaded successfully" -foregroundcolor black -backgroundcolor Green

Write-Host "authenticate to SharePoint Online Tenant site $url and get ClientContext object" -foregroundcolor black -backgroundcolor yellow
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($url)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $password)
$Context.Credentials = $credentials
$context.RequestTimeOut = 5000 * 60 * 10;
$web = $context.Web
$site = $context.Site
$context.Load($web)
$context.Load($site)
try
{
$context.ExecuteQuery()
Write-Host "authenticateed to SharePoint Online site collection $url and get ClientContext object succeefully" -foregroundcolor black -backgroundcolor Green
}
catch
{
Write-Host "Not able to authenticateed to SharePoint Online site collection $url $_.Exception.Message" -foregroundcolor black -backgroundcolor Red
return
}


#creating site using WebCreationInformation calss
Write-Host "creating subsite using custom webtemplate" -foregroundcolor black -backgroundcolor yellow
$wci = New-Object Microsoft.SharePoint.Client.WebCreationInformation
$wci.Url = "Year7"
$wci.Title = "Year 7"
$wci.UseSamePermissionsAsParentSite = $true
$wci.WebTemplate = "{D0714A63-356A-4B73-815B-6E1DF824237F}#Template"
$wci.Language = 1033
$blogWeb = $site.RootWeb.Webs.Add($wci);
try
{
$context.ExecuteQuery();
Write-Host "Sub site created successfully using custom webtemplate" -foregroundcolor black -backgroundcolor Green
}
catch
{
Write-Host "Error while creating the Sub site using custom webtemplate" $_.Exception.Message -foregroundcolor black -backgroundcolor RED
}

Answer

You end up with the last value because your variable assignment falls outside of the loop which is reading the CSV file. As such, you get the last thing the variable was set to (the last line from the CSV).

I expect you'll need to create instances of WebCreationInformation for each row in the csv file, then add each of those to $site.RootWeb.Webs as I attempt to do in this example.

Import-Csv 'C:\sp\import.csv' | ForEach-Object{
    $wci = New-Object Microsoft.SharePoint.Client.WebCreationInformation
    $wci.Url = $_.Urlsub
    $wci.Title = $_.Title
    $wci.UseSamePermissionsAsParentSite = $true
    $wci.WebTemplate = "{D0714A63-356A-4B73-815B-6E1DF824237F}#Template"
    $wci.Language = 1033
    $blogWeb = $site.RootWeb.Webs.Add($wci);
}