☰

T-SQL: 新しい Excel ファイルにエクスポートする 質問する

tag-icon • tag-icon sql-server-2008 t-sql excel
T-SQL: 新しい Excel ファイルにエクスポートする 質問する

さまざまなことを実行するスクリプトがあり、最終結果は 1 つの大きな表になります。この最終的な表を新しい Excel ファイル (列ヘッダーも含む) にエクスポートするにはどうすればよいか知りたいです。

これをスクリプト内で実行する必要があります。

ベストアンサー1

これは、SQL から Excel にエクスポートするための、これまでのところ最高の投稿です。

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

ユーザーからの引用ですmadhivanan。

DTSとエクスポートウィザードを使用する以外に、このクエリを使用してSQL Server2000からExcelにデータをエクスポートすることもできます。

テーブル列と同じヘッダーを持つtestingという名前のExcelファイルを作成し、次のクエリを使用します。

1 SQL Server テーブルから既存の EXCEL ファイルにデータをエクスポートする

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable

2 Excelから新しいSQL Serverテーブルにデータをエクスポートする

select * 
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')

3 Excel から既存の SQL Server テーブルにデータをエクスポートする (編集済み)

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [SheetName$]')

4 事前にEXCELファイルを作成したくない場合は、データをエクスポートするには、

EXEC sp_makewebtask 
    @outputfile = 'd:\testing.xls', 
    @query = 'Select * from Database_name..SQLServerTable', 
    @colheaders =1, 
    @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

（これで、表形式のデータを含むファイルを見つけることができます）

5 見出し（列名）付きの新しいEXCELファイルにデータをエクスポートするには、次の手順を作成します。

create procedure proc_generate_excel_with_columns
(
    @db_name    varchar(100),
    @table_name varchar(100),   
    @file_name  varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
    @columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
    information_schema.columns
where 
    table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

プロシージャを作成したら、データベース名、テーブル名、ファイル パスを指定して実行します。

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

なんと 29 ページもありますが、これは、他の人がさまざまな方法を示しており、また、これと同じように、実行方法について質問している人がいるためです。

そのスレッドを最後までたどり、人々が尋ねたさまざまな質問とその解決方法を調べてください。ざっと目を通しただけでもかなりの知識が得られ、その一部を使用して期待どおりの結果を得ることができました。

単一セルを更新するには

同じくメンバーの Peter Larson は次のように投稿しています: ここで欠けているものが 1 つあると思います。Excel ファイルにエクスポートおよびインポートできるのはすばらしいことですが、単一のセルを更新するのはどうでしょうか。または、セルの範囲を更新するのはどうでしょうか。

これがそれをどう管理するかの原則です

update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\test.xls;hdr=no', 
'SELECT * FROM [Sheet1$b7:b7]') set f1 = -99

次のように Excel に数式を追加することもできます。

update OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\test.xls;hdr=no', 
'SELECT * FROM [Sheet1$b7:b7]') set f1 = '=a7+c7'

T-SQL を使用して列名をエクスポートする

メンバーのMladen Prajdicも、これを行う方法についてブログ記事を書いています。ここ

参考文献:詳しくはこちら（ちなみに、これはSQL Serverをさらに活用したい人にとっては素晴らしいブログ/フォーラムです）。エラー参照には、これ

発生する可能性のあるエラー

次のエラーが表示された場合:

OLE DB プロバイダー 'Microsoft.Jet.OLEDB.4.0' は分散クエリには使用できません

次にこれを実行します:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

おすすめ記事