Use Powershell to create a csv file with output from SQL query

We needed to transfer some data from Microsoft Dynamics AX to a old system running on DB2, to keep the data updated. 

This script runs daily and checks for changes made yesterday and creates a .csv file with the result, which can be imported/converted to most system.  See comments for explanation: 

This script needs to be run on a server with SQL installed, as it uses the SQL server snapin.
 
set-executionpolicy unrestricted

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

#Your SQL query
$SQLquery =@"

SELECT CUSTACCOUNT, FIRSTNAME + ' ' + MIDDLENAME as FIRSTMIDDLE,LASTNAME,STREET,ZIPCODE,CITY,PHONE,CELLULARPHONE,EMAIL,BIRTHDATE,CPR FROM [Your server].[dbo].[table]
where DATAAREAID = 'ID' AND
convert(varchar(10), CREATEDDATETIME, 102) = dateadd(day,datediff(day,0,getdate()),-1)


"@
#Run the query and export the result
$result = invoke-sqlcmd -query $SQLquery -serverinstance Server -database database
$result |export-csv c:\file.csv -notypeinformation -Encoding UTF8 -Delimiter ";"

#Give the file 5 sec to be created
Start-Sleep -s 5

#Remove all " from file - PowerShell adds them when using export-csv
(Get-Content c:\file.csv) | % {$_ -replace '"', ""} | out-file -FilePath c:\file.csv -Force

#Give the file 2 sec to be created
Start-Sleep -s 2

#Load the file and remove the headers - first line in csv
$csv = Get-Content c:\file.csv
$csv = $csv[1..($csv.count - 1)]
$csv > c:\file.csv

Daily health report by mail - SharePoint 2010

Found a script that sends a email with the SharePoint Health report. Its useful when you want the information delivered to your inbox instead of going to each SharePoint environment and checking the health report there. Put the script below, into a scheduled task and run it daily:

if ($PSVersionTable) {$Host.Runspace.ThreadOptions = 'ReuseThread'}

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

# get the health reports list

$ReportsList = [Microsoft.SharePoint.Administration.Health.SPHealthReportsList]::Local

$FormUrl = '{0}{1}?id=' -f $ReportsList.ParentWeb.Url, $ReportsList.Forms.List.DefaultDisplayFormUrl

$body = $ReportsList.Items | Where-Object {$_['Severity'] -ne '4 - Success'} | ForEach-Object {

    New-Object PSObject -Property @{

        Url = "$($_['Title'])"

        Severity = $_['Severity']

        Category = $_['Category']

        Explanation = $_['Explanation']

        Modified = $_['Modified']

        FailingServers = $_['Failing Servers']

        FailingServices = $_['Failing Services']

        Remedy = $_['Remedy']

    }

} | ConvertTo-Html | Out-String

# creating clickable HTML links

$body = $body -replace '<','<' -replace '>','>' -replace '"','"'

$params = @{

   To = 'you@domain.com','manager@domain.com'

   From = 'SPHealth@domain.com'

   Subject = 'Daily Health Analyzer report'

   SmtpServer = 'smtp1'

   Body = $body

   BodyAsHtml = $true

}

Send-MailMessage @params

Source: Shay Levy

Collection of SharePoint webparts - Sites and places

 ‎http://www.dundas.com - Primarily chart and KPI webparts  

http://www.amrein.com/sharepoint - A collection of various useful webpart, Image Rotator etc..

http://www.boostsolutions.com - Administration, BI, Collaboration, Content Management and Security

http://www.bamboosolutions.com - Many useful webparts and solutions available to 2007, 2010 and 2013

http://www.virtosoftware.com - Administration, BI, Collaboration, Content Management, Workflow, Bulk Operations and Project Management

http://www.pentalogic.net - A small collection of webparts you might find useful. Team Time - if you looking for a Punch Card/TimeSheets solution

http://www.kwizcom.com  - Many useful webparts and solutions available to 2007, 2010 and 2013

http://www.lightningtools.com

http://www.layer2.de

http://www.codeplex.com - Opensouce projects/webparts, with very different functionality, workflow add-ons, powershell scipts ect...

http://www.harepoint.com 

SharePoint, Show 2 or more webparts side by side

It can be very useful to be able to place 2 webparts side by side in SharePoint, especially if you don't have the option to add a custom page template or don't know how to. Also in SharePoint online, you possibilities can be limited, and this might be a good solution. Just add a table to you page and place your webparts inside the table columns:

Taken from the source link below:

How do we do it?

• enter in Edit Mode;
• choose a location for the table;
• insert a table with n columns and n rows (depends on the number of web parts);

If you already have some web parts in the page just drag & drop them in the table.
If not, click in a column and then insert the desired web part.
  

Source: Alexandru Dionisie

Count all documents in SharePoint 2010 with powershell

Runs trough sharepoint and counts all items in "Documents" library.

cls

$total = 0
$totalembers = 0;

foreach($site in Get-SPSite -Limit all)
{
    foreach($web in $site.AllWebs)
    {
        $totalembers = $totalembers + 1
       
        $list = $web.Lists["Documents"];
       
        $listCount = $list.ItemCount
        #$listCount = $list.Item.Count #more accurate but slow
       
        $total = $total + $list.ItemCount
        echo $web
        echo $total
    }
}

$logMsg = "Total Members " + $totalembers + "; Total Documents " + $total + ";"
write $logMsg

Upload file to multiple document libraries in SharePoint 2010 with PowerShell

We needed to publish a file to all document libraries, with the same name, in a site collection. To achieve that, the following powershell script was made, by bits and pieces from different blogs. The script runs trough the site collection and uploads the file to the "Documents" folder.

clear
$DocLibName = "Documents"
$FilePath = "d:\file.pdf"


################## NUMBER OF MEMBERSITES COUNT ##################
$webapplication = Get-SPWebApplication "Web"
$icount = 0

foreach($sitecount in $webapplication.Sites){
       $icount++
}
$icount = ($icount-81)
echo $icount
################## NUMBER OF MEMBERSITES COUNT END ###############

################## Upload to ALL MEMBERSITES ##################

$i = 0
$numberarray = 0..$icount

echo ($nowStart = Get-Date)

    foreach($i in $numberarray) {
#We have different site collections, so we need to run trough them all
    $siteurl = "URL/"+$i+"/"
    $site = Get-SPSite($siteurl)

            foreach($web in $site.AllWebs) {
            #Get the Web & Lists to upload the file
            $List = $Web.GetFolder($DocLibName)
            
            #Get the Files collection
            $Files = $List.Files
            
            #Get File Name from Path
            $FileName = $FilePath.Substring($FilePath.LastIndexOf("\")+1)
            
            #Get the File
            $File= Get-ChildItem $FilePath
            
            #Add File to the collection
            $Files.Add($DocLibName +"/" + $FileName,$File.OpenRead(),$false)
            echo "Uploader til "$web.Url
            #Dispose the objects
            $web.Dispose()
            }
    $i++
    }
echo "Start:"$nowStart
echo "Slut:"($nowEnd = Get-Date)
################## Upload to MEMBERSITES END #################
  
It should also work with multiple files in the upload folder.

Update listview on multiple list in SharePoint 2010

Our member system is a SharePoint 2010, where each member has its own site. Each site has a document library to handle the member documents. The default view needed some extra columns and information. Here is the powershell script to create a new view and make it default:

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
$webapplication = Get-SPWebApplication "Web"
foreach($site in $webapplication.Sites){
    foreach($web in $site.AllWebs){
     
$listCounter = $web.Lists.Count
for($i=0;$i -le $listCounter;$i++) {

    $list = $web.Lists[$i]
    if($list.BaseType -eq "DocumentLibrary") {
    $newList = $web.Lists.item($list.ID);


  $viewfields = New-Object System.Collections.Specialized.StringCollection
  $viewfields.Add("Type")
  $viewfields.Add("Navn")
  $viewfields.Add("Emne")
  $viewfields.Add("Medlemsid")
  $viewfields.Add("Oprettet")
  $viewfields.Add("Sagsid")
  

  [void]$newList.Views.Add("Filer", $viewfields, "", 100, $true, $true)
  $newList.Update();

  $view=$newList.Views["Filer"]
  $view.DefaultView = $true
  $view.Update()
}
}

$web.Dispose();
}
}
$site.Dispose(); 

Scrolling error in Sharepoint 2010 with Google Chrome

We have a public facing SharePoint site, where members can access various information. Some members was missing the option to scroll, when visiting the site. The problem was with our masterpage, as we had disabled the ribbon and the site was "anonymous". The error occurred when Sharepoint loaded the ribbon and other scipts on initialisation. The following javascipt, found some where on the internet, takes care of that:
 
if (jQuery.browser.webkit) {
    jQuery(document).ready(function () {
        var interval;
        function loopCheck() {
            if (typeof (_spBodyOnLoadWrapper) !== "undefined" && _spBodyOnLoadCalled == false)
                _spBodyOnLoadWrapper();
             else
                window.clearInterval(interval);
        }
        // Give SP a chance..
        setTimeout(function () { interval = window.setInterval(loopCheck, 30); }, 120);
    });
}

Export/Copy list to another Farm/Site collection/Site with Powershell

We needed to publish 3 daily teasers from a member restricted area to a public site. The following powershell script exports a sharepoint list to a csv file:

cls
if((Get-PSSnapin | Where {$_.Name -eq "Microsoft.SharePoint.PowerShell"}) -eq $null) {
    Add-PSSnapin Microsoft.SharePoint.PowerShell;
}

$MyWeb = Get-SPWeb "Site"
$MyWeb.Lists["Name"].Items |
     ForEach-Object{
          New-Object PSObject -Property @{
                                          Emne=$_["Emne"]
                                          Kategori=$_["Kategori"]
                                          Tekst=$_["Tekst"]
                                         }
     } | Export-Csv -path 'c:\file.csv' -NoTypeInformation -Encoding unicode
   
     echo "Exporting done" 

Now we have the exported items in a file, which we can import in to any other sharepoint location, where we have a list. The import list need to have the same columns as the exported list. Here is the import script:

clear
# Import the .csv file, and specify manually the headers, without column name in the file
$contents = Import-CSV ‘c:\file.csv' -header("Kategori", "Tekst","Emne")
# Web URL
$webURL = “Site”
$web = Get-SPWeb -Identity $webURL
$listName = "Name"

############ Delete all list items ################

    $itemArray = $web.Lists["$listName"].Items | % { $_ }

    $itemArray | % { $_.Delete() }

###################################################

Echo $listName
$list= $web.Lists["$listName"]
$i = 1
# Iterate for each list column
foreach ($row in $contents )
{
    $item = $list.Items.Add();
    $item["Emne"] = $row.Emne
    $item["Kategori"] = $row.Kategori
    $item["Tekst"] = $row.Tekst
    $item["IdSort"] = $i++
    $item["URL"] = "Link"
    $item.Update()
}
Write-Host -ForegroundColor green "List Updated Successfully"
$web.Dispose()

IdSort and URL are added during the import, as we needed to control sorting and, where the items links.

Wrapped in a scheduled job, we now have "live update" from a member restricted area. This could also be done with a timer job or a custom webpart, but this powershell option is quick and cost effective.