Я видел несколько хаков, чтобы попытаться заставить утилиту bcp экспортировать имена столбцов вместе с данными. Если все, что я делаю, это сброс таблицы в текстовый файл, что является самым простым способом, чтобы bcp добавлял заголовки столбцов?
Здесь команда bcp, которую я использую в настоящее время:
bcp myschema.dbo.myTableout myTable.csv /SmyServer01 /c /t, -T
Проще всего использовать параметр 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 будет:
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
Этот метод автоматически выводит имена столбцов с данными вашей строки с помощью BCP.
script записывает один файл для заголовков столбцов (читается из таблицы 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. Недостатком является то, что он создает временные файлы. Microsoft должна действительно исправить утилиту bcp для поддержки этого.
Это решение использует трюк конкатенации строки SQL из здесь в сочетании с идеями bcp из здесь
Я пытался понять, как это сделать в последнее время, и хотя мне нравится самое популярное решение наверху, оно просто не сработает для меня, поскольку мне нужны имена, которые будут псевдонимом, который я ввел в script, поэтому Для выполнения пользовательских имен таблиц я использовал несколько командных файлов (с некоторой помощью от коллеги).
Пакетный файл, который инициирует bcp, имеет строку внизу script, которая выполняет другой script, который объединяет файл шаблона с именами заголовков и файл, который был только что экспортирован с помощью 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
Хорошей альтернативой является SqlCmd, поскольку она включает заголовки, но имеет недостаток добавления пробелов вокруг данных для удобства чтения. Вы можете комбинировать SqlCmd с утилитой GnuWin32 sed (stream editing) для очистки результатов. Вот пример, который работал у меня, хотя я не могу гарантировать, что он пуленепробиваемый.
Сначала экспортируйте данные:
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
У меня была такая же проблема. Мне нужно было экспортировать заголовок столбца с помощью утилиты bcp SQL-сервера. Этот способ я экспортировал табличные "заголовки" с данными в один и тот же экспортированный файл за один раз.
DECLARE @table_name VARCHAR(50) ='mytable' DECLARE @columnHeader VARCHAR(8000) SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ ''''+column_name +'''' FROM Nal2013.INFORMATION_SCHEMA.COLUMNS WHERE [email protected]_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
Счастливое кодирование:)
Как и решение marc_s, вы также можете использовать osql или SQLCMD
Это включает заголовки, и он может действовать как bcp с использованием -Q и -o. Однако они не поддерживают файлы формата, такие как bcp.
Вот довольно простая хранимая процедура, которая тоже делает трюк.
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
Из всего, что я знаю, BCP только экспортирует данные - я не думаю, что есть способ заставить его экспортировать строку заголовка с именами столбцов тоже.
Один из распространенных способов решения этой проблемы - использовать представление для ваших фактических данных для экспорта, которое в основном делает UNION ALL над двумя утверждениями:
а затем используйте bcp в этом представлении вместо прямой таблицы данных.
Марк
Все версии немного отличаются друг от друга. Это версия, которую я разработал на протяжении многих лет. Кажется, что эта версия отражает все проблемы, с которыми я столкнулся. Просто заполните набор данных в таблицу, затем передайте имя этой таблицы в эту хранимую процедуру.
Я вызываю эту хранимую процедуру следующим образом:
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(max)='' --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 @[email protected]+'\' END
--END
--BEGIN Drop TEMP Table IF Exists
SET @SQL='IF (EXISTS (SELECT * FROM '[email protected]+'.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TEMP_'[email protected]_NAME+''')) BEGIN EXEC(''DROP TABLE ['[email protected]+'].[dbo].[TEMP_'[email protected]_NAME+']'') END'
EXEC(@SQL)
--END
SET @SQL='SELECT COLUMN_NAME,DATA_TYPE FROM '[email protected]+'.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='''[email protected]_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]%'','[email protected]_NAME+') WHEN ''0'' THEN NULL ELSE ''"''+RTRIM(LTRIM('[email protected]_NAME+'))+''"'' END AS ['[email protected]_NAME+'],'
END
ELSE
BEGIN
SET @SQL+='''"''+RTRIM(LTRIM('[email protected]_NAME+'))+''"'' AS ['[email protected]_NAME+'],'
END
END
ELSE
BEGIN SET @[email protected]_NAME+',' END
SET @CSVHeader+='"'[email protected]_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 ['[email protected]+'].[dbo].[TEMP_'[email protected]_NAME+'] FROM ['[email protected]+'].[dbo].['[email protected]_NAME+']' END
EXEC(@SQL)
IF @IncludeHeader=0
BEGIN
--BEGIN Create Data file
SET @CmdExc ='BCP "'[email protected]+'.dbo.TEMP_'[email protected]_NAME+'" out "'[email protected]+'Data_'[email protected]_NAME+'.csv" /c /t, -T'
EXEC master..xp_cmdshell @CmdExc
--END
SET @CmdExc ='del '[email protected][email protected] EXEC master..xp_cmdshell @CmdExc
SET @CmdExc ='ren '[email protected]+'Data_'[email protected]_NAME+'.csv '[email protected] EXEC master..xp_cmdshell @CmdExc
END
else
BEGIN
--BEGIN Create Header and main file
SET @CmdExc ='echo '[email protected]+'> '[email protected][email protected] EXEC master..xp_cmdshell @CmdExc
--END
--BEGIN Create Data file
SET @CmdExc ='BCP "'[email protected]+'.dbo.TEMP_'[email protected]_NAME+'" out "'[email protected]+'Data_'[email protected]_NAME+'.csv" /c /t, -T'
EXEC master..xp_cmdshell @CmdExc
--END
--BEGIN Merge Data File With Header File
SET @CmdExc = 'TYPE '[email protected]+'Data_'[email protected]_NAME+'.csv >> '[email protected][email protected] EXEC master..xp_cmdshell @CmdExc
--END
--BEGIN Delete Data File
SET @CmdExc = 'DEL /q '[email protected]+'Data_'[email protected]_NAME+'.csv' EXEC master..xp_cmdshell @CmdExc
--END
END
--BEGIN Drop TEMP Table IF Exists
SET @SQL='IF (EXISTS (SELECT * FROM '[email protected]+'.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TEMP_'[email protected]_NAME+''')) BEGIN EXEC(''DROP TABLE ['[email protected]+'].[dbo].[TEMP_'[email protected]_NAME+']'') END'
EXEC(@SQL)
Вы можете сделать то же самое с хранимой процедурой.
--Create stored procedure
CREATE PROCEDURE [dbo].[spTest]
AS
BEGIN
SELECT 'Name','ID','Created'
UNION ALL
SELECT name, convert(nvarchar,database_id), convert(nvarchar,create_date)
FROM sys.databases
END
GO
Затем создайте работу агента SQL с шагом CmdExec. Это выполнит хранимую процедуру и создаст файл csv в указанном месте.
bcp "EXEC DatabaseNameHere.dbo.spTest" queryout "\\IPAddressHere\c$\temp\testFile.csv" -T -c -t,
В последней версии sqlcmd добавлена опция -w для удаления дополнительного места после значения поля; однако он не помещает кавычки вокруг строк, что может быть проблемой для CSV при импорте значения поля, содержащего запятую.
Я успешно достиг этого с помощью кода ниже. Поместите ниже код в новое окно запроса SQL Server и попробуйте
CREATE TABLE tempDBTableDetails ( TableName VARCHAR(500), [RowCount] VARCHAR(500), TotalSpaceKB VARCHAR(500),
UsedSpaceKB VARCHAR(500), UnusedSpaceKB VARCHAR(500) )
-- STEP 1 ::
DECLARE @cmd VARCHAR(4000)
INSERT INTO tempDBTableDetails
SELECT 'TableName', 'RowCount', 'TotalSpaceKB', 'UsedSpaceKB', 'UnusedSpaceKB'
INSERT INTO tempDBTableDetails
SELECT
S.name +'.'+ T.name as TableName,
Convert(varchar,Cast(SUM(P.rows) as Money),1) as [RowCount],
Convert(varchar,Cast(SUM(a.total_pages) * 8 as Money),1) AS TotalSpaceKB,
Convert(varchar,Cast(SUM(a.used_pages) * 8 as Money),1) AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables T
INNER JOIN sys.partitions P ON P.OBJECT_ID = T.OBJECT_ID
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
INNER JOIN sys.allocation_units A ON p.partition_id = a.container_id
WHERE T.is_ms_shipped = 0 AND P.index_id IN (1,0)
GROUP BY S.name, T.name
ORDER BY SUM(P.rows) DESC
-- SELECT * FROM [FIINFRA-DB-SIT].dbo.tempDBTableDetails ORDER BY LEN([RowCount]) DESC
SET @cmd = 'bcp "SELECT * FROM [FIINFRA-DB-SIT].dbo.tempDBTableDetails ORDER BY LEN([RowCount]) DESC" queryout "D:\Milind\export.xls" -U sa -P dbowner -c'
Exec xp_cmdshell @cmd
--DECLARE @HeaderCmd VARCHAR(4000)
--SET @HeaderCmd = 'SELECT ''TableName'', ''RowCount'', ''TotalSpaceKB'', ''UsedSpaceKB'', ''UnusedSpaceKB'''
exec master..xp_cmdshell 'BCP "SELECT ''TableName'', ''RowCount'', ''TotalSpaceKB'', ''UsedSpaceKB'', ''UnusedSpaceKB''" queryout "d:\milind\header.xls" -U sa -P dbowner -c'
exec master..xp_cmdshell 'copy /b "d:\Milind\header.xls"+"d:\Milind\export.xls" "d:/Milind/result.xls"'
exec master..xp_cmdshell 'del "d:\Milind\header.xls"'
exec master..xp_cmdshell 'del "d:\Milind\export.xls"'
DROP TABLE tempDBTableDetails
Вы должны решить эту проблему с помощью одного вида 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
Все установленные