logo标签列表

使用bcp实用程序和SQL Server 2008将表导出到文件，包括列标题（列名）。

sql-servercsvheaderbcp
47

我看过很多关于如何让bcp工具将列名和数据一起导出的方法。如果我只是将一个表导出到文本文件中，最简单的方法是什么，可以让bcp添加列标题？

这是我目前使用的bcp命令：

bcp myschema.dbo.myTableout myTable.csv /SmyServer01 /c /t, -T
我使用了with子句和一个虚拟列OrderCol来保证顺序。with temp as ( SELECT 'colName1','colName2','colName3','colName4','colName5','colName6','colName7','colName8',1 OrderCol Union all SELECT col1,col2,col3,col4,col5,col6,col7,col8, 2 OrderCol FROM TableName ) select col1,col2,col3,col4,col5,col6,col7,col8 from Temp order by OrderCol - kanagaraj palanisamy
19个回答
65
这种方法使用BCP自动输出包含行数据的列名。
该脚本会先写入一个包含列头信息（从INFORMATION_SCHEMA.COLUMNS表中读取）的文件，然后再将表数据追加到另一个文件中。
最终的输出结果被合并到TableData.csv中，其中包含了列头和行数据。只需替换顶部的环境变量以指定服务器、数据库和表名即可。
set BCP_EXPORT_SERVER=put_my_server_name_here
set BCP_EXPORT_DB=put_my_db_name_here
set BCP_EXPORT_TABLE=put_my_table_name_here

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from %BCP_EXPORT_DB%.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='%BCP_EXPORT_TABLE%'; select @colnames;" queryout HeadersOnly.csv -c -T -S%BCP_EXPORT_SERVER%

BCP %BCP_EXPORT_DB%.dbo.%BCP_EXPORT_TABLE% out TableDataWithoutHeaders.csv -c -t, -T -S%BCP_EXPORT_SERVER%

set BCP_EXPORT_SERVER=
set BCP_EXPORT_DB=
set BCP_EXPORT_TABLE=

copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv TableData.csv

del HeadersOnly.csv
del TableDataWithoutHeaders.csv

请注意，如果您需要提供凭据，请使用 -T 选项替换为 -U my_username -P my_password。
这种方法的优点是始终使用 INFORMATION_SCHEMA.COLUMNS 保持列名与表同步。缺点是它会创建临时文件。微软应该真正修复 bcp 实用程序以支持此功能。
此解决方案使用 here 中的 SQL 行串联技巧与 here 中的 bcp 想法相结合。
我使用select substring(@colnames,2,1000000000);而不是 select @colnames; ，因为 @colnames 变量在标题前面带有一个逗号。 - Galled
3
很棒的脚本，老兄。如果可以的话我会投两票的。有没有办法将表格和数据库的名称作为批处理脚本变量？ - CrazyTim
@CrazyTim，我已经将名称设置为环境变量以便于使用。很高兴它对你有用。 - Dylan Hogg
当我尝试时，我会收到以下错误：错误= [Microsoft] [SQL Native Client] 只有在复制到服务器时才能跳过主机文件列。 - thchaver
@thchaver，您使用的SQL Server版本是哪个？这个问题可能与SQL Server Express有关。 - Dylan Hogg
52
最简单的方法是使用queryout选项，并使用union all将列列表与实际表内容链接起来。
    bcp "select 'col1', 'col2',... union all select * from myschema.dbo.myTableout" queryout myTable.csv /SmyServer01 /c /t, -T

一个例子：
create table Question1355876
(id int, name varchar(10), someinfo numeric)

insert into Question1355876
values (1, 'a', 123.12)
     , (2, 'b', 456.78)
     , (3, 'c', 901.12)
     , (4, 'd', 353.76)

这个查询将返回带有标题作为第一行的信息（请注意数字值的转换）：

select 'col1', 'col2', 'col3'
union all
select cast(id as varchar(10)), name, cast(someinfo as varchar(28))
from Question1355876
命令将是：
bcp "select 'col1', 'col2', 'col3' union all select cast(id as varchar(10)), name, cast(someinfo as varchar(28)) from Question1355876" queryout myTable.csv /SmyServer01 /c /t, -T
2
抛出错误：将 varchar 值“COL001”转换为数据类型 int 失败。 - Galled
2
我添加了一个包含数字值的示例。您需要将数字列转换为varchar（或nvarchar）。 - Filip De Vos
2
Order By子句失败了！ - Srinivas
1
如果源表中的数据类型为int，则此方法无效。 - MichaelEvanchik
1
在查询中将列转换为 varchar 类型。 - Filip De Vos
显示剩余4条评论
26

适用于：

  • Windows 64位操作系统
  • SQL Server（已测试过 SQL Server 2017 ，可用于所有版本）：

选项1：命令提示符

sqlcmd -s, -W -Q "set nocount on; select * from [DATABASE].[dbo].[TABLENAME]" | findstr /v /c:"-" /b > "c:\dirname\file.csv"

其中：

  • [DATABASE].[dbo].[TABLENAME] 是要写入的表。
  • c:\dirname\file.csv 是要写入的文件（用引号括起来以处理路径中带有空格的情况）。
  • 输出的 .csv 文件包括标题。

注意：我倾向于避免使用 bcp：它是一个遗留工具，比 sqlcmd 早了十年，而且似乎总是在不引起一大堆麻烦的情况下运行不起来。

选项2：在 SQL 脚本内部

-- Export table [DATABASE].[dbo].[TABLENAME] to .csv file c:\dirname\file.csv
exec master..xp_cmdshell 'sqlcmd -s, -W -Q "set nocount on; select * from [DATABASE].[dbo].[TABLENAME]" | findstr /v /c:"-" /b > "c:\dirname\file.csv"'

故障排除：必须在 MSSQL 中启用 xp_cmdshell。

示例输出

文件：file.csv：

ID,Name,Height
1,Bob,192
2,Jane,184
3,Harry,186

速度

尽可能快：与 bcp 相同的速度，比从 SSMS 手动导出的速度快多倍。

参数解释（可选-可以忽略）

在 sqlcmd 中：

  • -s, 在每个列之间放置逗号。
  • -W 消除值两侧的填充。
  • set nocount on 消除查询末尾的垃圾行。

对于 findstr：

  • 所有这些只是移除标题下方的第二行下划线，例如 --- ----- ---- ---- ----- --。
  • /v /c:"-" 匹配任何以“-”开头的行。
  • /b 返回所有其他行。

导入到其他程序中

在 Excel 中：

  • 可以直接在 Excel 中打开文件。

在 Python 中：

import pandas as pd
df_raw = pd.read_csv("c:\dirname\file.csv")
3
太棒了！我特别喜欢它只需要一行SQL代码就可以运行导出。我可能会开始使用它而不是bcp。唯一需要注意的是，如果您在第一列中的任何值以“-”开头，它们将从导出中删除。这可能会对转换为varchar的负数产生混乱。 - Geoff Griswald
1
这太棒了。帮了我很多。谢谢。 - abhijitcaps
1
非常好，运行得很顺利。只需要想办法删除NULL并用空格或无替换，但查找/替换可以解决这个问题。 - lvanzyl
1
真聪明。所以基本上bcp必须消失？ - undefined
1
我刚刚自学了如何使用BCP，但为什么选择@Contango的第二个选项呢？哈哈 - undefined
15
一个好的替代品是SqlCmd，因为它包括标题，但它的缺点是为了人类可读性而添加数据周围的空格填充。您可以将SqlCmd与GnuWin32 sed（流编辑）实用程序结合使用来清理结果。这是一个对我有效的示例，尽管我不能保证它是万无一失的。
首先，导出数据：
sqlcmd -S Server -i C:\Temp\Query.sql -o C:\Temp\Results.txt -s"    "
-s" "是双引号中的制表符。我发现必须通过批处理文件运行此命令，否则Windows命令提示符将把制表符视为自动完成命令，并将文件名替换为制表符。如果Query.sql包含：
SELECT name, object_id, type_desc, create_date
FROM MSDB.sys.views
WHERE name LIKE 'sysmail%'

那么您将在Results.txt中看到类似以下的内容。 
name                                          object_id   type_desc           create_date            
-------------------------------------------   ----------- ------------------- -----------------------
sysmail_allitems                               2001442204 VIEW                2012-07-20 17:38:27.820
sysmail_sentitems                              2017442261 VIEW                2012-07-20 17:38:27.837
sysmail_unsentitems                            2033442318 VIEW                2012-07-20 17:38:27.850
sysmail_faileditems                            2049442375 VIEW                2012-07-20 17:38:27.860
sysmail_mailattachments                        2097442546 VIEW                2012-07-20 17:38:27.933
sysmail_event_log                              2129442660 VIEW                2012-07-20 17:38:28.040

(6 rows affected)
接下来，使用sed解析文本：
sed -r "s/ +\t/\t/g" C:\Temp\Results.txt | sed -r "s/\t +/\t/g" | sed -r "s/(^ +| +$)//g" | sed 2d | sed $d | sed "/^$/d" > C:\Temp\Results_New.txt

请注意，2d 命令表示删除第二行，$d 命令表示删除最后一行，而 "/^$/d" 则删除任何空行。
清理后的文件看起来像这样（虽然我用 | 替换了制表符以便在此处进行可视化）： 
name|object_id|type_desc|create_date
sysmail_allitems|2001442204|VIEW|2012-07-20 17:38:27.820
sysmail_sentitems|2017442261|VIEW|2012-07-20 17:38:27.837
sysmail_unsentitems|2033442318|VIEW|2012-07-20 17:38:27.850
sysmail_faileditems|2049442375|VIEW|2012-07-20 17:38:27.860
sysmail_mailattachments|2097442546|VIEW|2012-07-20 17:38:27.933
sysmail_event_log|2129442660|VIEW|2012-07-20 17:38:28.040
6

最近，我尝试了解如何实现以下内容。虽然我喜欢最受欢迎的解决方案，但对我来说并不起作用，因为我需要别名与脚本中输入的名称相同，所以我使用了一些批处理文件（得到同事的帮助）来完成自定义表名称。

启动bcp的批处理文件在脚本底部有一行代码，执行另一个脚本，将模板文件与标题名称和刚刚使用bcp导出的文件合并，使用以下代码。希望这能帮助其他人解决类似问题。

echo Add headers from template file to exported sql files....
Echo School 0031
copy e:\genin\templates\TEMPLATE_Courses.csv + e:\genin\0031\courses0031.csv e:\genin\finished\courses0031.csv /b
这是最好的答案。只要记住，你不必从批处理文件中执行此操作，如果你是通过 'xp_cmdshell' 调用复制命令的话，也可以这样做。 - Steve
6
我遇到了同样的问题。我需要使用SQL Server bcp工具导出列标题。这样，我可以将“headers”表中的数据与表格一起导出到同一个文件中。 
DECLARE @table_name  VARCHAR(50) ='mytable'
DECLARE @columnHeader VARCHAR(8000)
SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''+column_name +'''' FROM Nal2013.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@table_name
SELECT @raw_sql = 'bcp "SELECT '+ @columnHeader +' UNION ALL SELECT * FROM mytable" queryout c:\datafile.csv -c -t, -T -S '+ @@servername
EXEC  xp_cmdshell @raw_sql
祝编码愉快 :)
如果数据类型不同怎么办？ - MichaelEvanchik
如果数据类型不同，在SELECT中将任何不是VARCHAR或NVARCHAR的内容转换为VARCHAR。由于你无论如何都要写入到.CSV文件中，所以这不会对输出造成任何影响。 - Geoff Griswald
4

这里有一个相当简单的存储过程，同样可以解决问题...

    CREATE PROCEDURE GetBCPTable
    @table_name varchar(200)
AS
BEGIN
    DECLARE @raw_sql nvarchar(3000)

    DECLARE @columnHeader VARCHAR(8000)
    SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''+column_name +'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name

    DECLARE @ColumnList VARCHAR(8000)
    SELECT @ColumnList = COALESCE(@ColumnList+',' ,'')+ 'CAST('+column_name +' AS VARCHAR)' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name

    SELECT @raw_sql = 'SELECT '+ @columnHeader +' UNION ALL SELECT ' + @ColumnList + ' FROM ' + @table_name
    --PRINT @raw_SQL
    EXECUTE sp_executesql  @raw_sql
END
GO
3

每个人的版本都有一些不同。这是我多年来开发的版本。这个版本似乎考虑到了我遇到的所有问题。只需将数据集填充到表中，然后将表名传递给此存储过程即可。

我像这样调用此存储过程：

EXEC    @return_value = *DB_You_Create_The_SP_In*.[dbo].[Export_CSVFile]
        @DB = N'*YourDB*',
        @TABLE_NAME = N'*YourTable*',
        @Dir = N'*YourOutputDirectory*',
        @File = N'*YourOutputFileName*'

还有另外两个变量：

  • @NullBlanks -- 它将取走任何没有值的字段并将其置空。这非常有用，因为按照CSV规范，每个数据点应该周围加上引号。如果你有一个大的数据集，通过不在这些字段中使用""（两个双引号）可以节省相当多的空间。如果你觉得这个无用，则将其设置为0。
  • @IncludeHeaders -- 我有一个存储过程用于输出CSV文件，所以我在那里设置了这个标志，以防我不想要标题。

这将创建存储过程：

CREATE PROCEDURE [dbo].[Export_CSVFile] 
(@DB varchar(128),@TABLE_NAME varchar(128), @Dir varchar(255), @File varchar(250),@NULLBLANKS bit=1,@IncludeHeader bit=1)
AS

DECLARE @CSVHeader varchar(max)=''  --CSV Header
, @CmdExc varchar(8000)=''          --EXEC commands
, @SQL varchar(max)=''              --SQL Statements
, @COLUMN_NAME varchar(128)=''      --Column Names
, @DATA_TYPE varchar(15)=''         --Data Types

DECLARE @T table (COLUMN_NAME varchar(128),DATA_TYPE varchar(15))

--BEGIN Ensure Dir variable has a backslash as the final character
IF NOT RIGHT(@Dir,1) = '\' BEGIN SET @Dir=@Dir+'\' END
--END

--BEGIN Drop TEMP Table IF Exists
SET @SQL='IF (EXISTS (SELECT * FROM '+@DB+'.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TEMP_'+@TABLE_NAME+''')) BEGIN EXEC(''DROP TABLE ['+@DB+'].[dbo].[TEMP_'+@TABLE_NAME+']'') END'
EXEC(@SQL)
--END

SET @SQL='SELECT COLUMN_NAME,DATA_TYPE FROM '+@DB+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='''+@TABLE_NAME+''' ORDER BY ORDINAL_POSITION'
INSERT INTO @T
EXEC (@SQL)

SET @SQL=''
WHILE exists(SELECT * FROM @T)
    BEGIN
        SELECT top(1) @DATA_TYPE=DATA_TYPE,@COLUMN_NAME=COLUMN_NAME FROM @T
        IF @DATA_TYPE LIKE '%char%' OR @DATA_TYPE LIKE '%text'
            BEGIN 
            IF @NULLBLANKS = 1
                BEGIN
                    SET @SQL+='CASE PATINDEX(''%[0-9,a-z]%'','+@COLUMN_NAME+') WHEN ''0'' THEN NULL ELSE ''"''+RTRIM(LTRIM('+@COLUMN_NAME+'))+''"'' END AS ['+@COLUMN_NAME+'],' 
                END
            ELSE
                BEGIN
                    SET @SQL+='''"''+RTRIM(LTRIM('+@COLUMN_NAME+'))+''"'' AS ['+@COLUMN_NAME+'],' 
                END
            END
        ELSE
            BEGIN SET @SQL+=@COLUMN_NAME+',' END
            SET @CSVHeader+='"'+@COLUMN_NAME+'",'
            DELETE top(1) @T
    END 

IF LEN(@CSVHeader)>1 BEGIN SET @CSVHeader=RTRIM(LTRIM(LEFT(@CSVHeader,LEN(@CSVHeader)-1))) END

IF LEN(@SQL)>1 BEGIN SET @SQL= 'SELECT '+ LEFT(@SQL,LEN(@SQL)-1) + ' INTO ['+@DB+'].[dbo].[TEMP_'+@TABLE_NAME+'] FROM ['+@DB+'].[dbo].['+@TABLE_NAME+']' END
EXEC(@SQL)

IF @IncludeHeader=0 
    BEGIN
        --BEGIN Create Data file
        SET  @CmdExc ='BCP "'+@DB+'.dbo.TEMP_'+@TABLE_NAME+'" out "'+@Dir+'Data_'+@TABLE_NAME+'.csv" /c /t, -T' 
        EXEC master..xp_cmdshell @CmdExc
        --END
        SET  @CmdExc ='del '+@Dir+@File EXEC master..xp_cmdshell @CmdExc
        SET  @CmdExc ='ren '+@Dir+'Data_'+@TABLE_NAME+'.csv '+@File EXEC master..xp_cmdshell @CmdExc
    END 
else
    BEGIN

        --BEGIN Create Header and main file
        SET  @CmdExc ='echo '+@CSVHeader+'> '+@Dir+@File EXEC master..xp_cmdshell @CmdExc
        --END

        --BEGIN Create Data file
        SET  @CmdExc ='BCP "'+@DB+'.dbo.TEMP_'+@TABLE_NAME+'" out "'+@Dir+'Data_'+@TABLE_NAME+'.csv" /c /t, -T' 
        EXEC master..xp_cmdshell @CmdExc
        --END

        --BEGIN Merge Data File With Header File
        SET @CmdExc = 'TYPE '+@Dir+'Data_'+@TABLE_NAME+'.csv >> '+@Dir+@File EXEC master..xp_cmdshell @CmdExc
        --END

        --BEGIN Delete Data File
        SET @CmdExc = 'DEL /q '+@Dir+'Data_'+@TABLE_NAME+'.csv' EXEC master..xp_cmdshell @CmdExc
        --END
    END
--BEGIN Drop TEMP Table IF Exists
SET @SQL='IF (EXISTS (SELECT * FROM '+@DB+'.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TEMP_'+@TABLE_NAME+''')) BEGIN EXEC(''DROP TABLE ['+@DB+'].[dbo].[TEMP_'+@TABLE_NAME+']'') END'
EXEC(@SQL)
1
很好，谢谢！ 使用"@CmdExc varchar(max)" 时会出现以下错误： 流程期望类型为“varchar”的参数“command_string”。因此将其从max更改为8000，问题得到解决。 - vukis
3

这里的一些解决方案过于复杂。以下是只有4行代码的解决方案，没有批处理文件、没有外部应用程序，并且全部自包含在SQL服务器中。

在此示例中，我的表名为"MyTable"，它有两列名为Column1和Column2。Column2是一个整数，所以我们需要将其CAST为varchar进行导出：

DECLARE @FileName varchar(100)
DECLARE @BCPCommand varchar(8000)
DECLARE @ColumnHeader varchar(8000)

    SET @FileName = 'C:\Temp\OutputFile.csv'
 SELECT @ColumnHeader = COALESCE(@ColumnHeader+',' ,'')+ ''''+column_name +'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='MyTable'
    SET @BCPCommand = 'bcp "SELECT '+ @ColumnHeader +' UNION ALL SELECT Column1, CAST(Column2 AS varchar(100)) AS Column2 FROM MyTable" queryout "' + @FileName + '" -c -t , -r \n  -S . -T'
   EXEC master..xp_cmdshell @BCPCommand

您可以将此内容添加到存储过程中，以完全自动化创建带有标题行的 .CSV 文件。
2

您应该可以通过一个CTE视图和一个包含BCP代码的批处理文件来解决此问题。首先创建视图。由于这相对简单，我没有创建临时表。通常我会这样做。

CREATE VIEW [dbo].[vwxMySAMPLE_EXTRACT_COLUMNS]
  AS
    WITH MYBCP_CTE (COLUMN_NM, ORD_POS, TXT)
     AS
      ( SELECT  COLUMN_NAME
             , ORDINAL_POSITION
             , CAST(COLUMN_NAME AS VARCHAR(MAX))
        FROM [INFORMATION_SCHEMA].[COLUMNS]
        WHERE TABLE_NAME = 'xMySAMPLE_EXTRACT_NEW'
        AND ORDINAL_POSITION = 1

        UNION ALL

        SELECT  V.COLUMN_NAME
              , V.ORDINAL_POSITION
              , CAST(C.TXT + '|' + V.COLUMN_NAME AS VARCHAR(MAX))
        FROM [INFORMATION_SCHEMA].[COLUMNS]  V INNER JOIN MYBCP_CTE C
        ON V.ORDINAL_POSITION = C.ORD_POS+1
        AND V.ORDINAL_POSITION > 1
        WHERE TABLE_NAME = 'xMySAMPLE_EXTRACT_NEW'
      )

      SELECT CC.TXT
      FROM MYBCP_CTE CC INNER JOIN ( SELECT MAX(ORD_POS) AS MX_CNT
                                     FROM MYBCP_CTE C
                                    ) SC
      ON CC.ORD_POS = SC.MX_CNT

现在，创建批处理文件。我将它创建在我的Temp目录中，但我很懒。
  cd\
  CD "C:\Program Files\Microsoft SQL Server\110\Tools\Binn"

  set buildhour=%time: =0%
  set buildDate=%DATE:~4,10%
  set backupfiledate=%buildDate:~6,4%%buildDate:~0,2%%buildDate:~3,2%%time:~0,2%%time:~3,2%%time:~6,2%

  echo %backupfiledate%
  pause

上述代码只是创建一个日期以附加到您的文件末尾...接下来，第一个bcp语句使用递归cte连接所有内容。

  bcp "SELECT *  FROM [dbo].[vwxMYSAMPLE_EXTRACT_COLUMNS] OPTION (MAXRECURSION 300)" queryout C:\Temp\Col_NM%backupfiledate%.txt -c -t"|" -S MYSERVERTOLOGINTO -T -q
  bcp "SELECT *  FROM [myDBName].[dbo].[vwxMYSAMPLE_EXTRACT_NEW] " queryout C:\Temp\3316_PHYSDATA_ALL%backupfiledate%.txt -c -t"|" -S MYSERVERTOLOGINTO -T -q

现在使用复制命令将它们合并在一起：
  copy C:\Temp\Col_NM%backupfiledate%.txt  + C:\Temp\3316_PHYSDATA_ALL%backupfiledate%.txt  C:\Temp\3316_PHYSDATA_ALL%backupfiledate%.csv

一切就绪

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接