Shulou(Shulou.com)05/31 Report--

This article mainly explains the "basic methods of importing and exporting Excel from SQL Server". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the basic methods of importing and exporting Excel from SQL Server".

Basic methods of importing / exporting Excel from SQL Server

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

To import data into SQL from an Excel file, it is simple to use the following statement:

/ * /

If the table that accepts the data import already exists

Insert into table select * from

OPENROWSET (MICROSOFT.JET.OLEDB.4.0

, Excel 5.0 cross HDRist Yeswitte Databasec flower test.xlsflore sheet1 $)

-- if you import data and generate tables

Select * into table from

OPENROWSET (MICROSOFT.JET.OLEDB.4.0

, Excel 5.0 cross HDRist Yeswitte Databasec flower test.xlsflore sheet1 $)

/ * /

-- if you export data from a SQL database to Excel, if the Excel file already exists and the header has been created based on the data to be received, you can simply use:

Insert into OPENROWSET (MICROSOFT.JET.OLEDB.4.0

, Excel 5.0 cross HDRist Yeswitte Databasec flower test.xlsflore sheet1 $)

Select * from table

-- if the Excel file does not exist, you can also use BCP to import an Excel-like file, paying attention to the case:

-- Export the table

EXEC master..xp_cmdshell bcp database name. Dbo. Table name out "c:test.xls" / c-/ S "Server name" / U "user name"-P "password"

-- Export the 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 "user name"-P "password"

/ *-description:

C:test.xls is the name of the imported / exported Excel file.

Sheet1 $is the worksheet name of the Excel file, which usually needs to be added with $before it can be used normally.

-- * /

As mentioned above, what is exported with BCP is an Excel-like file, which is essentially a text file

-- to export a real Excel file. Use the following methods

/ *-- data export EXCEL

Export the data in the table to Excel, including the field name, and the file is a real Excel file

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

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

Based on generality, only standard data types are supported.

-- Zou Jian 2003.10 Muhammad /

/ *-- 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, NIsProcedure) = 1)

Drop procedure [dbo]. [p_exporttb]

GO

Create proc p_exporttb

@ tbname sysname,-- the name of the table to export

@ path nvarchar (1000),-- File storage directory

@ fname nvarchar =-- File name, default is table name

As

Declare @ err int,@src nvarchar, @ desc nvarchar, @ 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 axi1)

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 @ err0 goto lberr

Exec @ err=sp_oamethod @ obj,open,null,@constr

If @ err0 goto lberr

/ *-if you overwrite an existing table, add the following statement

-Delete the table before creating it / if it exists

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

Exec @ err=sp_oamethod @ obj,execute,@out out,@sql

-- * /

-- create the SQL of the 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 > 255then 255else 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 @ err0 goto lberr

Exec @ err=sp_oadestroy @ obj

-- Import data

Set @ sql=openrowset (MICROSOFT.JET.OLEDB.4.0,Excel 5.0

; DATABASE=+@path+@fname+, [+ @ tbname+$])

Exec (insert into) select from)

Return

Lberr:

Exec sp_oageterrorinfo 0recoversrc 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 top is to guide the table, and the following is to guide the query statement.

/ *-- data export EXCEL

Export the data in the query to Excel, including the field name, and the file is a real Excel file

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

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

Based on generality, only standard data types are supported.

-- Zou Jian 2003.10 Muhammad /

/ *-- 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, 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 100percent

@ path nvarchar (1000),-- File storage directory

@ fname nvarchar,-- filename

@ sheetname varchar =-- the name of the worksheet to be created, which defaults to the file name

As

Declare @ err int,@src nvarchar, @ desc nvarchar, @ 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 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 axi1)

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 @ err0 goto lberr

Exec @ err=sp_oamethod @ obj,open,null,@constr

If @ err0 goto lberr

-- create the SQL of the table

Declare @ tbname sysname

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

Set @ sql=select * into [+ @ tbname+] from () 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 when a.length > 255then 255else 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)

Select @ sql=create table [+ @ sheetname

+] (+ substring (@ sql,2,8000) +)

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

Exec @ err=sp_oamethod @ obj,execute,@out out,@sql

If @ err0 goto lberr

Exec @ err=sp_oadestroy @ obj

-- Import data

Set @ sql=openrowset (MICROSOFT.JET.

Thank you for your reading, the above is the content of "basic methods of importing and exporting Excel from SQL Server". After the study of this article, I believe you have a deeper understanding of the basic methods of importing and exporting Excel from SQL Server, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.