locked
How Can I use Powershell to Export to CSV File From a List RRS feed

  • Question

  • Hello! I'm a newbie in Powershell and I'd appreciate your help.

    I wrote the code below, where it gets a list of servers and run the T-SQL command for each server regarding check if there are some DB's which are not in AO. I'd like the result was sent to a CSV file so I'd be able to export to a Table.

    "Try.. Catch..ea stop" was necessary to avoid process stop when running in a SQL Job whether some server is offline.

    I try to use export-csv at the end, but it results in a empty file.

    What am I missing or doing wrong?

    Thanks in advance!

    FOREACH($server in GC "D:\AO_Control\Servers_List.txt")
    {
    Try {
    $server  
    invoke-sqlcmd -ServerInstance $server -query "SELECT @@servername as 'Server', name as 'Databases', convert(varchar(8), getdate(), 1) as 'Date' from sys.databases WHERE database_id > 4 AND database_id NOT IN (select database_id from sys.dm_hadr_database_replica_states)" -querytimeout 65534 | ft -autosize | out-string -width 4096 -ea stop |export-csv -Path D:\AO_Control\so.csv -NoTypeInformation
     }
    Catch {}
    }

    Thursday, January 19, 2017 5:25 PM

All replies

  • Hello!

    If you omit the 'ft -autosize' and 'out-string' commands from your pipeline (that is, pipe your invoke-sqlcmd command straight into export-csv), do you get the results you're expecting? I believe that the result objects' properties which you want to write as CSV entries are lost since the objects have been converted into a string first.

    Edit: additionally, it looks like the 'so.csv' file will be overwritten on each iteration of the foreach loop.  Maybe you'd want to export them to "D:\AO_Control\$($server).csv" instead, to generate one csv file per server?

    Let me know if this helps, or if I've misunderstood any parts of the question.  Thanks!

    • Edited by John_Evs Thursday, January 19, 2017 11:53 PM
    Thursday, January 19, 2017 11:46 PM
  • ## server list from a string
    ###   $ServerList = "dev", "qa"  
    
    ## serverlist from a file
    $ServerList=Get-Content C:\temp\Serverlist.txt
    
    ##The combined file
    $finalfilepath = "C:\temp\filefinal.csv"
    ##clear the file content if it exists
    if (Test-Path $finalfilepath) {Clear-Content C:\temp\filefinal.csv }
    
    foreach ($CurrentServer in $ServerList) 
    {  
    
    ##Save one serverinstance as a file
      $csvFilePath ="C:\temp\($CurrentServer)list.csv"
       
      Invoke-Sqlcmd -Server $CurrentServer -Database master  -Query "SELECT @@servername as 'Server', name as 'DB', convert(varchar(8), getdate(), 1) as 'Date' from sys.databases WHERE database_id > 4 AND database_id NOT IN (select database_id from sys.dm_hadr_database_replica_states)"  -querytimeout 65534  | export-csv $csvFilePath   -NoTypeInformation
    	Get-Content  $csvFilePath >>  $finalfilepath
    }
    
    ##Open in notepad
    notepad C:\temp\filefinal.csv
    

    Friday, January 20, 2017 8:46 PM