PowerShell Scripts for Export and Import SharePoint List to SQL table

Kumarmaddu
Posted by Kumarmaddu under PowerShell category on | Points: 40 | Views : 3751
######### Export SPList to SQL #############


function ExecuteSQLInsert($sqlVAL1,$sqlVAL2,$sqlVAL3)
{
$ErrorActionPreference = 'stop' # Prepare script for stopping
$SqlCmd.CommandText ="INSERT INTO Companydetails(CompanyId,CompanyName,CompanyName1) VALUES ($sqlVAL1,'$sqlVAL2','$sqlVAL3')" # Configure TSQL

Try
{
$SqlCmd.ExecuteNonQuery() #
}
Catch
{
Write-Warning "$_" # Report Errors
Write-Warning $SqlCmd.CommandText
Write-host
}

}

######connect to SQL database windows authentication ###########

$connection= new-object system.data.sqlclient.sqlconnection #Set new object to connect to sql database

$Connection.ConnectionString ="Your SQL Details"
# Connectiongstring setting for <ServerName> <databasename> with window authentication

#$Connection.ConnectionString ="server=<ServerName>;database=<databasename>;User Id=<username>;Password=<password>;trusted_connection=False; # Connectiongstring setting for <ServerName> <databasename> with SQL authentication <username><password>

Write-host "connection information:"

$connection #List connection information

Write-host "Connecting to database.."

$connection.open() #Open Connection

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand #setting object to use sql commands

$SqlCmd.Connection = $connection


######### Get SPList ########
$spWeb = Get-SPWeb -identity "http://SiteName/" # Get SPWeb

$list = $spWeb.Lists["CompanyDetails"] # Get SPList

foreach ($item in $list.items)
{
$groupcol = $Item["CompanyName1"]
$test
foreach($group in $groupcol)
{
$test+=$group.lookupvalue
}

$Lookup = new-object Microsoft.SharePoint.SPFieldLookupValue($item["CompanyName"])
$User = $Lookup.LookupValue;


ExecuteSQLInsert $item.ID $User value

}

$connection.Close()

$spWeb.Dispose()


######Import SQLData to SPList ###########

$connection= new-object system.data.sqlclient.sqlconnection #Set new object to connect to sql database

$Connection.ConnectionString ="SQl Server Details" # Connectiongstring setting for <ServerName> <databasename> with window authentication

#$Connection.ConnectionString ="server=<ServerName>;database=<databasename>;User Id=<username>;Password=<password>;trusted_connection=False; # Connectiongstring setting for <ServerName> <databasename> with SQL authentication <username><password>

Write-host "connection information:"

$connection #List connection information

Write-host "Connecting to database.."

$connection.open() #Open Connection


######### SQL query #############

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand #setting object to use sql commands

$SqlQuery = "SELECT orderId, productId, UnitPrice, Quantity, Discount FROM OrderDetails " #set SQL query

$SqlCmd.CommandText = $SqlQuery # get query

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter #

$SqlAdapter.SelectCommand = $SqlCmd #

$SqlCmd.Connection = $connection

$DataSet = New-Object System.Data.DataSet

$SqlAdapter.Fill($DataSet)

$SqlAdapter.Dispose()

$connection.Close()

$DataSet.Tables[0]

######### Add to SPList ########

$spWeb = Get-SPWeb -identity "http://Site Name" # Get SPWeb

$list = $spWeb.Lists["Orders"] # Get SPList

foreach ($Row in $DataSet.Tables[0].Rows)
{

$item = $list.Items.Add();

$item["Title"] = $Row["orderId"]
$item["ProductId"] = $Row["ProductId"]
$item["UnitPrice"] = $Row["UnitPrice"]
$item["Quantity"] = $Row["Quantity"]
$item["Discount"] = $Row["Discount"]

$item.Update()

}

$spWeb.Dispose()

Comments or Responses

Login to post response