PowerShell and sql query

rabinda77 66 Reputation points
2021-02-03T19:03:31.013+00:00

Hi,

I have .txt file with a list of server names.
I am running a sql query and putting the results of the query in to a csv file.
After iterating through each of the servers combining all the csv files to one file.63529-sqllogin-expiration-report2.txt

However the results of the final CSV file does not come as proper columns.

Any insights would be helpful.

I have attached the final csv file.

Thank you

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,404 questions
{count} votes

Accepted answer
  1. Ian Xue (Shanghai Wicresoft Co., Ltd.) 31,091 Reputation points Microsoft Vendor
    2021-02-04T08:35:56.793+00:00

    Hi,

    Your attachment is not a csv file. If you want to combine csv files you can try this

    Get-Item -Path $curlocation\*.out | ForEach-Object{  
        import-csv $_} | Export-Csv -Path $curlocation\$RunOutFile -NoTypeInformation  
    

    Best Regards,
    Ian Xue

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

    If the Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


1 additional answer

Sort by: Most helpful
  1. Rich Matheisen 45,111 Reputation points
    2021-02-04T15:52:48.323+00:00

    If you remove the "-Verbose" from the Invoke-SQLCmd you can export the results of running the query on all server instances. See if this works:

    $Serverlist = Servers.txt
    $RunOutFile = "LoginExpirationReport.csv"
    
    Get-Content $Serverlist |
        ForEach-Object {
            $Params = [PSCustomObject]@{
                Query = "sqlquery1"
                ServerInstance = $_
                Database = "master"
                Username = "test332"
                Password = "ppp"
                Verbose = $true     # the use of "Verbose" will screw up the Export-CSV if directed to stream #1 !
            }
            Invoke-Sqlcmd @Params 4>&1
        } | Export-CSV -Path "$curlocation\$RunOutFile" -NoTypeInformation -Encoding UTF8
    
    0 comments No comments