从SQL Server数据库导出xlsx文件

I'm trying to save Excel files in my SQL Server database as BLOB and export them. Importing the file was quiet easy, but when I'm trying to export them to the file system, the file is damaged and unreadable.

我正在尝试将我的SQL Server数据库中的Excel文件保存为BLOB并导出它们。导入文件非常简单，但是当我尝试将它们导出到文件系统时，文件已损坏且无法读取。

I was quite shocked, because I thought Microsoft wouldn't have much problems importing and exporting their own file types.

我感到非常震惊，因为我认为微软在导入和导出自己的文件类型时不会遇到太多问题。

INSERT INTO TestBlob(tbName, tbDesc, tbBin) 
    SELECT 
        'testfile.xlsx', 'testfile', BulkColumn 
    FROM 
        OPENROWSET (Bulk 'C:\temp\testfile.xlsx', Single_Blob) AS tb

I already tried to export it by bcp but I just got a corrupted file.

我已经尝试通过bcp导出它，但我刚收到一个损坏的文件。

Do I have to provide a special format file for xlsx files? Are there any other ways to get the xlsx files out of the database?

我是否必须为xlsx文件提供特殊格式文件？有没有其他方法可以将xlsx文件从数据库中删除？

Thanks in advance

提前致谢

BR. RF

BR。 RF

1 个解决方案

#1

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


USE [AdventureWorks];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO

https://blog.sqlauthority.com/2008/01/08/sql-server-2005-export-data-from-sql-server-2005-to-microsoft-excel-datasheet/

https://blog.sqlauthority.com/2008/01/08/sql-server-2005-export-data-from-sql-server-2005-to-microsoft-excel-datasheet/

Or . . .

要么 。 。 。

1 Export data to existing EXCEL file from SQL Server table

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



2 Export data from Excel to new SQL Server table

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



3 Export data from Excel to existing SQL Server table

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



4 If you dont want to create an EXCEL file in advance and want to export data to it, use


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

(Now you can find the file with data in tabular format)


5 To export data to new EXCEL file with heading(column names), create the following procedure


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)


After creating the procedure, execute it by supplying database name, table name and file path

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

As an alternative, you could use "External data" from Excel. It can use ODBC connection to fetch data from external source: Data/Get External Data/New Database Query. That way, even if the data in the database changes, you can easily refresh.

作为替代方案，您可以使用Excel中的“外部数据”。它可以使用ODBC连接从外部源获取数据：数据/获取外部数据/新数据库查询。这样，即使数据库中的数据发生变化，您也可以轻松刷新。