Copy codeThe Code is as follows:
-- Configure permissions
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'ad Hoc Distributed Queries ', 1;
GO
RECONFIGURE;
GO
SQL SERVER and EXCEL Data Import and Export
1. query Excel Data in SQL SERVER:
-- ===================================================== ====================
Copy codeThe Code is as follows:
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 an example of a query. It queries an Excel spreadsheet through the ole db provider for Jet.
SELECT *
FROM OpenDataSource ('Microsoft. Jet. OLEDB.4.0 ',
'Data Source = "c: \ Finance \ account.xls"; User ID = Admin; Password =; Extended properties = Excel 5.0 ')... xactions
Bytes -------------------------------------------------------------------------------------------------
2. Import Excel Data to SQL server:
-- ===================================================== ====================
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows:
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
Bytes -------------------------------------------------------------------------------------------------
3. Export the data queried in SQL SERVER into an Excel file
-- ===================================================== ====================
T-SQL code:
Copy codeThe Code is as follows:
EXEC master.. xp_mongoshell 'bcp database name. dbo. Table name out c: \ Temp.xls-c-q-S "servername"-U "sa"-P ""'
Parameter: S indicates the SQL server name, U indicates the user, and P indicates the password.
Note: You can also export text files and other formats.
Instance:
Copy codeThe Code is as follows:
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'
Use ADO to export the EXCEL file code in VB6:
Copy codeThe Code is as follows:
Dim cn As New ADODB. Connection
Cn. open "Driver = {SQL Server}; Server = WEBSVR; DataBase = WebMis; UID = sa; WD = 123 ;"
Cn.exe cute "master .. xp_mongoshell 'bcp" SELECT col1, col2 FROM database name. dbo. Table Name "queryout E: \ DT.xls-c-Sservername-Usa-ppassword '"
Bytes ------------------------------------------------------------------------------------------------
4. insert data to Excel in SQL SERVER:
-- ===================================================== ====================
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows:
INSERT
OPENDATASOURCE ('Microsoft. JET. OLEDB.4.0 ',
'Extended Properties = Excel 8.0; Data source = C: \ training \ inventur.xls ')... [Filiale1 $]
(Bestand, produkt) VALUES (20, 'test ')