Share via

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,462 questions
Accepted answer
  1. Ian Xue (Shanghai Wicresoft Co., Ltd.) 34,271 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
Most helpful Newest Oldest
  1. Rich Matheisen 45,906 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