SQL Server and Excel data cross-guide

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, then everybody learned?