Import and Export Excel files in SQL Server

How to import/export Excel files from SQL Server

/* ============= Basic method for importing/exporting Excel =================== */

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

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

-- Assume that 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 $)

-- Assume that the data is imported and the table is generated.

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 document 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 document does not exist, BCP can be used to import Excel-like documents. 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 document name for import/export.

Sheet1 $ is the worksheet name of the Excel document. You must add $ to use the worksheet normally.

--*/

-- As mentioned above, the Excel-like documents exported with BCP are essentially text documents,

-- To export a real Excel file, use the following method:

/* -- Data export Excel

Export The data in the table to excel, including the field name. The document is a real Excel document.

If the document does not exist, the document will be automatically created

If the table does not exist, the table is automatically created.

Only standard data types can be exported for the sake of universality.

-- Producer build 2003.10 --*/

/* -- Call example

P_exporttb @ tbname = region data, @ Path = C:, @ fname=aa.xls

--*/

If exists (select * From DBO. sysobjects where id = object_id (N [DBO]. [p_exporttb]) and objectproperty (ID, nisprocedure) = 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) = -- document name, table name by default

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 document 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 =; readonly = false

+; Create_db = "+; database = + @ SQL +"

-- Connect 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 the existing table is overwritten, add the following statement.

-- Delete the table before creating the table.

Select @ SQL = drop table [+ @ tbname +]

Exec @ err = sp_oamethod @ OBJ, execute, @ out, @ SQL

--*/

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

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 5.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

-- The above is the table for export, and the following is the query statement for export.

/* -- Data export Excel

Export the queried data to excel, including the field name. The document is a real Excel document.

If the document does not exist, the document will be automatically created

If the table does not exist, the table is automatically created.

Only standard data types can be exported for the sake of universality.

-- Producer build 2003.10 --*/

/* -- Call example

P_exporttb @ sqlstr = select * From region data

, @ Path = C:, @ fname112aa.xls, @ sheetname = region information

--*/

If exists (select * From DBO. sysobjects where id = object_id (N [DBO]. [p_exporttb]) and objectproperty (ID, nisprocedure) = 1)

Drop procedure [DBO]. [p_exporttb]

Go

Create proc p_exporttb

@ Sqlstr varchar (8000), -- query statement. If order by is used in the query statement, add Top 100 percent.

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

@ Fname nvarchar (250), -- document name

@ Sheetname varchar (250) = -- Name of the worksheet to be created. The default value is document 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 @fname1_temp.xls

If isnull (@ sheetname,) = set @ sheetname = Replace (@ fname ,.,#)

-- Check whether the document 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 =; readonly = false

+; Create_db = "+; database = + @ SQL +"

-- Connect 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

-- SQL statement used to create a table

Declare @ tbname sysname

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

Set @ SQL = select * into [+ @ tbname +] From (+ @ sqlstr +)

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 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 tempdb .. syscolumns a left join tempdb .. policypes 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, @ 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

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

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

Select @ SQL, @ constr, @ fdlist

Go