Basic methods for importing / exporting Excel from SQL Server / * =========================================================================================================================================================================== ======== * / From the Excel file, import data into the SQL database, very simple, directly with the following statement: / * ================ ============================================================================================================================================================================================================= * / - If the table has an Insert INTO table Select * from OpenRowSet ('Microsoft.jet.OleDb.xls),' 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 the data is exported from the SQL database, if the Excel file already exists, but also in accordance with the data to be received, 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 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 "/ U" User Name "-p" Password "'- 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 "User Name" -p "password" '/ * - Description: c: Test.xls is imported / exported Excel file name. SHEET1 $ Is the work name name of the Excel file, usually add $ to use it. - * / - It has been said that the BCP is exported is the class Excel file, which is a text file, - To export real Excel file. Use the following method / * - Data export the data in the Excel export table to Excel, contain the field name, file as a real Excel file, if the file does not exist, will automatically create files, if the table does not exist, will Automatic creation table is based on versatility, only support for export standard data types - Zou Jian 2003.10 - * / / * - call sample 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 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_FIL EEXIST @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 lberr EXEC @ Err =
Sp_oAmethod @ Obj, 'Open', NULL, @ Constrif @err <> 0 goto lberr / * - If you override the existing table, add the following statement - Before you create, remove the table / if you exist, select @SQL = 'drop table [' @ TBNAME ']' exec @ err = sp_oamethod @ Obj, 'execute', @ out out, @ SQL - * / - create table's SQLSELECT @SQL = ', @ fdlist =' 'select @ fdlist = @ fdlist ', [' a.name '], @ SQL = @ SQL ', [' a.name '] Case When B.Name in (' char ',' nchar ' , 'VARCHAR', 'NVARCHAR') TEN 'TEXT (' CAST (Case When A.Length> 255 THEN 255 ELSE A.LENGTH End As Varchar) ')' When B.Name in ('Tynyint', 'Int ',' Bigint ',' Int 'When B.Name in (' SmallDatetime ') Then' DateTime 'When B.Name in (' Money ',' SmallMoney ') Then' Money ' else b.name endFROM syscolumns a left join systypes b on a.xtype = 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 L Berr 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 OUTLBEXIT: SELECT CAST (@err as varbinary (4)) AS error number, @ src AS error source, @ DESC AS error Description Select @ SQL, @ Constr, @ fdlistgo - above is a list The following is the. / * - Data Export Excel Export Introduction to Excel, including the field name, file is a real Excel file, if the file does not exist, if the file does not exist, if the table does not exist, will Automatic creation table based on versatility, only supports export standard data types - Zou Jian 2003.10 - * / / * - Call sample p_exporttb @ SQLSTR = 'SELECT * FROM area information', @ Path = 'c:', @ FNAME = 'aa.xls', @ sheetname = 'region' - * / 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 @ SQLSTR VARCHAR (8000), - Query statement, 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) = '' - The work table name to create, the default is the file name as Declare @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 =' 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 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 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 ',' Bigint ',' Tinyint ') Then' Int 'When B. Name in ('smalldatetime', 'datetime') Then 'DateTime' When B.Name in ('Money', 'Smallmoney') Then 'Money' Else B.Name endfrom tempdb..syscolumns a left join tempdb..systypes B ON A.XTYPE = B.XUSERTYPEWHERE B.NAME N OT in ('Image', 'Text', 'UniqueIdentifier', 'SQL_VARIANT', 'NTEXT', 'VARBINARY', 'Binary', 'TimeStamp') And A.ID =
