SQL Server CSV 提取表中的列中包含换行符、双引号和逗号吗？
我在 CSV 中提取了大约 10 个表，其中使用“作为文本限定符。问题是我的提取在 Excel 中看起来不正确，因为几列中存在特殊字符。某些列在本应保留在列中时却分成了新行。
- 我已经使用 Management Studio 导出功能手动执行此操作，但是使用脚本将 10 个表提取到 CSV 并使用双引号限定符的最佳
- 方法
- 是什么 ？我在我的列中处理换行符，我们需要它们来迁移到新系统，但 PM 想要使用 Excel 打开文件并进行修改，
我知道问题主要在于 Excel 是这样的。解释文件，其中加载实用程序到另一个数据库可能不会对新行执行任何特殊操作，但是数据中的双引号和逗号怎么样，如果我不关心excel，我必须逃避它吗
？
I extracted some 10 tables in CSV with " as the text qualifier. Problem is my extract does not look right in Excel because of special characters in a few columns. Some columns are breaking into a new row when it should stay in the column.
- I've been doing it manually using the management studio export feature, but what's the best extract the 10 tables to CSV with the double quote qualifier using a script?
- Will I have to escape commas and double quotes? Best way to do this?
- How should I handle newline codes in my columns, we need them for migration to a new system, but the PM wants to open the files and make modifications using Excel. Can they have it both ways?
I understand that much of the problem is that Excel is interpreting the file where a load utility into another database might not do anything special with new line, but what about double quotes and commas in the data, if I don't care about excel, must I escape that?
Many Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果您使用的是 SQL Server 2005 或更高版本，导出向导将为您导出 Excel 文件。
右键单击数据库，选择任务->导出数据...
将源设置为数据库。
将目的地设置为 excel。
在向导结束时，选择创建 SSIS 包的选项。然后，您可以创建一个作业来按计划或按需执行包。
If you are using SQL Server 2005 or later, the export wizard will export the excel file out for you.
Right click the database, select Tasks-> Export Data...
Set the source to be the database.
Set the destination to excel.
At the end of the wizard, select the option to create an SSIS package. You can then create a job to execute the package on a schedule or on demand.
我建议永远不要使用逗号作为分隔符 - 它们在其他地方出现得太频繁。使用选项卡，因为选项卡不太容易包含在 Excel 表格中。
确保字段开头不包含空格，除非您希望字段中包含该空格。
尝试将文本 lf 更改为文字文本 \n。即：
您可能有：
0,1,"第 1 行
Line 2", 3
我建议你想要：
0 1 "Line 1\nLine 2" 3
（假设行之间的间距是制表符）
祝你好运
I'd suggest never using commas for your delimiter - they show up too frequently in other places. Use a tab, since a tab isn't too easy to include in Excel tables.
Make sure you never start a field with a space unless you want that space in the field.
Try changing your text lf's into the literal text \n. That is:
You might have:
0,1,"Line 1
Line 2", 3
I suggest you want:
0 1 "Line 1\nLine 2" 3
(assuming the spacing between lines are tabs)
Good luck
据我所知，csv 列中不能有新行。如果您知道列可能包含逗号、双引号或换行符，那么您可以使用此 SQL 语句将值提取为有效的 csv
SELECT '"' + REPLACE(REPLACE(REPLACE(CAST([yourColumnName] AS VARCHAR(MAX)), '"', '""'), char(13), ''), char(10), '') + '"' FROM yourTable。As far as I know, you cannot have new line in csv columns. If you know a column could have comma, double quotes or new line, then you can use this SQL statement to extract the value as valid csv
SELECT '"' + REPLACE(REPLACE(REPLACE(CAST([yourColumnName] AS VARCHAR(MAX)), '"', '""'), char(13), ''), char(10), '') + '"' FROM yourTable.