SQL Server and Access, Excel Data Conversion
Database administrators who are familiar with SQL Server 2000 know that DTS can be exported for data import, in fact, we can also use Transact-SQL statements to import export operations. In the Transact-SQL statement, we mainly use the OpenDataSource function, the OpenRowSet function, for details about the function, please refer to the SQL online help. With the following methods, SQL Server, Access, Excel data conversion can be easily implemented, and the detailed description is as follows:
First, SQL Server and Access data import export
Conventional data import export:
Use the DTS wizard to migrate your Access data to SQL Server, you can use these steps:
1 On the Tools menu in the SQL Server Enterprise Manager, select Data Transformation
2Services (Data Conversion Service) and select CZDIMPORT DATA.
3 Select Microsoft Access As the Source in Choose A Data Source dialog box, then type your .mdb database (.mdb file extension) file name or by browseing.
4 In the Choose A Destination dialog box, select Microsoft Ole DB ProvIdeer for SQL Server, select the Database Server, and then click the necessary verification mode.
5 In Specify Table Copy, or Query dialog box, click Copy Tables.
6 In the SELECT SOURCE TABLES dialog box, click SELECT ALL. Next, complete.
Transact-SQL statement into imported export:
1. Query Access data in SQL Server:
- ================================================================================================================================================================== ======
SELECT *
From OpenDataSource ('Microsoft.jet.OleDb.4.0',
'Data Source = "C: /Db.mdb"; user ID = admin; password =') ... Table name
-------------------------------------------------- -----------------------------------------------
2. Import Access into SQL Server
- ================================================================================================================================================================== ====== Run in SQL Server:
SELECT *
INTO NewTable
From OpenDataSource ('Microsoft.jet.OleDb.4.0',
'Data Source = "C: /Db.mdb"; user ID = admin; password =') ... Table name
-------------------------------------------------- -----------------------------------------------
3. Insert the data in the SQL Server table into the Access table
- ================================================================================================================================================================== ======
Run in SQL Server:
INSERT INTO OpenDataSource ('Microsoft.jet.OleDb.4.0',
'Data Source = "C: /Db.mdb"; user ID = admin; password =') ... Table name
(Column 1, Column 2)
SELECT Column 1, Column Name 2 from SQL Table
Example:
INSERT INTO OpenRowset ('Microsoft.jet.OleDb.4.0',
'C: /db.mdb'; 'admin'; '', Test)
SELECT ID, NAME from test
INSERT INTO OpenRowSet ('Microsoft.jet.OleDb.4.0', 'c: /trade.mdb'; 'admin'; '', table name)
SELECT *
From Sqltablename
-------------------------------------------------- -----------------------------------------------
Second, SQL Server and Excel data import 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 an example of a query, which ques query Excel spreadsheet by an OLE DB provider for Jet.
SELECT *
From OpenDataSource ('Microsoft.jet.OleDb.4.0',
'Data source = "c: /finance/account.xls"; user ID = admin; password =; extended proties = excel 5.0') ... xactions
-------------------------------------------------- -----------------------------------------------
2. Import Excel's data into 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 $]
Example:
Select * INTO newTable
From OpenDataSource ('Microsoft.jet.OleDb.4.0',
'Data source = "c: /finance/account.xls"; user ID = admin; password =; extended proties = excel 5.0') ... xactions
-------------------------------------------------- -----------------------------------------------
3. Guide the data in SQL Server into an Excel file
- ================================================================================================================================================================== ====== T-SQL code:
EXEC MASTER..XP_CMDSHEC 'BCP library name .dbo. Table name Out C: /Temp.xls -c -q -s "servername" -u "sa" -p ""
Parameters: s is the SQL server name; u is user; P is password
Description: You can also export a variety of formats such as text files.
Example: EXEC MASTER..XP_CMDSHEC MASTER.. BCP SaleteStmp.dbo.cusaccount out c: /temp1.xls -c -q -s "pmserver" -u "sa" -p "sa" "
Exec master..xp_cmdshell 'bcp "SELECT AU_FNAME, AU_LNAME" queryout c: / authors.xls -c -sservername -
USA
-Ppassword '
Apply ADO to export EXCEL file code 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_cmdshell 'bcp" select col1, col2 from library name .dbo. Table name "Queryout E: /DT.XLS -C -SSERVERNAME -
USA
-Ppassword '"
-------------------------------------------------- -----------------------------------------------
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/INNVENTUR.XLS') ... [Filia1 $]
(Bestand, Product) VALUES (20, 'Test')
-------------------------------------------------- -----------------------------------------------
Summary: Using the above statement, we can easily convert the data in SQL Server, Access, and Excel spreadsheet software, which provides us with great convenience!
reference:
http://www.itrain.de/knowhow/sql/transfer/adhoc/
