locked
Character Limit of Export-csv RRS feed

  • General discussion

  • I've notice that when piping results to Export-csv there is a 255 character limit on the output.  How do I go around this?  

    I'm doing an SQL query via powershell and exporting the results to CSV via Export-csv cmdlet.  I notice that when I do that the results on some cells are truncated at around the 255 character limit.  I tried using Out-File and it overcomes this, but the output results is not in an easily usable format that I can import into Excel as compared to using the export-csv cmdlet.

    Does anyone know a work around to this limitation of export-csv?


    • Changed type Bill_Stewart Monday, October 31, 2016 6:04 PM
    • Moved by Bill_Stewart Monday, October 31, 2016 6:05 PM This is not SQL Server support forum
    Wednesday, September 21, 2016 10:38 PM

All replies

  • The width of the field depends on the type returned from SQLServer.

    CSV files are not useful for backups.  Use BCP and a native format to create text backups of SQLServer tables.

    Excel field limits are set at 32,767 characters.


    \_(ツ)_/

    Wednesday, September 21, 2016 10:50 PM
  • Here is an example:

    PS >$x = @([pscustomobject]@{ Name = 'Test'; Data = '' })
    PS >$x[0].Data = '*' * 1024
    PS >$x | Export-csv testlong.csv -NoTypeInformation
    PS >$y = Import-Csv testlong.csv
    PS >$y.Data.Length
    1024
    
    


    \_(ツ)_/

    Wednesday, September 21, 2016 10:54 PM
  • I've notice that when piping results to Export-csv there is a 255 character limit on the output.

    Please post a short sample script that contains only the minimum amount of code needed to reproduce this condition. I just tested Export-Csv with a string longer than 255 characters and it was not truncated.


    -- Bill Stewart [Bill_Stewart]

    Thursday, September 22, 2016 2:14 PM
  • I've been using Invoke-sqlcmd2 (https://github.com/RamblingCookieMonster/PowerShell/blob/master/Invoke-Sqlcmd2.ps1) to query a DB; however when I pipe to export-csv it seems to cut some fields short.  When I just pipe to Out-file the output contains the full values, that's why I was thinking export-csv was truncating.. 
    Thursday, September 22, 2016 3:03 PM
  • That script does not correctly query the database.  Post you issues to the author of the script and maybe it will get fixed.

    I am pretty sure the cause will be: The default length for a "char" field in SqlServer is 255. To get the actual length it is necessary to query the schema for the designed size.


    \_(ツ)_/

    Thursday, September 22, 2016 4:30 PM
  • Thanks.  I'll try that.
    Thursday, September 22, 2016 4:42 PM