SQL Server and Excel Data Import and Export

1. query Excel Data in SQL Server: -- ===================================================== ====================  Select * From   OpenDataSource ( 'Microsoft. Jet. oledb.4.0' , 'Data source = "C: \ book1.xls"; user id = admin; Password =; extended properties = Excel 5.0' )... [Sheet1 $] The following is a query example, which is provided by ole db for jet. Program Query an Excel worksheet. Select * From   OpenDataSource ( 'Microsoft. Jet. oledb.4.0' ,'Data source = "C: \ finance \ account.xls"; user id = admin; Password =; extended properties = Excel 5.0' )... Xactions events ----------------------------------------------------------------------------------------------- -- 2. Import Excel Data to SQL Server: -- ===================================================== ====================  Select * Into Newtable From   OpenDataSource ( 'Microsoft. Jet. oledb.4.0' , 'Data source = "C: \ book1.xls"; user id = admin; Password =; extended properties = Excel 5.0' )... [Sheet1 $] instance: Select *Into Newtable From   OpenDataSource ( 'Microsoft. Jet. oledb.4.0' , 'Data source = "C: \ finance \ account.xls"; user id = admin; Password =; extended properties = Excel 5.0' )... Xactions events ----------------------------------------------------------------------------------------------- -- 3. Export the data queried in SQL Server into an Excel file -- ===================================================== ==================== T-SQL Code : Exec Master .. xp_mongoshell 'Bcp database name. DBO. Table name out c: \ temp.xls-C-Q-s "servername"-U "sa"-P ""' Parameter: S is the SQL server name, U is the user, p is the password Description: You can also export text files and other format instances: Exec Master .. xp_mongoshell 'Bcp saletesttmp. DBO. cusaccount out c: \ temp1.xls-C-Q-s "pmserver"-U "sa"-P "sa "'  Exec Master .. xp_mongoshell 'Bcp "select au_fname, au_lname from pubs .. authors order by au_lname" queryout c: \ authors.xls-C-sservername-USA-ppassword' Export the Excel file code by using ADO in VB6: dim CN As   New ADODB. Connection CN. Open "Driver = { SQL Server}; server = websvr; Database = Webmis; uid = sa; WD = 123; "CN.Execute "Master .. xp_mongoshell 'Bcp "select col1, col2 from database name. DBO. Table Name" queryout E: \ dt.xls-C-sservername-USA-ppassword' "Begin "---------------------------------------------------------------------------------------------- -- 4. insert data to excel in SQL Server: -- ===================================================== ==================== Insert Into   OpenDataSource ( 'Microsoft. Jet. oledb.4.0' , 'Data source = "C: \ temp.xls"; user id = admin; Password =; extended properties = Excel 5.0' )... Table1 (A1, A2, A3) Values (1, 2, 3) T-SQL code: insert Into   OpenDataSource ( 'Microsoft. Jet. oledb.4.0' , 'Extended properties = Excel 8.0; Data Source = c: \ training \ inventur.xls' )... [Filiale1 $] (Bestand, Produkt) Values (20, 'Test' )