none
Export data from SQL Server to text file RRS feed

  • Question

  • I have this code below that ouputs a query to a text file. THis may seem simple but how do i get it delimited with column headings and size the table so all column fit. I thought autosize would accomplish this and i could not find any good material to add a delimiter to the table

    param ( [string]$SQLServer, [string]$Database, [string]$filename, )

    $Query = "Very Long QUery Goes Here with CASE WHEN and udfs" $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=$SQLSERVER;Database=$DATABASE;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $Query $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $SqlConnection.Close() $DataSet.Tables[0] | format-table -auto > $filename

    Thursday, May 24, 2012 2:00 PM

Answers

All replies

  • Hi,

    Try

    $DataSet.Tables[0] | export-csv -NoTypeInformation $filename

    J

    • Marked as answer by Jay_Michael Thursday, May 24, 2012 3:01 PM
    Thursday, May 24, 2012 2:06 PM
  • Can't beleive I missed that little piece of code in my research! Thank you. Does this accept other delimiters, such as tab?

    Thursday, May 24, 2012 2:16 PM
  • Yeap ... have a look at export-csv -Delimiter

    I think somethink like this would work ..

    $DataSet.Tables[0] | export-csv -Delimiter "`t" -NoTypeInformation $filename

    J
    • Proposed as answer by John Grenfell Thursday, May 24, 2012 2:20 PM
    Thursday, May 24, 2012 2:20 PM
  • Do i need powershell v2 for the delimiter parameter?
    Thursday, May 24, 2012 2:34 PM
  • I've only ever run v2 ....

    but you're right v1 doesn't support delimiter :o(

    Thursday, May 24, 2012 2:42 PM
  • I believe I should not run into any comma errors (they should not put commans in a field). I may upgrade to 2.0 if my machine lets me, i think i originally downloaded 1.0 with SQL server 2008 R2

    Thursday, May 24, 2012 3:01 PM