在SQL Server 2012中使用格式提取CSV文件 [英] Extract CSV file with format in SQL Server 2012

问题描述

我想从SQL服务器中提取CSV文件，我设法使用以下代码执行此操作。

  DECLARE   @ bcpCommand1   VARCHAR （ 2000 ）
  SET   @ bcpCommand1  = '  bcp SELECT * from ## tblUserqueryout' 
  SET   @ bcpCommand1  = < span class =code-sdkkeyword> @ bcpCommand1  + '  C：\OutPut \ CSV \\ \\ My_Output.csv -c -w -T -S197.1.1.1 -Umyuser -Pypypwd， -  CRAW' 
 
  EXEC  CPS..xp_cmdshell  @ bcpCommand1  

但问题是...



1）大整数值自动转换为科学记数法.Inste广告我希望显示所有数字。



2）我想将日期显示为'01 -Dec-2015 11:20 AM' 。



3）如果任何数据包含逗号，则数据将被分割到下一列。例如名称是mohan，Raj然后在csv数据中Mohan在一列中而raj在另一列中。如果要避免那样



4）有没有使用格式文件提取csv。怎么做？



这是我想提取到格式化的CSV文件。



有没有办法做到这一点。请帮助我并发布代码。

解决方案

参见https://msdn.microsoft.com/en-us/library/ms709353(v=vs。 85）.aspx [ ^ ]。

I want to extract the CSV file from SQL server and I manage to do that with the following codes.

DECLARE @bcpCommand1 VARCHAR(2000) 
SET @bcpCommand1 = 'bcp " SELECT * from ##tblUser " queryout' 
SET @bcpCommand1 = @bcpCommand1 + ' C:\OutPut\CSV\My_Output.csv -c -w -T -S197.1.1.1 -Umyuser -Pmypwd","-CRAW'  

EXEC CPS..xp_cmdshell @bcpCommand1 

But the problem is ...

1)The big integer values are automatically coverted to scientific notation.Instead of this I want to display all the digits as it is.

2)I want to display the date as '01-Dec-2015 11:20 AM'.

3)If any data contains comma then the data is splitted to the next column. for example name is "mohan,Raj" then in the csv data Mohan is in one column and raj in another column.how to avoid that

4) Is there any way extract csv using format file.How to do that?

That is I want to extract to CSV file applied with formatting.

Is there any way to do that.Please help me on this and post the code for that.

解决方案

See https://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx[^].

这篇关于在SQL Server 2012中使用格式提取CSV文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！