Basic methods of importing / exporting Excel from SQL Server
/ * =================== Basic method for import / export EXCEL ============================ * /
From the Excel file, import data into the SQL database, very simple, directly with the following statement:
/ * ================================================================================================================================================================ =================== * / - If the table accepts data imported, there is an Insert INTO table Select * from OpenRowSet ('Microsoft.jet.OleDb.4.0', ' Excel 5.0; HDR = YES; Database = C: Test.xls', Sheet1 $)
- If imported data and generates a table Select * INTO table from OpenrowSet ('Microsoft.jet.OleDb.xls),' Excel 5.0; HDR = YES; Database = C: Test.xls', Sheet1 $)
/ * ================================================================================================================================================================ =================== * / - If the data is exported from the SQL database, if the Excel file already exists, it has been created in accordance with the data to receive. Head, you can use: Insert Into OpenRowSet ('Microsoft.jet.OleDb.4.0', 'Excel 5.0; HDR = YES; Database = C: Test.xls', Sheet1 $) Select * From
- If the Excel file does not exist, you can also use BCP to guide the class Excel file, pay attention to case: - Export tables Exec master..xp_cmdshell 'bcp database name .dbo. Table name OUT "C: Test. XLS "/ c - / s" server name "/ U" user name "-p" password "'
- Export query case EXEC MASTER..XP_CMDSHELL 'BCP "SELECT AU_FNAME, AU_LNAME" queryout "c: test.xls" / c - / s "server name" / u "user name" -P "Password" '/ * - Description: C: Test.xls For import / export Excel file name. SHEET1 $ is the work table name of the Excel file, usually add $ to use it. - * / - What has already said, use BCP to export the class Excel file, which is a text file,
- To export a real Excel file. Use the following method
/ * - Data exports the data in the Excel export table to Excel, contains the field name, and the file is a real Excel file. If the file does not exist, the file will be automatically created. If the table does not exist, the automatic creation table is based on versatility. Also supports export standard data types - Zou Jian 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, - To export the table name @Path nVarchar (1000), - File storage directory @fname nvarchar (250) = '' - file name, default is a table name asDeclare @err int, @ Src nvarchar (255), @ desc nvarchar (255), @ out intdeclare @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 @ FnameInsert Into #TB exec master..xp_fileexist @SQL
- Database creation statement set @ SQL = @ path @ fnameif exists (select 1 from #tb where a = 1) set @ Constr = 'driver = {Microsoft Excel Driver (* .xls)}; DSN =' ''; Readonly = false ' '; Create_DB = " '; Database =' @ SQL '" "
- Connect database EXEC @ Err = sp_oacreate 'adodb.connection', @ obj outif @err <> 0 goto lberrexec @ err = sp_oamethod @ Obj, 'open', null, @ constrif @err <> 0 goto lberr
/ * - If you override the already existing table, add the following statement - to delete the table before creating / if you exist, select @ SQL = 'drop table [' @ TBNAME ']' Exec @ Err = sp_oamethod @obj , 'Execute', @ out out, @ SQL - * /
- Create a table sqlselect @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 "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 Endfrom Syscolumns a Left Join System Syscolumns A LEFT JOIN SYSTYPES B on A. xiin = B.XUSERTYPEWHERE B.NAME NOT IN ('Image', 'Text', ' uniqueidentifier ',' sql_variant ',' ntext ',' varbinary ',' binary ',' timestamp ') and object_id (@tbname) = idselect @ sql =' create table [ ' @ tbname '] ( ' substring ( @ SQL, 2,8000) ')', @ fdlist = Substring (@ fdlist, 2,8000) exec @ err = sp_oamethod @ Obj, 'execute', @ out out, @ SQLIF @err <> 0 goto lberr
Exec @ Err = sp_oadestroy @obj
- Imported 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 OUTLBEXIT: SELECT CAST (@err as varbinary (4)) AS error number, @ src as error source, @ DESC AS error Description SELECT @ SQL, @ Constr, @ fdlistgo- - The above is the guide, below is the query statement.
/ * - Data Export Excel Export Data into Excel, contains the field name, file is a real Excel file, if the file does not exist, will automatically create files, if the table does not exist, will automatically create a form based on versatility, Also supports export standard data types - Zou Jian 2003.10 - * /
/ * - Call example
P_exporttb @ Sqlstr = 'SELECT * FROM area, @ Path =' c: ', @ fname =' aa.xls', @ sheetname = 'area data' - * / 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 P_EXPORTTB @ SQLSTR VARCHAR (8000), - query statement, if you use Order by in the query statement, please add TOP 100 Percent @ path nvarchar (1000), - File Storage Directory @fname nvarchar (250), - - File name @Sheetname varchar (250) = '' - To create the work table name, default is file name as declare @err int, @ src nvarchar (255), @ desc nvarchar (255), @ out inteclare @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 if the file already exists if right (@ path, 1) <> 'set @ path = @ Path ' 'Create Table #TB (A bit, b bit, c bit) set @ SQL = @ Path @ FnameInsert Into #TB EXEC MASTER..XP_FILEEXIST @ SQL - Database Create Statement Set @ SQL = @ Path @ FNameiF EXISTS (SELECT 1 from #tb where a = 1) Set @ Constr = 'Driver = {Microsoft Excel Driver (* .xls) }; DSN = '' '; readonly = false' '; create_db = " '; Database = ' @ SQL '" "
- Connect database EXEC @ Err = sp_oacreate 'adodb.connection', @ Obj outif @err <> 0 goto lberr
Exec @ Err = SP_OAMETHOD @ Obj, 'Open', NULL, @10 @err <> 0 goto lberr
- Create a table sqldeclare @tbname = '## tmp _' convert (varchar (38), newid ()) set @ SQL = 'SELECT * INTO [' @ TBNAME '] from (' @ SQLSTR ') 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> 255 THEN 255 Else A.Length End As Varchar) ')' When B.Name in ('Tynyint', 'Int') Then 'int' when b.name in ('smalldatetime') Then 'datetime' When B.Name In (' Money ',' Smallmoney ') Then' Money 'Else B.Name Endfrom Tempdb..syscolumns a left a.xtype = B.XUSERTYPEWHERE 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 @ ire = sp_oamethod @ Obj,' Execute ', @ Out out, @ SQLIF @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 OUTLBEXIT: SELECT CAST (@err as varbinary (4)) AS error number, @ src AS error source, @ DESC AS error Description Select @ SQL, @ Constr, @ fdlistgo
