SQL Import statements that export Excel data _mssql

--From the Excel file, import the data into the SQL database, very simply, use 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.
--*/

--Here's how to export a real Excel file:
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
/*--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 (please keep this information for reference)--*/
/*--Call Example
P_EXPORTTB @tbname = ' Area information ', @path = ' C:\ ', @fname = ' Aa.xls '
--*/
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= "' + @sql + '";D bq= ' + @sql
Else
Set @constr = ' provider=microsoft.jet.oledb.4.0; Extended properties= "Excel 8.0; Hdr=yes '
+ ';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 like '%char '
Then case when a.length>255 then ' memo '
Else ' text (' +cast (a.length as varchar) + ') ' End
When b.name like '%int ' or b.name= ' bit ' then ' int '
When b.name like '%datetime ' Then ' datetime '
When b.name like '%money ' then ' money '
When b.name like '%text ' Then ' Memo '
else B.name End
From syscolumns a LEFT join systypes B on A.xtype=b.xusertype
where B.name not in (' image ', ' uniqueidentifier ', ' sql_variant ', ' 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 8.0; Hdr=yes;imex=1
;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

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
/*--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
If the table does not exist, the table is created automatically
Only export standard data types are supported based on commonality considerations
--Jiangjian 2003.10 (please keep this information for reference)--*/
/*--Call Example
P_EXPORTTB @sqlstr = ' SELECT * from area information '
, @path = ' C:\ ', @fname = ' Aa.xls ', @sheetname = ' Area information '
--*/
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= "' + @sql + '";D bq= ' + @sql
Else
Set @constr = ' provider=microsoft.jet.oledb.4.0; Extended properties= "Excel 8.0; Hdr=yes '
+ ';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 like '%char '
Then case when a.length>255 then ' memo '
Else ' text (' +cast (a.length as varchar) + ') ' End
When b.name like '%int ' or b.name= ' bit ' then ' int '
When b.name like '%datetime ' Then ' datetime '
When b.name like '%money ' then ' money '
When b.name like '%text ' Then ' Memo '
else B.name End
From tempdb.. Syscolumns a LEFT join tempdb. Systypes B on A.xtype=b.xusertype
where B.name not in (' image ', ' uniqueidentifier ', ' sql_variant ', ' varbinary ', ' binary ', ' timestamp ')
and a.id= (select ID from tempdb.. sysobjects where name= @tbname)
If @ @rowcount =0 return
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 8.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 declare @tbname sysname--the name of the table to be exported, note that only the table name/view name
declare @path nvarchar (1000)--File storage directory
declare @fname nvarchar (250)--filename, default to table name
Set @tbname = ' Salaryreports '
Set @path = ' E:\ '
Set @fname = ' Salaryreportsxls '
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 '
--drop Table #tb
--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
--select * from #tb
--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= "' + @sql + '";D bq= ' + @sql
Else
Set @constr = ' provider=microsoft.jet.oledb.4.0; Extended properties= "Excel 5.0; Hdr=yes '
+ ';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
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