使用“|”获取* .csv文件中的查询结果符号作为我的分隔符 [英] Get the Query result in *.csv file with '|' Symbol as my delimiter

问题描述

我需要以csv文件格式获取查询结果，并使用''|''符号作为我的分隔符。

例如我有一个查询，例如

  SELECT  Ord_ID，Ord_Amt  FROM  OrderDtls 

上面的查询结果，我需要将'''''存储在''CSV''文件中'作为我的分隔符。



问候，

GVPrabu

解决方案

请参阅 http://www.connectionstrings.com/textfile [ ^ ]有关如何更改默认分隔符的详细信息。

我找到了解决方案.. ..



我们可以使用以下SQL命令...

  EXECUTE  master.dbo.xp_cmdshell '  bcpSELECT object_name（object_id）作为TbLName，名称为ColName FROM SandBox.sys.columns ORDER BY object_name（object_id） ，column_idqueryout C：\ Data\Objects.txt -t| -c -T' 

否则我们将执行以下Management Studio设置...



如果您使用的是Microsoft Sql Server管理工作室，那么这是可配置的。

（实际上在查询分析器中几乎完全相同）



工具 - >选项 - >查询结果 - > Sql Server - >结果到文本。



将输出格式从制表符分隔为自定义分隔。

< b>将管道放入自定义分隔符框。



返回查询选择查询 - >结果到文件。执行文件，给它一个磁盘路径和一个名字。



文件将是你的名字，带有.rpt扩展名，你可以重命名它有一个.txt扩展名。



如果你不想要，你显然必须将结果设置为文本值回到之前的设置这是永久性的。



偶尔适用一次。但如果你必须经常生成这个文件，我会考虑创建一个SSIS包。或者如上所述。



问候，

GVPrabu

只需复制并粘贴此代码你的sql编辑器

你会在你的C：驱动器中找到一个* .csv文件

  DECLARE   @ Ord_ID   varchar （ 50 ）
  DECLARE   @ Return   varchar （ 50 ）
  DECLARE   @ Ord_Amt   INT  
  DECLARE   @ cmd   Nvarchar （ 2000 ）
  DECLARE   @ Order   CURSOR  
  SET   @ Order  =  CURSOR   FOR  
  SELECT  Ord_ID，Ord_Amt  FROM  OrderDtls 
  OPEN   @ Order  
  FETCH  NEXT 
  FROM   @ Order   INTO   @ Ord_ID ， @ Ord_Amt  
  WHILE   @@ FETCH_STATUS  =  0  
 < span class =code-keyword> BEGIN  
  set   @ cmd  = '  echo' + @ Ord_ID + ' ;' +  convert （ varchar （ 50 ）， @ Ord_Amt ）+ ' >> c：\ myTable.csv'   -   设置@cmd ='echo'+ @ Ord_ID +'>> c：\ myTable.csv' 
  EXEC   @ Return  = MASTER..xp_cmdshell  @ cmd  
 < span class =code-keyword> FETCH  NEXT 
  FROM   @ Order   INTO   @ Ord_ID ， @ Ord_Amt  
  END  
  CLOSE   @ Or der  
  DEALLOCATE   @ Order  

Hi,

I need to get my query result in "csv" file format with ''|'' Symbol as my delimiter.
for example I am having a query like

SELECT Ord_ID, Ord_Amt FROM OrderDtls 

The above Query result, I need to store in ''CSV'' file with ''|'' as my delimiter.

Regards,
GVPrabu

解决方案

See http://www.connectionstrings.com/textfile[^] for details of how to change the default separator.

I found the solution....

We can use the following SQL Command for this...

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT object_name(object_id) As TbLName,name as ColName FROM SandBox.sys.columns  ORDER BY object_name(object_id), column_id" queryout C:\Data\Objects.txt -t"|" -c -T ' 

or else we will do the following Management Studio Settings...

If your using Microsoft Sql Server management studio this is Configurable.
(actually almost the exact same in query analyzer)

Tool--> Options -->Query Results --> Sql Server --> Results to Text.

Change output format from Tab Delimited to Custom Delimited.
Put a Pipe in the Custom Delimiter Box.

Go back to your query select Query --> Results to File. Execute file, Give it a Disk path and a name.

The file will be your name with a .rpt Extension, you can rename it to have a .txt extension.

You obviously have to set the results to text value back to your previous setting if you don''t want this permanently.

Works good for a one time once in a while thing. but if you must generate this file frequently I would look at creating a SSIS Package. or as suggested above.

Regards,
GVPrabu

just copy and paste this code in your sql editor
you will find a *.csv file in you C: drive

DECLARE @Ord_ID varchar(50)
DECLARE  @Return varchar(50)
DECLARE @Ord_Amt INT
DECLARE @cmd Nvarchar(2000)
DECLARE @Order CURSOR
SET @Order = CURSOR FOR
SELECT Ord_ID, Ord_Amt FROM OrderDtls
OPEN @Order
FETCH NEXT
FROM @Order INTO @Ord_ID,@Ord_Amt
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = 'echo ' +  @Ord_ID+ ';' + convert(varchar(50), @Ord_Amt) + ' >>c:\myTable.csv'--set @cmd = 'echo ' + @Ord_ID+' >>c:\myTable.csv'
EXEC @Return = MASTER..xp_cmdshell @cmd 
FETCH NEXT
FROM  @Order INTO @Ord_ID,@Ord_Amt
END
CLOSE @Order
DEALLOCATE @Order

这篇关于使用“|”获取* .csv文件中的查询结果符号作为我的分隔符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！