CSV Output to Database Table - PowerShell

  • 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

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

  • 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)

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply