Here we go. I have this nice script that does exactly what its supposed to do except for one small detail; Formatting of the .csv file. If anyone can help, please do. The script is perfectly safe to run. It will output results to a .csv file from a SharePoint search. After which, it will upload those results to the site library of your choosing. here is the script:
param (
[string]$searchString = “$(Read-Host ‘Enter a search string:’)”,
[Int]$searchRowLimit = “$(Read-Host ‘Row Limit (0 for unlimited):’)”,
[string]$searchResults = “$(Read-Host ‘Path for results(CSV):’)”
)
#test search service name is Search Service Application 1…
$proxy = Get-SPEnterpriseSearchServiceApplicationProxy -Identity “Search Service Application 1”
#$proxy = Get-SPEnterpriseSearchServiceApplicationProxy -Identity “Search Service Application”
$query = New-Object Microsoft.office.Server.Search.Query.KeywordQuery $proxy
$query.ResultTypes = [Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults
$query.RowLimit = $searchRowLimit
$query.QueryText = $searchString
$resultTableColl = $query.Execute()
$resultTable = $resultTableColl.Item[Microsoft.Office.Server.Search.Query.ResultType]::RelevantResults)
$resDataTable = $resultTable.Table
$resDataTable.Rows | Select-object -Property Path,Title,@{Name=”Date”; Expression = {$_.Write}},@{Name=”Author”; Expression = {$_.Author}},@{Name=”Size”; Expression = { “{0:N0}KB” -f ($_.Size / 1Kb) }} | Export-Csv -Path $searchResults -Encoding unicode
#Upload the results to SharePoint List
$webUrl = Read-Host “Enter a site URL”
$listName = Read-Host “Enter a list name”
$File = Get-Item $searchResults
$Stream = $File.OpenRead()
$Web = Get-SPWeb $webURL
$List = $Web.Lists[“$listName”]
$FileCollection = $List.RootFolder.Files
$FileCollection.Add($File.Name,$Stream,$true)
$Stream.Close()
$File.Delete()
Please help.
I was able to overcome my dilemma by changing the script slightly.
$resDataTable.Rows | Select-object -Property Path,Title,@{Name=”Date”; Expression = {$_.Write}},@{Name=”Author”; Expression = {$_.Author}},@{Name=”Size”; Expression = { “{0:N0}KB” -f ($_.Size / 1Kb) }} | Export-Csv $searchResults -NoTypeInformation
Now when I open the .csv in Excel, I have my Path column, Name column and so on. Now it looks nice and neat with a little Excel work.
?width=721