SQL import Excel General stored procedures

SQL import Excel General stored procedures
/*
-- The following two statements must be executed before the stored procedure is created. Otherwise, the stored procedure cannot be created or an error occurs during the call.
Set ansi_warnings on -- returns a warning.
Go
Set ansi_nulls on -- specifies the SQL-92 of these operators to follow when equal (=) and not equal (<>) Comparison operators are used for null values.
Go
-- Import Excel Data to SQL database
-- Copyright: Departure letwego.cn
-- Call an instance
Exec spexceloutin
@ Stroptions = 'in ',
@ Strwhere = 'where room number> 0', -- import/export query conditions (including the where keyword)
@ Strexcelpath = 'd: \ test.xls ', -- absolute path of Excel
@ Strexcelsheetname = 'sheet2', -- the name of the worksheet to be imported in Excel, such as sheet2. Note that do not add $
@ Strexcelfiled = 'unit No., unit name, Room No., building area, floor, floor name, floor no., owner ', -- Excel worksheet Field

@ Strsqltablename = 'textouttin ', -- Name of the SQL Database Import table
@ Strsqlfiled = 'unit No., unit name, Room No., building area, floor, floor name, floor no., owner no. '-- SQL table Field
*/

Create procedure spexceloutin

@ stroptions varchar ( 200 ) = null,
@ strwhere nvarchar ( 4000 ) = null, -- Import / export query conditions (including where keywords)

@ strexcelpath nvarchar ( 1000 ) = null, -- Excel absolute path
@ strexcelsheetname nvarchar ( 50 ) = null, -- the name of the worksheet to be imported in Excel, such as sheet2. Note, do not add $
@ strexcelfiled nvarchar ( 1000 ) = null, -- fields in an Excel worksheet

@ Strsqltablename nvarchar (100)=Null,--SQL Database Import/Export table name
@ Strsqlfiled nvarchar (1000)=Null--SQL table fields

As

Declare @ strsql nvarchar (4000)

If object_id ('Tempdb .. # temp') Is not null drop table # temp

/*Import*/
If @ stroptions='In'And not @ strexcelpath is null begin

Set @ strexcelpath= '''Microsoft. Jet. oledb.4.0'',''Data Source ="' +@ Strexcelpath+ '"; User id = admin; Password =; extended properties = Excel 5.0'''

-- Store data in a temporary table (1)
Set @ strsql =
' Select ' + @ Strexcelfiled +
' Into # temp
From
OpenDataSource ( ' + @ Strexcelpath + ' ) ' + @ Strexcelsheetname + ' $ '
+ @ Strwhere

--Print (@ strsql)
Exec (@ strsql)

-- Import data from a temporary table to a SQL database table (2. Perform more processing in two steps)
Set @ strsql = ' Insert ' + @ Strsqltablename + ' ( ' + @ Strsqlfiled + ' ) Select * from # temp '
-- Print (@ strsql)
Exec (@ strsql)

--Select*From ## temp
Drop table # temp

End

/*Export*/
If @ stroptions='Out'And not @ strexcelpath is null begin

set @ strexcelpath = ''
-- convert to an Excel file similar to (not a real Excel file, but a text file), which is fixed here,
-- exec master .. xp_cmdshell ' BCP" select * From WY _ Fuzhou branch. DBO. texceloutin "queryout c: \ test.xls-C-s" (local) "-U" sa "-P" 123 " '
end
go

copyright

If the title is not marked with or other words, the title is original to the author. You are welcome to reprint the title. The copyright belongs to the author and the blog Park.

prepared by Wen jingliang

Article Source: http://wenjl520.cnblogs.com/or http://www.cnblogs.com/