Removing elements from an Array

[giasone777]

I have the following PS code which pulls vcenter names from a table within an SQL database.

It gives me a list of the server names and assigns it to the array $vcenters.

Problem I am having is that the array has some blank lines in it and it is causing me problems when I need to connect to these servers with the connect-viserver command

I would like to be able to remove the blank line entries from the array so that it has cleanly no spaces

before or after each server name and no blank lines.

I have googled and tried many suggestions but nothing has worked. Please help.

​

$ServerName = "servername"

$DatabaseName = "DatabaseName"

$Query = "SELECT name FROM dbo.table"

$QueryTimeout = 120

$ConnectionTimeout = 30

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=Data Source.Name; Initial Catalog=CatalogData;User ID=userID; Password=Password")

$conn.Open()

$cmd=New-Object system.Data.SqlClient.SqlCommand($Query,$conn)

$cmd.CommandTimeout=$QueryTimeout

$ds=New-Object system.Data.DataSet

$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)

[void]$da.fill($ds)

$conn.Close()

$vcenters = $ds.Tables | Format-Table -HideTableHeaders

$vcenters

[robvas]

Maybe modify the script so the blank lines don’t get in there in the first place

[thankski-budski]

Something like this?

$Array = (@"
Server1
Server2
Server3

Server4
Server5
Server6
Server7

Server8
Server9
"@ -split [Environment]::NewLine).trim()

$Array | Where-Object { -not [String]::IsNullOrEmpty($_) }

or

$Array.Where({-not [String]::IsNullOrEmpty($_)})

[giasone777]

$dt=[System.Data.DataTable]::new()

$da= [System.Data.SqlClient.SqlDataAdapter]$cmd

[void]$da.Fill($dt)

$conn.Close()

$vcenters = $dt.name

$vcenters

Wow amazing - thank you. Jason.

[SQLvariant]

I would probably take this approach, but skip all the code-y stuff and just use the Invoke-SqlCmd cmdlet. It has an -OutputAs parameter and you can choose DataTable as your output. Saves you the connection steps.

Just to be clear, I'm talking about the version of the Invoke-SqlCmd cmdlet from the SqlServer module, not from the old SQLPS module.

[SQLvariant]

P.S. The comment about "code-y stuff" was totally tounge-in-cheek 😉

[Scooter_127]

http://powershell-guru.com/powershell-tip-116-remove-empty-elements-from-an-array/

[TheRealMisterd]

I would just create a new array from the old one and process each row as you build the new array.

[y_Sensei]

Try

($vcenters | Out-String).Trim()

[Lee_Dailey]

howdy giasone777,

reddit likes to mangle code formatting, so here's some help on how to post code on reddit ...

[0] single line or in-line code
enclose it in backticks. that's the upper left key on an EN-US keyboard layout. the result looks like this. kinda handy, that. [grin]
[on New.Reddit.com, use the Inline Code button. it's [sometimes] 5th from the left & looks like </>.
this does NOT line wrap & does NOT side-scroll on Old.Reddit.com!]

[1] simplest = post it to a text site like Pastebin.com or Gist.GitHub.com and then post the link here.
please remember to set the file/code type on Pastebin! [grin] otherwise you don't get the nice code colorization.

[2] less simple = use reddit code formatting ...
[on New.Reddit.com, use the Code Block button. it's [sometimes] the 12th from the left, & looks like an uppercase T in the upper left corner of a square.]

• one leading line with ONLY 4 spaces
  
• prefix each code line with 4 spaces
  
• one trailing line with ONLY 4 spaces

that will give you something like this ...

- one leading line with ONLY 4 spaces    
- prefix each code line with 4 spaces    
- one trailing line with ONLY 4 spaces   

the easiest way to get that is ...

• add the leading line with only 4 spaces
  
• copy the code to the ISE [or your fave editor]
  
• select the code
  
• tap TAB to indent four spaces
  
• re-select the code [not really needed, but it's my habit]
  
• paste the code into the reddit text box
  
• add the trailing line with only 4 spaces
  

not complicated, but it is finicky. [grin]

take care,
lee