Powershell script for tab delimited for SSIS upload

Question

question

Noonies asked Mar 5, '13

Powershell script for tab delimited for SSIS upload

Hi Everyone! I have become challenged with a TXT file that a server admin would like for me to upload which he is using a powershell script to output to the TXT file. The format is using a custom format and not using tab delimited so this has been quite difficult for me to work with. In reviewing the Powershell script I found he is using the following:


    # Create a custom object to hold the results
 $results = "" | Select-Object `
 Name, `
 NumOfDays, `
 NumOfCrashes, `
 NumOfReboots, `
 MinutesDown, `
 MinutesUp, `
 PercentDowntime, `
 PercentUptime
 $results.Name = $ComputerName
 $results.NumOfDays = $NumberOfDays
 $results.NumOfCrashes = $crashCounter
 $results.NumOfReboots = $rebootCounter
 $results.MinutesDown = "{0:n2}" -f $downtime.TotalMinutes
 $results.MinutesUp = "{0:n2}" -f $uptime.TotalMinutes
 $results.PercentDowntime = "{0:p4}" -f (1 - $uptime.TotalMinutes/$minutesInPeriod)
 $results.PercentUptime = "{0:p4}" -f ($uptime.TotalMinutes/$minutesInPeriod)
 
 Write-Output $results
 
 # Kill our session to the remote computer
 Remove-PSSession -Session $mySession

I believe this is causing the columns to look like the below: (without the spaces between each record - couldnt get this to format right for me in this)


Name            : ServerName
NumOfDays       : 8
NumOfCrashes    : 0
NumOfReboots    : 2
MinutesDown     : 1.68
MinutesUp       : 11,518.32
PercentDowntime : 0.0146 %
PercentUptime   : 99.9854 %

Is there a way I can modify to be tab delimited on output? If I'm in the wrong place do you know of a good Powershell forum I could reach out to? Or do you know of a work around I could do with the current format so that I can build a package around it for data automation upload?

ssis powershell

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey ♦♦ commented · Mar 23, 2013 at 10:19 AM

If any of the answers below were helpful to you, please show that by clicking on the thumbs up next to the answer. If any of the answers below solved your problem, please indicate that by clicking on the check box.

1 ·

Noonies commented · Mar 05, 2013 at 10:31 PM

My apology for the formating above. I couldn't get the code of the customer object to format for me along with the return data set in the text file. :-|

0 ·

Answer 1 · 2013-03-06T02:50:33Z

Shawn_Melton answered Mar 6, '13

Not sure how experienced of a PowerShell your admin is but if a better way of writing that bit of code:


$results = Select @{Label="Name";Expression={$ComputerName}},
@{Label="NumOfDays";Expression={$NumberOfDays}},
@{Label="NumOfCrashes";Expression={$crashCounter}},
@{Label="NumOfReboots";Expression={$rebootCounter}},
@{Label="MinutesDown";Expression={"{0:n2}" -f $downtime.TotalMinutes}},
@{Label="MinutesUp";Expression={"{0:n2}" -f $uptime.TotalMinutes}},
@{Label="PercentDowntime";Expression={"{0:p4}" -f (1 - $uptime.TotalMinutes/$minutesInPeriod)}},
@{Label="PercentUptime";Expression={"{0:p4}" -f ($uptime.TotalMinutes/$minutesInPeriod)}}

$results | Export-Csv C:\temp\Results.csv -NoTypeInformation

I used the Export-Csv cmdlet with this command as an example Get-Process | Select Name, ID, ProcessName | Export-Csv C:\temp\myfile.csv -NoTypeInformation and got this within my csv file:


"Name","Id","ProcessName"
"armsvc","1720","armsvc"
"atieclxx","1288","atieclxx"
"atiesrxx","1056","atiesrxx"

I am not to sure how SSIS will handle the quotes everywhere. If it does not like it you can use PowerShell to [remove all the quotes][1] from the file. [1]: http://blogs.technet.com/b/heyscriptingguy/archive/2011/11/02/remove-unwanted-quotation-marks-from-csv-files-by-using-powershell.aspx

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Noonies commented · Mar 06, 2013 at 05:25 PM

@Shawn - Thanks. I have modified the script to see if this will give us what we need. :) If not, I will look at the added link to remove quotes. I will follow up shortly! Our admin pulled the script from the web and he and I are not all that knowledgeable in Powershell scripting. But I definitely would love to learn more on this. Thanks again for reading and taking the time to answer my question.

0 ·

Noonies commented · Mar 06, 2013 at 10:18 PM

@Shawn - I added your above code to the Powershell script and I was wondering whether or not I should have commented out the Write-Output $results. I'm getting an error and did comment out the Write-Output $results. The error reads: WARNING: This could take several minutes! Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null. At C:\windows\system32\WindowsPowerShell\v1.0\getServerUptimeBL2.ps1:283 char:26 + $results | Export-Csv <<<

0 ·

Shawn_Melton commented · Mar 06, 2013 at 11:01 PM

This error is in reference to the $uptime variable. You may need to see how it is populating that variable. The specific error I believe would mean it cannot evaluate the expression for that column.

0 ·

Shawn_Melton commented · Mar 06, 2013 at 11:04 PM

What is the end result your admin is trying to achieve with the script he found? There is usually more than one way to obtain info with PS.

0 ·

Noonies commented · Mar 06, 2013 at 11:12 PM

He wants to export a file which will pull X amount of days and total server uptime and downtime so he can keep a log to present to our boss who in return will be showing this to the COO. They were going to keep this in an Excel spreadsheet, but I had thought I could upload this and timestamp to a SQL table and build reporting using SSRS. This could eliminate them having to recompile weekly data into an Excel spreadsheet. But when I reviewed the format I couldnt do the upload using SSIS for data automation into the SQL table.

0 ·

question