Search code examples
sql-serversqlcmd

How to ignore the new line in table value?

I am trying to export a SQL Server table to CSV file. What I am doing is call the sqlcmd command

C:\>sqlcmd -S EC2AMAZ-5UNBD90 -Q "SELECT * FROM dbo.PMDOCS  WHERE hmy=95613900;" -s "," -o "C:\pmdocs_ms_v1.csv" -E -y0

A particular column in this table could contains 'new line' character, such as this record, what they write to the CSV file is like:

95613900,25,1731323,2020-06-06 11:59:04.000,\\asp1\kfs\kusersdefpaths\Pangea Properties\Live\Attachments\WorkOrder\06-2020\5883_1179695_0.jpg,Tier 547 door is broken
Tier 543 does not lock latch broken 5883_1179695_0.jpg,0,0,0,6889803,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0

A single record was stored in two lines. Please advise what should I do to make them in one line. Thanks in advance.

Solution

  • As mentioned sqlcmd and bcp have poor/non-existent support for well-formed CSV.

    Instead, install the DbaTools in Powershell

    Import-Module dbatools;

    And simply use Invoke-DbaQuery

    Invoke-DbaQuery `
  -SqlInstance 'EC2AMAZ-5UNBD90' `
  -Query 'SELECT * FROM dbo.PMDOCS WHERE hmy=95613900;' `
  -As DataTable `
| Export-Csv -Path 'C:\pmdocs_ms_v1.csv' -NoTypeInformation;

    You can pass parameters using the -SqlParameter parameter.