Basic ways to import/export Excel from SQL Server

Excel|server
Basic ways to import/export Excel from SQL Server


/*=================== Import/Export Excel basic methods ===================*/

From the Excel file, import the data into the SQL database, very simply, using the following statement directly:

/*===================================================================*/
--If the table that accepts the data import already exists
INSERT INTO table SELECT * FROM
OPENROWSET (' MICROSOFT. JET. oledb.4.0 '
, ' Excel 5.0; Hdr=yes;database=c:test.xls ', sheet1$)

--If you import data and generate a table
SELECT * Into table from
OPENROWSET (' MICROSOFT. JET. oledb.4.0 '
, ' Excel 5.0; Hdr=yes;database=c:test.xls ', sheet1$)


/*===================================================================*/
-If you export data to Excel from an SQL database, if the Excel file already exists and you have created a header for the data you want to receive, you can simply use:
Insert into OPENROWSET (' MICROSOFT. JET. oledb.4.0 '
, ' Excel 5.0; Hdr=yes;database=c:test.xls ', sheet1$)
SELECT * FROM table


--If the Excel file does not exist, you can also use BCP to guide the files of class Excel, and note the case:
--Case of export table
EXEC Master.. xp_cmdshell ' bcp database name. dbo. Table name out "C:test.xls"/C-/s "server name"/u "username"-P ""

--Case of export query
EXEC Master.. xp_cmdshell ' bcp ' select au_fname, au_lname from pubs. Authors ORDER BY au_lname "Queryout" C:test.xls "/C-/s" server name/U "username"-P "password"


/*--Description:
C:test.xls is the Excel file name for import/export.
Sheet1$ is the name of the worksheet for the Excel file, which will normally be added with $ to work properly.
--*/
As mentioned above, the class Excel file is exported in bcp, which is essentially a text file,

--To export a real Excel file. Use the following method


/*--Data Export Excel

Export data from tables to Excel, including field names, files as real Excel files
, if the file does not exist, the file will be created automatically
, the table is created automatically if the table does not exist
Only export standard data types are supported based on commonality considerations
--Jiangjian 2003.10--*/

/*--Call Example

P_EXPORTTB @tbname = ' Area information ', @path = ' C: ', @fname = ' Aa.xls '
--*/
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_EXPORTTB] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_EXPORTTB]
Go

Create proc P_EXPORTTB
@tbname sysname--Name of the table to be exported
@path nvarchar (1000),--File storage directory
@fname nvarchar (250) = '--filename, default to table name
As
declare @err int, @src nvarchar (255), @desc nvarchar (255), @out int
declare @obj int, @constr nvarchar (1000), @sql varchar (8000), @fdlist varchar (8000)

--Parameter detection
If IsNull (@fname, ') = ' Set @fname = @tbname + '. xls '

--Check if the file already exists
If Right (@path, 1) <> ' Set @path = @path + '
CREATE TABLE #tb (a bit,b bit,c bit)
Set @sql = @path + @fname
INSERT into #tb exec master. Xp_fileexist @sql

--Database Creation statement
Set @sql = @path + @fname
if exists (select 1 from #tb where a=1)
Set @constr = ' Driver={microsoft Excel DRIVER (*.xls)};D sn= '; Readonly=false '
+'; create_db= "+ ';D atabase= ' + @sql + '" '


--Connecting to the database
exec @err =sp_oacreate ' adodb.connection ', @obj out
If @err <>0 goto Lberr

EXEC @err =sp_oamethod @obj, ' open ', NULL, @constr
If @err <>0 goto Lberr

/*--If you overwrite a table that already exists, add the following statement
--Delete table before creation/if present
Select @sql = ' drop table [' + @tbname + '] '
EXEC @err =sp_oamethod @obj, ' execute ', @out out, @sql
--*/

--Create a table SQL
Select @sql = ', @fdlist = '
Select @fdlist = @fdlist + ', [' +a.name+ '] '
, @sql = @sql + ', [' +a.name+ '] '
+case when b.name in (' char ', ' nchar ', ' varchar ', ' nvarchar ') then
' Text (' +cast (case if a.length>255 then 255 else a.length end as varchar) + ') '
When b.name in (' Tynyint ', ' int ', ' bigint ', ' tinyint ') then ' int '
When b.name in (' smalldatetime ', ' datetime ') Then ' datetime '
When B.name in ("Money", ' smallmoney ') Then ' money '
else B.name End
From syscolumns a LEFT join systypes B on A.xtype=b.xusertype
where B.name not in (' image ', ' text ', ' uniqueidentifier ', ' sql_variant ', ' ntext ', ' varbinary ', ' binary ', ' timestamp ')
and object_id (@tbname) =id
Select @sql = ' CREATE TABLE [' + @tbname
+ '] (' +substring (@sql, 2,8000) + ') '
, @fdlist =substring (@fdlist, 2,8000)
EXEC @err =sp_oamethod @obj, ' execute ', @out out, @sql
If @err <>0 goto Lberr

EXEC @err =sp_oadestroy @obj

--Import data
Set @sql = ' OpenRowset (' MICROSOFT. JET. oledb.4.0 ', ' Excel 5.0; Hdr=yes
;D atabase= ' + @path + @fname + ', [' + @tbname + ' $]] '

EXEC (' insert INTO ' + @sql + ' (' + @fdlist + ') Select ' + @fdlist + ' from ' + @tbname)

Return

Lberr:
EXEC sp_OAGetErrorInfo 0, @src out, @desc out
Lbexit:
Select CAST (@err as varbinary (4)) as error number
, @src as error source, @desc as error description
Select @sql, @constr, @fdlist
Go
--The above is the Guide table, the following is the Guide query statement.

/*--Data Export Excel

Export the data in the query to Excel, including the field name, and the file as a true Excel file
, if the file does not exist, the file will be created automatically
, the table is created automatically if the table does not exist
Only export standard data types are supported based on commonality considerations
--Jiangjian 2003.10--*/

/*--Call Example

P_EXPORTTB @sqlstr = ' SELECT * from area information '
, @path = ' C: ', @fname = ' Aa.xls ', @sheetname = ' Area information '
--*/
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_EXPORTTB] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_EXPORTTB]
Go

Create proc P_EXPORTTB
@sqlstr varchar (8000),--The query statement, if the order by is used in the query, add the top percent
@path nvarchar (1000),--File storage directory
@fname nvarchar (250),--filename
@sheetname varchar (250) = ' "-Name of the sheet to create, default to filename
As
declare @err int, @src nvarchar (255), @desc nvarchar (255), @out int
declare @obj int, @constr nvarchar (1000), @sql varchar (8000), @fdlist varchar (8000)

--Parameter detection
If IsNull (@fname, ') = ' Set @fname = ' Temp.xls '
If IsNull (@sheetname, ') = ' Set @sheetname =replace (@fname, '. ', ' # ')

--Check if the file already exists
If Right (@path, 1) <> ' Set @path = @path + '
CREATE TABLE #tb (a bit,b bit,c bit)
Set @sql = @path + @fname
INSERT into #tb exec master. Xp_fileexist @sql

--Database Creation statement
Set @sql = @path + @fname
if exists (select 1 from #tb where a=1)
Set @constr = ' Driver={microsoft Excel DRIVER (*.xls)};D sn= '; Readonly=false '
+'; create_db= "+ ';D atabase= ' + @sql + '" '

--Connecting to the database
exec @err =sp_oacreate ' adodb.connection ', @obj out
If @err <>0 goto Lberr

EXEC @err =sp_oamethod @obj, ' open ', NULL, @constr
If @err <>0 goto Lberr

--Create a table SQL
declare @tbname sysname
Set @tbname = ' # #tmp_ ' +convert (varchar), newid ())
Set @sql = ' select * into [' + @tbname + '] from (' + @sqlstr + ') a '
EXEC (@sql)

Select @sql = ', @fdlist = '
Select @fdlist = @fdlist + ', [' +a.name+ '] '
, @sql = @sql + ', [' +a.name+ '] '
+case when b.name in (' char ', ' nchar ', ' varchar ', ' nvarchar ') then
' Text (' +cast (case if a.length>255 then 255 else a.length end as varchar) + ') '
When b.name in (' Tynyint ', ' int ', ' bigint ', ' tinyint ') then ' int '
When b.name in (' smalldatetime ', ' datetime ') Then ' datetime '
When B.name in ("Money", ' smallmoney ') Then ' money '
else B.name End
From tempdb.. Syscolumns a LEFT join tempdb. Systypes B on A.xtype=b.xusertype
where B.name not in (' image ', ' text ', ' uniqueidentifier ', ' sql_variant ', ' ntext ', ' varbinary ', ' binary ', ' timestamp ')
and a.id= (select ID from tempdb.. sysobjects where name= @tbname)
Select @sql = ' CREATE TABLE [' + @sheetname
+ '] (' +substring (@sql, 2,8000) + ') '
, @fdlist =substring (@fdlist, 2,8000)

EXEC @err =sp_oamethod @obj, ' execute ', @out out, @sql
If @err <>0 goto Lberr

EXEC @err =sp_oadestroy @obj

--Import data
Set @sql = ' OpenRowset (' MICROSOFT. JET. oledb.4.0 ', ' Excel 5.0; Hdr=yes
;D atabase= ' + @path + @fname + ', [' + @sheetname + ' $]] '

EXEC (' insert INTO ' + @sql + ' (' + @fdlist + ') Select ' + @fdlist + ' from [' + @tbname + '] ')

Set @sql = ' drop table [' + @tbname + '] '
EXEC (@sql)
Return

Lberr:
EXEC sp_OAGetErrorInfo 0, @src out, @desc out
Lbexit:
Select CAST (@err as varbinary (4)) as error number
, @src as error source, @desc as error description
Select @sql, @constr, @fdlist
Go