T-SQL: 새 Excel 파일로 내보내기

T-SQL: 새 Excel 파일로 내보내기

나는 다양한 일을 하는 스크립트를 가지고 있고 최종 결과는 큰 테이블 하나입니다.이 최종 테이블을 새로운 Excel 파일(열 헤더 포함)로 내보낼 수 있는 방법이 궁금합니다.

저는 이것을 대본 내에서 해야 할 것입니다.

SQL에서 Excel로 내보내기 위한 가장 좋은 게시물은 다음과 같습니다.

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

사용자로부터 인용하기madhivanan,

DTS 및 내보내기 마법사를 사용하는 것 외에도 이 쿼리를 사용하여 SQL Server 2000에서 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페이지라는 엄청난 분량이지만, 다른 사람들이 다양한 방법을 보여줄 뿐만 아니라 사람들이 어떻게 해야 하는지에 대한 질문을 하기 때문입니다.

전체적으로 그 스레드를 따르고 사람들이 질문한 다양한 질문들과 그것들이 어떻게 해결되는지 살펴보세요.저는 대충 훑어보기만 해도 꽤 많은 지식을 습득했고 예상되는 결과를 얻기 위해 그 일부를 사용했습니다.

단일 셀을 업데이트하는 방법

또한 피터 라슨이라는 회원이 다음과 같은 글을 올립니다.여기서 한 가지가 빠진 것 같습니다.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 회원은 또한 이것을 하는 방법에 대한 블로그 엔트리를 여기에 가지고 있습니다.

참고 자료: www.sqlteam.com (참고 자료: 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

PowerShell 사용:

$Server = "TestServer"
$Database = "TestDatabase"
$Query = "select * from TestTable"
$FilePath = "C:\OutputFile.csv"

# This will overwrite the file if it already exists.
Invoke-Sqlcmd -Query $Query -Database $Database -ServerInstance $Server | Export-Csv $FilePath

제가 평소에 필요한 것은 엑셀로 읽을 수 있는 CSV 파일뿐입니다.그러나 실제 Excel 파일이 필요한 경우 일부 코드를 눌러 CSV 파일을 Excel 파일로 변환합니다.이 대답은 이것에 대한 해결책을 제공하지만, 저는 그것을 테스트하지 않았습니다.

언급URL : https://stackoverflow.com/questions/9086880/t-sql-export-to-new-excel-file