MSSQL数据导入导出Excel的相关代码

MSSQL数据导入导出Excel的相关代码

参考： https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

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)

 

创建完上面存储过程，运行时，提供数据库名、表名和文件位置即可。

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

6 To update single cells

A member also there Peter Larson posts the following: I think one thing is missing here. It is great to be able to Export and Import to Excel files, but how about updating single cells? Or a range of cells?

This is the principle of how you do manage that

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

You can also add formulas to Excel using this:

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'

 

7 Exporting with column names using T-SQL

参考：https://weblogs.sqlteam.com/mladenp/2006/07/25/10771/

USE master 
GO
IF
    object_id( 'spExportData' ) IS NOT NULL DROP PROC spExportData 
GO
    CREATE PROC spExportData ( @dbName VARCHAR ( 100 ) = 'master',
    @SQL VARCHAR ( 5000 ) = '',
    
    @fullFileName VARCHAR ( 100 ) = '' ) AS
IF
    @SQL = '' 
    OR @fullFileName = '' BEGIN
SELECT
    0 AS ReturnValue – failure RETURN 
    END -- if DB isn't passed in set it to master
SELECT
    @dbName = 'use ' + @dbName + ';'
IF
    object_id( '##TempExportData' ) IS NOT NULL DROP TABLE ## TempExportData
IF
    object_id( '##TempExportData2' ) IS NOT NULL DROP TABLE ## TempExportData2 -- insert data into a global temp table
DECLARE
    @columnNames VARCHAR ( 8000 ), @columnConvert VARCHAR ( 8000 ), @tempSQL VARCHAR ( 8000 ) SELECT
    @tempSQL = LEFT ( @SQL, charindex( 'from', @SQL ) - 1 ) + ' into ##TempExportData ' + SUBSTRING (
    @SQL,
    charindex( 'from', @SQL ) - 1,
    len( @SQL )) EXEC ( @dbName + @tempSQL )
IF
    @@error > 0 BEGIN
SELECT
    0 AS ReturnValue – failure RETURN 
    END -- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT
    @columnNames = COALESCE ( @columnNames + ',', '' ) + column_name,
    @columnConvert = COALESCE ( @columnConvert + ',', '' ) + 'convert(nvarchar(4000),' + column_name +
CASE
    
    WHEN data_type IN ( 'datetime', 'smalldatetime' ) THEN
    ',121' 
    WHEN data_type IN ( 'numeric', 'decimal' ) THEN
    ',128' 
    WHEN data_type IN ( 'float', 'real', 'money', 'smallmoney' ) THEN
    ',2' 
    WHEN data_type IN ( 'datetime', 'smalldatetime' ) THEN
    ',120' ELSE '' 
    END + ') as ' + column_name 
FROM
    tempdb.INFORMATION_SCHEMA.Columns 
WHERE
    table_name = '##TempExportData' --execute select query to insert data and column names into new temp table
SELECT
    @SQL = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] 
    from ##TempExportData union all select ''' + replace( @columnNames, ',', ''', ''' ) + ''', ''1'') t order by [temp##SortID]' EXEC ( @SQL ) -- build full BCP query
SELECT
    @SQL = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -CRAW' -- execute BCP
    EXEC master..xp_cmdshell @SQL
IF
    @@error > 0 BEGIN
SELECT
    0 AS ReturnValue – failure RETURN 
    END DROP TABLE ## TempExportData DROP TABLE ## TempExportData2 SELECT
    1 AS ReturnValue–success 
GO
-- declare @sql varchar(6800),    @dbName varchar(100), @fullFileName varchar(100)
-- select    @dbName = 'CASNW_LNDB', @sql = 'select * from BDC_BLK_CR6_3220_W_D_R_Sf_P_day by TmStamp', @fullFileName = 'e:\test.xls'
-- exec    master..spExportData @dbName, @sql, @fullFileName

如果出现错误：

1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries

运行下列代码：

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

8 Use 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