Búsqueda de sitios web

Exportar datos de lista de SharePoint a una tabla de SQL Server usando PowerShell

Requisito: Exportar datos de la lista de SharePoint a la tabla de SQL Server.

Para una herramienta de inteligencia empresarial construida internamente, teníamos el requisito de exportar datos de listas de SharePoint a una tabla de SQL Server. Como Microsoft no admite la consulta directa de bases de datos de contenido de SharePoint, también podría causar problemas de rendimiento en su granja de SharePoint. ¡No pensemos más en eso! Por supuesto, existen productos de terceros disponibles en el mercado para hacer esto. Sin embargo, se puede utilizar PowerShell para cumplir con este requisito.

Este script de PowerShell recupera las listas de SharePoint proporcionadas, recorre en iteración todos los campos disponibles, crea dinámicamente una tabla de SQL Server y luego inserta los datos después de traducirlos y los inserta en la tabla de la base de datos de SQL Server.

Aquí está mi script de PowerShell para convertir la lista de SharePoint en una tabla de SQL Server y extraer datos de SharePoint a SQL:

Secuencia de comandos de PowerShell para extraer datos de la lista de SharePoint en una tabla de SQL Server:


Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 

Function Create-List2Table($ListName,$TableName, $WebURL)
{
#Configuration Variables
$DatabaseServer="SP16-SQLServer01"
$DatabaseName="SharePointBI"
#SQL Authentication 
$UserID="UserID"
$Password="Password"

#Log File
$CurrentDir=Split-Path -Parent -Path $MyInvocation.MyCommand.Definition
$LogFile = $CurrentDir+ "\" + $(Get-Date -format "yyyyMMdd_hhmmss")+".txt"

#Get Web, List and Fields
$Web= Get-SPWeb $WebURL
$List= $Web.Lists[$ListName]
#Get all required fields from the lists 
$ListFields = $List.Fields | Where { ($_.Hidden -ne $true ) -and ($_.ReadOnlyField -ne $true ) -and ($_.InternalName -ne "Attachments") -and ($_.InternalName -ne "ContentType") }

#Function to Execute given SQL Query
Function Execute-SQLQuery([string]$Query)
{
    #Write-Host "Executing SQL: ", $Query
    #Connection object
    $Connection = New-Object System.Data.SqlClient.SqlConnection("server=$DatabaseServer;database=$DatabaseName;User Id=$UserID;Password=$Password")
    try
    {
        $Connection.open() 
        $cmd = new-object System.Data.SqlClient.SqlCommand ($Query, $Connection)
        $ReturnValue = $cmd.ExecuteNonQuery()
    }
    catch  { Write-Host "Error: ", $error[0];  "$($Query): $($_.Exception.Message)" >> $LogFile }
    finally{ $Connection.close() }

}

#Function to Drop Table, if exists!
Function Drop-Table([string]$TableName)
{
  $Query = "IF (OBJECT_ID('[dbo].[$($TableName)]','U') IS NOT NULL) DROP TABLE [dbo].[$($TableName)]"
  
  #Run the Query 
  Execute-SQLQuery $Query
}

#Get SQL column Definition for SharePoint List Field
Function Get-ColumnDefinition([Microsoft.SharePoint.SPField]$Field) 
{
    $ColumnDefinition=[string]::Empty
    Switch($Field.Type)
    {
      "Boolean" { $ColumnDefinition = '['+ $Field.InternalName +'] [bit] NULL '}
      "Choice" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar](MAX) NULL '}
      "Currency" { $ColumnDefinition = '['+ $Field.InternalName +'] [decimal](18, 2) NULL '}
      "DateTime" { $ColumnDefinition = '['+ $Field.InternalName +'] [datetime] NULL '}
      "Guid" { $ColumnDefinition = '['+ $Field.InternalName +'] [uniqueidentifier] NULL '}
      "Integer" { $ColumnDefinition = '['+ $Field.InternalName +'] [int] NULL '}
      "Lookup" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (500) NULL '}
      "MultiChoice" { $ColumnDefinition = '['+ $Field.InternalName +'] [nText] (MAX) NULL '}
      "Note" { $ColumnDefinition = '['+ $Field.InternalName +'] [nText] NULL '}
      "Number" { $ColumnDefinition = '['+ $Field.InternalName +'] [decimal](18, 2) NULL '}
      "Text" { $ColumnDefinition = '['+ $Field.InternalName +'] [nVarchar] (MAX) NULL '}
      "URL" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (500) NULL '}
      "User" { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (255) NULL '}      
      default { $ColumnDefinition = '['+ $Field.InternalName +'] [nvarchar] (MAX) NULL '}
  }
  return $ColumnDefinition
}

################ Format Column Value Functions ######################
Function Format-UserValue([object] $ValueToFormat)
{
     $Users = [string]::join("; ",( $ValueToFormat | Select -expandproperty LookupValue))
     $Users = $Users -replace "'", "''"
     return "'" + $Users + "'"
}

Function Format-LookupValue([Microsoft.SharePoint.SPFieldLookupValueCollection] $ValueToFormat)
{
     $LookupValue = [string]::join("; ",( $ValueToFormat | Select -expandproperty LookupValue))
     $LookupValue = $LookupValue -replace "'", "''"
     return "'" + $LookupValue + "'"
}

Function Format-DateValue([string]$ValueToFormat)
{
  [datetime] $dt = $ValueToFormat
  return "'" + $dt.ToString("yyyyMMdd HH:MM:ss") + "'"
}

Function Format-MMSValue([Object]$ValueToFormat)
{
  return "'" + $ValueToFormat.Label + "'"
}

Function Format-BooleanValue([string]$ValueToFormat)
{
  if($ValueToFormat -eq "Yes") {return 1} else { return 0}
}

Function Format-StringValue([object]$ValueToFormat)
{
  [string]$result = $ValueToFormat -replace "'", "''"
  return "'" + $result + "'"
}

#Function to get the value of given field of the List item
Function Get-ColumnValue([Microsoft.SharePoint.SPListItem] $ListItem, [Microsoft.SharePoint.SPField]$Field) 
{
    $FieldValue= $ListItem[$Field.InternalName]
    
    #Check for NULL
    if([string]::IsNullOrEmpty($FieldValue)) { return 'NULL'}
    
    $FormattedValue = [string]::Empty
    
    Switch($Field.Type)
    {
    "Boolean"  {$FormattedValue =  Format-BooleanValue($FieldValue)}
    "Choice"  {$FormattedValue = Format-StringValue($FieldValue)}
    "Currency"  {$FormattedValue = $FieldValue}
    "DateTime"  {$FormattedValue = Format-DateValue($FieldValue)}
    "Guid" { $FormattedValue = Format-StringValue($FieldValue)}
    "Integer"  {$FormattedValue = $FieldValue}
    "Lookup"  {$FormattedValue = Format-LookupValue($FieldValue) }
    "MultiChoice" {$FormattedValue = Format-StringValue($FieldValue)}
    "Note"  {$FormattedValue = Format-StringValue($Field.GetFieldValueAsText($ListItem[$Field.InternalName]))}
    "Number"    {$FormattedValue = $FieldValue}
    "Text"  {$FormattedValue = Format-StringValue($Field.GetFieldValueAsText($ListItem[$Field.InternalName]))}
    "URL"  {$FormattedValue =  Format-StringValue($FieldValue)}
    "User"  {$FormattedValue = Format-UserValue($FieldValue) } 
     #Check MMS Field
     "Invalid" { if($Field.TypeDisplayName -eq "Managed Metadata") { $FormattedValue = Format-MMSValue($FieldValue) } else { $FormattedValue =Format-StringValue($FieldValue)}  }
    default  {$FormattedValue = Format-StringValue($FieldValue)}
  }
  Return $FormattedValue
}

#Create SQL Server table for SharePoint List
Function Create-Table([Microsoft.SharePoint.SPList]$List)
{
    #Check if the table exists already
    $Query="CREATE TABLE [dbo].[$($TableName)]([ID] [int] NOT NULL PRIMARY KEY, "
    foreach ($Field in $ListFields)
    {
        $Query += Get-ColumnDefinition($Field) 
        $Query += ","
    }
    $Query += ")"

    #Run the Query   
    Execute-SQLQuery $Query  
}

#Insert Data from SharePoint List to SQL Table
Function Insert-Data([Microsoft.SharePoint.SPList]$List)
{ 
    #Iterate through each row from the list 
    $ListItems= $List.Items # | where {$_["ID"] -eq 820}

    #Progress bar counter
    $Counter=0
    $ListItemCount=$ListItems.Count
    
    Write-host "Total SharePoint List Items to Copy:" $ListItemCount    
    foreach ($Item in $ListItems) 
    {

        Write-Progress -Activity "Copying SharePoint List Items. Please wait...`n`n" -status "Processing List Item: $($Item['ID'])" -percentComplete ($Counter/$ListItemCount*100)

        $sql = new-object System.Text.StringBuilder
        [void]$sql.Append("INSERT INTO [dbo].[$($TableName)] ( [ID] ")
        $vals = new-object System.Text.StringBuilder
        [void]$vals.Append("VALUES ("+ $Item["ID"])
        
        foreach ($Field in $ListFields) 
        {
            [void]$sql.Append(",[$($Field.InternalName)]")
            $ColumnValue =  Get-ColumnValue $Item $Field
            [void]$vals.Append( ","+ $ColumnValue)
        }
       
    [void]$sql.Append(") ")
    [void]$vals.Append(") ")
    
    #Combine Field and Values
    $SQLStatement = $sql.ToString() + $vals.ToString()
    
    #Run the Query   
    Execute-SQLQuery $SQLStatement 
    
    $Counter=$Counter+1;  
    }
"Total SharePoint List Items Copied: $($ListItemCount)" >> $LogFile
}

 #Call functions to export-import SharePoint list to SQL table
 Drop-Table $TableName
 Create-Table $List
 Insert-Data $List
}

#Call the function to Create SQL Server Table from SharePoint List
Create-List2Table -ListName "Projects" -TableName "ProjectData" -WebURL "https://portal.crescent.com/projects"
#Create-List2Table -ListName "Documents" -TableName "Documents" -WebURL "https://portal.crescent.com"

Asegúrese de tener la base de datos creada (en mi caso, es "SharePointBI") y que el nombre de usuario proporcionado en el script tenga acceso DBO o similar a la base de datos antes de ejecutar el script.

Artículos relacionados