How To Import Site Collection And Export The Same To New CSV For Each SC In SharePoint

Today, I am writing this article on how to import and export site collection details in .CSV (Comma-Separated Values) file in SharePoint. Thus, let me describe this task. We need the details of site collections and their subsite details, like site collection URL, Subsite URL, Content DB name, UID (GID), size of content DB etc.

We have a list (.CSV (Comma-Separated Values)) of site collection details from the client farm of SharePoint where thousands of Site collections are deployed. Now, we are looking to get all the details for each SharePoint site collection and result should be in .CSV (comma-separated values) file for each collection seperatly.

The case note is given below for more details in the screenshots.
  • Client input is given below in CSV file from different Web Application: A .CSV (Comma-Separated Values) file is full of site collection URL's.

  • The output is like the screen given below. For each site collection, new CSV file includes some more information on the same. Refer to the screenshot given below.

We need to run PowerShell script command to complete the requirement given above. I am going to perform this task by following the steps given below with the screenshots. We will add the code snippet as well.
  • If you want to perform this on demo environment, login to the Server and copy all site collection to CSV files to do the same. If you want to get Site collection details from SharePoint farm, run Get-SPSite command and copy all site collections and copy to csv file.
  • Once .CSV (Comma-Separated Values) file is ready with the details, we need to write a PowerShell script for the same.
  • Open PowerShell ISE with an administrator permission and copy the written code to the same with the required modification like path, location of file, view which you want to get in CSV file etc.
  • We can see in the code clearly mentioned “Clear-Host” in the first only, so we will not get other code details on the screen.
  • Moving to the next, we need to write the code for reading CSV file, which you copied to .CSV (Comma-Separated Values) file and saved the same location to the code. See the screenshot given below for more details.

  • Once this is done code for start looping and check that existing site collection details are done one by one.
  • Generate the file ddmmyy format with ID (it’s your option, if you want and you can change it also, as you need).
  • Pass the variable to get all SharePoint site collection details from CSV file.
  • Following the same, we will get it for DB name, size, Subsite etc.
  • Once everything is done, we will write the code to export for each site collection. For each file, you will get all the details from the attached code.
  • Save this file to the same location, where you have saved .CSV (Comma-Separated Values) (not Mandate).
Now, go to SharePoint Server. Make sure that you have farm administrator permission to execute this script and copy the same file to the same Server drive.
  • Open SharePoint Management shell with admin rights, drag and drop the same file to PowerShell Management shell and navigate to the path, as shown below.

    1. ###########################################################################################################  
    2. #SharePoint: How to Import Site collection and export same to New .csv for each Site Collection in details#  
    3. #Created Details: Feburary 2017                                                                           #  
    4. #reated By: Prashant Kumar                                                                                #  
    5. ###########################################################################################################  
    6.  
    7.  
    8. #First clear whole screen      
    9. Clear-Host  
    10.  
    11. #Variables  
    12. $Path = "C:\Users\Test\Desktop\mytest"  
    13.   
    14.   
    15. $importLocation="C:\Users\Test\Desktop\mytest\sss.csv"  
    16.   
    17. $csv=Import-Csv $importLocation  
    18. write-host "Reading csv from :" $importLocation;  
    19. foreach($row in $csv)  
    20. {  
    21.   $url = $row.URL;  
    22.   write-host "Processing siteurl:" $url;  
    23.   $s = Get-SPSite -Identity $url  
    24.   $FileLocation = "$($path)\$($s.id)_$(get-date -f yyyyMMdd).csv"   
    25.   $SPWebs = $S | Get-SPWeb -Limit all  
    26.   $ContentDB = Get-SPContentDatabase -site $url   
    27.   foreach ($web in $SPWebs){  
    28.     
    29.   write-host "Processing web url:" $url;  
    30.   $properties = @{  
    31.                                     'Site Collection URL'="$($Url)";  
    32.                                     'Sub Site URL'="$($web.Url)";  
    33.                                     'ID'="$($web.ID.Guid)";  
    34.                                     'ContentDBName'="$($ContentDB.Name)";  
    35.                                     'ContentDBSize'="$($ContentDB.DiskSizeRequired /1024/1024)"; #it will calculate size in MB  
    36.                                                 }#properties  
    37.                     $obj1 = New-Object –TypeName PSObject –Property $properties  
    38.                     Write-Output $obj1|Select-Object 'Site Collection URL','Sub Site URL','ID','ContentDBName','ContentDBSize'|Export-Csv -Path $FileLocation -Delimiter "," -Encoding UTF8 -Append -NoTypeInformation -Force  
    39.     
    40.       
    41.    }  
    42. }  

  • Once the file has been copied to the path, hit enter and wait for the result. It will run successfully and result will be, as shown below.



    Now, open any one of the .CSV (Comma-Separated Values) file and verified that we are getting an expected result.



    Also, we can check the above sub site collections exists under the given site collection.


Thanks.