如何忽略表格数值中的换行?

huangapple go评论74阅读模式
英文:

How to ignore the new line in table value?

问题

我正在尝试将 SQL Server 表导出为 CSV 文件。我所做的是调用 sqlcmd 命令:

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

这个表中的特定列可能包含换行符,就像这个记录一样,它们写入 CSV 文件的方式如下:

95613900,25,1731323,2020-06-06 11:59:04.000,\\asp1\kfs\kusersdefpaths\Pangea Properties\Live\Attachments\WorkOrder
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
6-2020883_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

一个单独的记录被存储在两行中。请建议我应该怎么做才能让它们在一行中。提前感谢。

英文:

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-202083_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.

答案1

得分: 1

如上所述,sqlcmdbcp 对格式良好的 CSV 文件支持不足或不存在。

相反,安装 PowerShell 中的 DbaTools

Import-Module dbatools;

然后简单地使用 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;

您可以使用 -SqlParameter 参数传递参数。

英文:

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.

huangapple
  • 本文由 发表于 2023年6月8日 03:43:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/76426639.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定