locked
#TYPE System.Data.DataRow Is 1st line of SSMS To CSV RRS feed

  • Question

  • almost a complete noob when it comes to powershell so please dumb down any response :).  This is the script I am using (created by bill fellows ... I can't find his blog post to link back to)

    $server = "localhost"
    $database = "master"
    $query = "SELECT D.* FROM sys.databases D"
    
    # powershell raw/verbatim strings are ugly
    # Update this with the actual path where you want data dumped
    $extractFile = @"
    C:\test.csv
    "@
    
    # If you have to use users and passwords, my condolences
    $connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
    $connectionString = [string]::Format($connectionTemplate, $server, $database)
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString
    
    $command = New-Object System.Data.SqlClient.SqlCommand
    $command.CommandText = $query
    $command.Connection = $connection
    
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $command
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $connection.Close()
    
    # dump the data to a csv
    # http://technet.microsoft.com/en-us/library/ee176825.aspx
    $DataSet.Tables[0] | Export-Csv $extractFile

    Tuesday, May 26, 2015 7:03 PM

Answers

  • Start by klearning how to use help.  It wil answer your questions.

    help help

    Read the details it will save you a lot of time.

    help export-csv -[full|Examples|Details|Parameter|etc]


    \_(ツ)_/

    • Marked as answer by IndigoMontoya Tuesday, May 26, 2015 7:27 PM
    Tuesday, May 26, 2015 7:22 PM
    Moderator

All replies

  • If you don't want the first line just add -NoTypeInformation to Export-Csv like this:

    Export-Csv $extractFile -NoTypeInformation

    • Proposed as answer by thandu123 Friday, January 3, 2020 2:06 AM
    • Unproposed as answer by jrvModerator Friday, January 3, 2020 3:02 AM
    Tuesday, May 26, 2015 7:22 PM
  • Start by klearning how to use help.  It wil answer your questions.

    help help

    Read the details it will save you a lot of time.

    help export-csv -[full|Examples|Details|Parameter|etc]


    \_(ツ)_/

    • Marked as answer by IndigoMontoya Tuesday, May 26, 2015 7:27 PM
    Tuesday, May 26, 2015 7:22 PM
    Moderator