SQL Server How to ignore the new line in table value?

cnjp1d6j 于 2023-06-21 发布在其他

关注(0)|答案(1)|浏览(161)

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.

sql-server

来源：https://stackoverflow.com/questions/76426639/how-to-ignore-the-new-line-in-table-value

1条答案

按热度按时间

bfnvny8b1#

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.

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server How to ignore the new line in table value?

1条答案

相关问题

热门标签

最新问答