英文:
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\WorkOrder95613900,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
如上所述,sqlcmd
和 bcp
对格式良好的 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论