CSV Output to Database Table - PowerShell

• gbargsley

  SSCommitted

  Points: 1514

  More actions

  April 5, 2011 at 12:47 pm

  #236346

  I am currently running a Powershell script on eight different servers getting file counts and sizes of a couple of directories. Right now I am saving the output to a log file (CSV) and the results from each site to a summary log file. I would like to change this to store results to a central database table. I am having a tough time finding the code samples I need to connect to a table and store the information. If anyone would provide guidance or assistance that would be great. I have copied my script below.

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

  #WordRad Check

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

  $StartFolder = "\\server1\d$\Results"

  $LogFileDate = (Get-Date).tostring("MMddyyyy")

  $DetailLogFile = "\\MainServer\C$\PowerShell\Logs\Images\BYDB_" + $LogFileDate + "_FolderCountSizeLog_Detail.txt"

  $SummaryLogFile = "\\MainServer\C$\PowerShell\Logs\Images\" + $LogFileDate + "_FolderCountSizeLog_Summary.txt"

  $GrandTotalCount = 0

  $GrandTotalSize = 0

  #cls

  $colItems = (Get-ChildItem $startFolder | Where-Object {$_.PSIsContainer -eq $True} | Sort-Object -ErrorAction SilentlyContinue)

  foreach ($i in $colItems)

  {

  $subFolderItems = (Get-ChildItem $i.FullName | Measure-Object -property length -sum -ErrorAction SilentlyContinue)

  $count = @((Get-ChildItem $i.FullName -force | where {$_.length -ge 0} )).Count

  $totalsize = ($subFolderItems.sum)

  $output_final = $i.FullName + ",",$totalsize + " MB,",$count

  $output_final | Out-File -Append $DetailLogFile -Encoding ASCII -ErrorAction SilentlyContinue

  $GrandTotalSize += $TotalSize

  $GrandTotalCount += $Count

  }

  $output_final = $startFolder + " -- Total Size: " + [math]::round($GrandTotalSize / 1GB, 2) + " GB " + "Total File Count: " + $GrandTotalCount

  $output_final | Out-File -Append $SummaryLogFile -Encoding ASCII -ErrorAction SilentlyContinue

• schleep

  SSChampion

  Points: 12586

  More actions

  May 11, 2011 at 1:14 pm

  #1323330

  This may help you: http://poshcode.org/2276

• murali.jillellamudi 85871

  Old Hand

  Points: 304

  More actions

  July 22, 2011 at 9:02 am

  #1357107

  Hi

  I tried the following and seems to be working. You can tweak the code for your requirement , need to change the servername , databasename, tablename. I picked this code from "Loading Data With Powershell By Chad Miller, 2011/05/13"

  from SQL SERVER CENTRAL.com

  Thanks Chad....!!!!

  ********************************************************************

  Function out-DataTable {

  $dt = new-object Data.datatable

  $First = $true

  foreach ($item in $input){

  $DR = $DT.NewRow()

  $Item.PsObject.get_properties() | foreach {

  if ($first) {

  $Col = new-object Data.DataColumn

  $Col.ColumnName = $_.Name.ToString()

  $DT.Columns.Add($Col) }

  if ($_.value -eq $null) {

  $DR.Item($_.Name) = "[empty]"

  }

  elseif ($_.IsArray) {

  $DR.Item($_.Name) =[string]::Join($_.value ,";")

  }

  else {

  $DR.Item($_.Name) = $_.value

  }

  }

  $DT.Rows.Add($DR)

  $First = $false

  }

  return @(,($dt))

  }

  $dataTable = ls c:\temp -r *.bak | Sort -desc lastwriteTime | SELECT Directory,Name,LastWriteTime | out-DataTable

  $connectionString = "Data Source=MyDBServerName;Integrated Security=true;Initial Catalog=MyDatabase;"

  $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString

  $bulkCopy.DestinationTableName = "MyTable"

  $bulkCopy.WriteToServer($dataTable)

  *****************************************************************************************

• murali.jillellamudi 85871

  Old Hand

  Points: 304

  More actions

  July 22, 2011 at 9:03 am

  #1357108

  http://www.sqlservercentral.com/articles/powershell/65196/