T-SQL export Excel statement-to be tested

/* = ======= */

To import data from an Excel file to a SQL database, you can simply use the following statement:

/* ===================================================== ============================= */

Exec master .. xp_cmdshell 'bcp "Exec weberp. DBO. dailycash_excel @ payaccount = NULL, @ startdate = NULL, @ enddate = NULL, @ trackstation = NULL "queryout" C: \ authors.xls "-C-s" (local) "-U" sa "-P" password "'
-- If the table to be imported 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 from the SQL database to excel, And the Excel file already exists, and you have created a header based on the data to be received, you can simply use it:

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 use bcp to import Excel-like files. Note the case sensitivity:

-- Export tables

Exec master.. xp_mongoshell 'bcp database name. DBO. Table name out "C: test.xls"/C-/s "server name"/u "username"-P "password "'

-- Export Query Information

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 worksheet name of the Excel file. You must add $ to use it normally.

--*/

/* -- Data export Excel

Export The data in the table to excel. If the file does not exist, the file is automatically created. If the table does not exist, the table is automatically created.

Call example
Exporttoexcel @ tbname = 'test', @ Path = 'C: \ ', @ fname='test.xls'

--*/

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [exporttoexcel] ') and objectproperty (ID, n' isprocedure') = 1)

Drop procedure [DBO]. [exporttoexcel]

Go

Create proc exporttoexcel

@ Pac, -- Name of the table to be exported. Note that only the table name/view name is required.

@ Path nvarchar (1000), -- file storage directory

@ Fname nvarchar (250) = ''-- file name. The default value is 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 whether 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)}; DSN = tt; readonly = false'

+ '; Create_db =' + @ SQL + '; DBQ =' + @ SQL

Else

Set @ constr = 'provider = Microsoft. Jet. oledb.4.0; extended properties = "Excel 8.0; HDR = Yes'

+ '; Database =' + @ SQL + '"'

-- Connect to the database

Print @ constr

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

-- SQL statement used to create a table

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 when 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 policypes 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)

Print @ SQL

Exec @ err = sp_oamethod @ OBJ, 'execute ', @ 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

; Database = '+ @ 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 code

, @ SRC as error source, @ DESC as error description

Select @ SQL, @ constr, @ fdlist

Go

Create proc sqltoexcel
(
@ Path varchar (100), -- file storage path
@ Fname varchar (100), -- file name
@ Sheetname varchar (80), --- worksheet name
@ Sqlstr varchar (8000) -- query statement. If order by is used in the query statement, add Top 100 percent. Note that if the table/view is exported, use the following stored procedure.

)
As
Set nocount on

Declare @ SQL varchar (8000)
Declare @ OBJ int -- OLE object
Declare @ constr varchar (8000)
Declare @ err int
Declare @ out int
Declare @ fdlist varchar (8000)
Declare @ tbname sysname -- temporary table
Declare @ SRC nvarchar (200)
Declare @ DESC nvarchar (200)

Set @ tbname = '# TMP _' + convert (varchar (38), newid ())

Exec ('select * into ['+ @ tbname +'] From '+' ('+ @ sqlstr +') ')

Select @ fdlist =''

Set @ SQL = @ path + @ fname
Set @ constr = 'driver = {Microsoft Excel Driver (*. xls)}; DSN = '''; readonly = false'

+ '; Create_db = "' + @ SQL + '"; DBQ =' + @ SQL

-- Generate an Excel Column
Set @ SQL =''
Select @ SQL = @ SQL + ',' + '[' +. name + ']' + case when B. name like '% char' Then case when. length> 255 then 'memo' else' text ('+ Cast (. 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,
@ Fdlist = @ fdlist + ',' + '[' + A. Name + ']'
From tempdb .. syscolumns a join tempdb... policypes B on A. xtype = B. xusertype

Where B. Name not in ('image', 'uniqueidentifier', 'SQL _ variant', 'varbinary', 'binary ', 'timestamp ')

And ID in (select ID from tempdb .. sysobjects where name = @ tbname) order by colorder

If @ rowcount = 0 return

Set @ fdlist = substring (@ fdlist, 2,8000)

-- Connect to the database
Exec @ err = sp_oacreate 'ADODB. connection', @ OBJ out
If @ err <> 0 goto lberror
Exec @ err = sp_oamethod @ OBJ, 'open', null, @ constr
If @ err <> 0 goto lberror
-- Create a workbook
Select @ SQL = 'create table ['+ @ sheetname
+ '] (' + Substring (@ SQL, 2,8000) + ')'

Exec @ err = sp_oamethod @ OBJ, 'execute ', @ out, @ SQL -- @ SQL provides parameters for the excute Method

If @ err <> 0 goto lberror

Exec @ err = sp_oadestroy @ OBJ

-- Import data
Set @ SQL = 'openrowset (''microsoft. Jet. oledb.4.0 '', ''excel 8.0; HDR = Yes
; Database = '+ @ path + @ fname + ''', [' + @ sheetname + '$])'
-- Print @ SQL
Exec ('insert into '+ @ SQL +' ('+ @ fdlist +') select' + @ fdlist + 'from [' + @ tbname + ']')


Exec ('drop table ['+ @ tbname +'] ')
Return


Lberror:
Exec sp_oageterrorinfo 0, @ SRC out, @ DESC out

Lbexit:
Select cast (@ err as varbinary (4) as error code
, @ SRC as error source, @ DESC as error description
Select @ SQL, @ constr, @ fdlist


Go