Friday 30 September 2011

Powershell: Out-File truncate text

I was looking to use Powershell to write out some data from a SQL table using a combination of Invoke-Sqlcmd with out-file. Everything was going nicely until my output file suddenly started having data truncated. Something like:

Invoke-Sqlcmd -Query "SELECT LongText FROM MyTable" -ServerInstance $sql | out-file -filepath $filepath

Produced a file containing:

LongText
----------------
This was my really really long and boring piece of text that I had writ...


The thing is, it happened quite randomly. Or so I thought. Turns out that the out-file cmdlet outputs exactly as if it was appearing within the console on that machine and my console was too narrow for the text. (This is all in the documentation to be fair). So manipulating the size of your console window (by editing the properties) results in different output lengths.


However, each powrshell install on a machine can be different so I really need my script to be able to handle this limitation. As of writing, I couldn't find anything fullproof and my best hack was to just explicity set a width that I "knew" was big enough to cater for my text.

Invoke-Sqlcmd -Query "SELECT LongText FROM MyTable" -ServerInstance $sql | out-file -filepath $filepath -width 2000

4 comments:

  1. AWESOME - Thanks so so much. This has really helped me tons.

    ReplyDelete
  2. Worked for me too. I didn't think about the out-file width side. I was trying to add column width on the invoke-sqlcmd side.

    ReplyDelete

/* add this crazy stuff in so i can use syntax highlighter