none
Select-Object -ExpandProperty Adds Blank Record to File

    Question

  • I am attempting to get a list of valid servers to feed into other PowerShell scripts.  Everything is great except I get a blank line at the end of the list in the output file.  That will cause an error on my down stream scripts.

    Here is the statement that is causing me my problem.

    Invoke-Sqlcmd -ServerInstance "MyServer" -database "MyDatabase" -Query "SELECT [ValidServerName] FROM dbo.ValidSQLServers WHERE ProdEnvironment = 'P' ORDER BY [ValidServerName]" | Select-Object -ExpandProperty ValidServerName > F:\Level1\Level2\AllProd.txt

    As you can see, I am pulling in data from a SQL Server database.  It is a single column of data and I pipe the output to SELECT-OBJECT with -ExpandProperty flag on the cmdlet.  When I open up the text file in Notepad, the blank line is at the end.

    I have investigated and tried just about everything.  I have seen answers to place the data in a CSV file.  I don't want to do that.  I just want my list as it is without the #$%&*! blank line inserted at the end.

    Any suggestions?

    Thanks, Jim  

    = = = = = = = = = = = = = = = 

    I wanted to give an update.  I tried to filter with the WHERE-OBJECT and it did not work either:

    Invoke-Sqlcmd -ServerInstance "MyServer" -database "MyDatabase" -Query "SELECT [ValidServerName] FROM dbo.ValidSQLServers WHERE ProdEnvironment = 'P' ORDER BY [ValidServerName]" | Select-Object -ExpandProperty ValidServerName | Where-Object -FilterScript {$_ -NE $null}> F:\Level1\Level2\AllProd.txt

    Jim


    Jim Drewe


    Wednesday, July 11, 2018 9:44 PM

Answers

  • JRV, thank you -- first for the speed reply and second for the insight.  It concerned me because whenever I manually created lists like this, if I had a blank line at the end, it caused problems.

    I am just curious.  Why the change to the output using Out-File?


    Jim Drewe

    • Marked as answer by JimAzSQL2 Wednesday, July 11, 2018 10:29 PM
    Wednesday, July 11, 2018 10:29 PM

All replies

  • There would be no blank line at the end of the file.  A file ends with a line break making it look like an extra line.

    I would not use "> F:\Level1\Level2\AllProd.txt"

    I would use "Out-File F:\Level1\Level2\AllProd.txt"


    \_(ツ)_/

    Wednesday, July 11, 2018 10:26 PM
    Moderator
  • JRV, thank you -- first for the speed reply and second for the insight.  It concerned me because whenever I manually created lists like this, if I had a blank line at the end, it caused problems.

    I am just curious.  Why the change to the output using Out-File?


    Jim Drewe

    • Marked as answer by JimAzSQL2 Wednesday, July 11, 2018 10:29 PM
    Wednesday, July 11, 2018 10:29 PM
  • This produces exactly one line in the file:

    invoke-sqlcmd -Query 'select name from sysobjects where name = ''plan_persist_query''' | select -expand name | out-file testsql.txt
    $name = cat  testsql.txt
    $name
    
    


    \_(ツ)_/

    Wednesday, July 11, 2018 10:33 PM
    Moderator
  • I am just curious.  Why the change to the output using Out-File?


    Jim Drewe

    More explicit and more manageable.


    \_(ツ)_/

    Wednesday, July 11, 2018 10:34 PM
    Moderator
  • Thank you again.  I REALLY appreciate it.  I ran the script and it works great down stream (and I made the change to Out-File the output.

    I marked the question as answered and voted you up.

    Have a great day.

    Jim


    Jim Drewe

    Wednesday, July 11, 2018 11:28 PM
  • Thank you - glad it works as needed.


    \_(ツ)_/

    Wednesday, July 11, 2018 11:36 PM
    Moderator
  • Hi,

     

    I am glad to hear that your issue was successfully resolved.

    If there is anything else we can do for you, please feel free to post in the forum.

     

    Best Regards,

    Kallen


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, July 12, 2018 2:02 AM
    Moderator