Sqlserver_excel Data Import and Export

1. Open the Enterprise Manager, open the database for which you want to import data, right-click the table, and select all tasks --> import data. The DTS import/export wizard is displayed, and press next,
2. Select the data source Microsoft Excel 97-2000, select the xls file to be imported as the file name, and click Next,
3. Select the Microsoft ole db provider for SQL Server, select local for the server (for example, vvv for a local database), and use SQL Server Authentication, user name SA, the password is blank. Select the database (such as client) for which you want to import data, and click Next,
4. Select a query item to specify the data to be transmitted, and click Next,
5. By the Query Builder, you can add columns in the source table list to the selected column list on the right of the xls file to be imported, the order of adding columns must be the same as that defined by fields in the database. Otherwise, an error will occur. Follow the next step,
6. Select the order in which the data will be arranged. In this step, the columns selected are the columns followed by order by in the query statement,
7. If you want to import all data, select all rows and click Next,
8. The query statement generated based on the previous operation is displayed. After confirming that the statement is correct, follow the next step,
9. You will see the table/worksheet/Excel naming area list. In the target column, select the table to be imported and click Next,
10. Select Run now and click Next,
11. You will see the summary of the entire operation. Just click Finish.

Of course, there are multiple options for some of the above steps. You can select the appropriate options as needed. For example, if you are interested in programming, you can choose to save the DTS package and save it as a visual basic file in step 1. You can check the code in it to improve your programming level.

How to import/export Excel files from SQL Server
　　
/* = ======= */
　　
To import data from an Excel file to a SQL database, you can simply use the following statement:
　　
/* ===================================================== ===================================== */
-- If the table to be imported 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 from the SQL database to excel, And the Excel file already exists, and you have created a header based on the data to be received, you can simply use it:
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 import Excel-like files. Note the case sensitivity:
-- Export tables
Exec master.. xp_mongoshell 'bcp database name. DBO. Table name out "C: test.xls"/C-/s "server name"/u "username"-P "password "'
　　
-- Export Query Information
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 worksheet name of the Excel file. You must add $ to use it normally.
--*/
-- As mentioned above, BCP is used to export Excel-like files, whose essence is text files,
　　
-- To export a real Excel file, use the following method:
　　
/* -- Data export Excel
　　
Export the table data to excel, including the field name. The file is a real Excel file.
If the file does not exist, the file is automatically created.
If the table does not exist, the table is automatically created.
Only standard data types can be exported for the sake of universality.
-- Producer build 2003.10 --*/
　　
/* -- Call example
　　
P_exporttb @ tbname = 'region 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) = ''-- file name. The default value is 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 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 a = 1)
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 @ err <> 0 goto lberr
　　
Exec @ err = sp_oamethod @ OBJ, 'open', null, @ constr
If @ err <> 0 goto lberr
　　
/* -- If the existing table is overwritten, add the following statement.
-- Delete the table before creating the table./If the table exists
Select @ SQL = 'drop table ['+ @ tbname +']'
Exec @ err = sp_oamethod @ OBJ, 'execute ', @ out, @ SQL
--*/
　　
-- SQL statement used to create a 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> 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 policypes 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, @ 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
; Database = '+ @ 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 code
, @ SRC as error source, @ DESC as error description
Select @ SQL, @ constr, @ fdlist
Go
-- The above is the table for export, and the following is the query statement for export.