我看过很多关于如何让bcp工具将列名和数据一起导出的方法。如果我只是将一个表导出到文本文件中,最简单的方法是什么,可以让bcp添加列标题?
这是我目前使用的bcp命令:
bcp myschema.dbo.myTableout myTable.csv /SmyServer01 /c /t, -T
我看过很多关于如何让bcp工具将列名和数据一起导出的方法。如果我只是将一个表导出到文本文件中,最简单的方法是什么,可以让bcp添加列标题?
这是我目前使用的bcp命令:
bcp myschema.dbo.myTableout myTable.csv /SmyServer01 /c /t, -T
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
select substring(@colnames,2,1000000000);
而不是 select @colnames;
,因为 @colnames
变量在标题前面带有一个逗号。 - Galledqueryout
选项,并使用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
适用于:
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
是要写入的文件(用引号括起来以处理路径中带有空格的情况)。注意:我倾向于避免使用 bcp
:它是一个遗留工具,比 sqlcmd
早了十年,而且似乎总是在不引起一大堆麻烦的情况下运行不起来。
-- 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 中:
在 Python 中:
import pandas as pd
df_raw = pd.read_csv("c:\dirname\file.csv")
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%'
接下来,使用sed解析文本: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 -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
最近,我尝试了解如何实现以下内容。虽然我喜欢最受欢迎的解决方案,但对我来说并不起作用,因为我需要别名与脚本中输入的名称相同,所以我使用了一些批处理文件(得到同事的帮助)来完成自定义表名称。
启动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
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祝编码愉快 :)
这里有一个相当简单的存储过程,同样可以解决问题...
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
每个人的版本都有一些不同。这是我多年来开发的版本。这个版本似乎考虑到了我遇到的所有问题。只需将数据集填充到表中,然后将表名传递给此存储过程即可。
我像这样调用此存储过程:
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*'
还有另外两个变量:
这将创建存储过程:
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)
这里的一些解决方案过于复杂。以下是只有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
您应该可以通过一个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
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
一切就绪