SQL Server, Excel, and access data import

 

 

  Data conversion between SQL Server and access and Excel

Database Administrators familiar with SQL Server 2000 know that DTS can import and export data. In fact, we can also use  TransAct  -  SQL statement. In  -  In SQL statements, we mainly use the OpenDataSource function,     OpenRowSet  For more information about functions, see SQL online help. The following methods can be easily implemented:  SQL Server, access, Excel Data conversion, detailed descriptions are as follows:

I. Import and export data from SQL Server and access

General data import and export:

Use the DTS wizard to migrate your access data to SQL Server. You can use these steps:

1. On the Tools menu in SQL Server Enterprise Manager, select data transformation
2 services, and then select czdimport data (import data ).
3. In the choose a data source dialog box, select Microsoft Access  As  The source,  Then, type the file name of Your. mdb database (. MDB File Extension) or browse to find the file.
4. In the choose a destination (select target) dialog box,  Select Microsoft ole db prov ider  For  SQL Server, select the database server, and then click the required authentication method  .
5 In specify  Table In the copy or query dialog box, click Copy tables ).
6. In the select Source tables dialog box, click Select  All  (All selected ). Next, complete.

TransAct  -  Import and Export SQL statements:

  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
  --  Bytes -----------------------------------------------------------------------------------------------  
 2  . Import access to 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
  --  Bytes -----------------------------------------------------------------------------------------------  
  3  . Insert data in the SQL Server table to 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 name 1, column name 2)
  Select  Column name 1, column name 2  From  SQL table
Instance:
  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
  --  Bytes -----------------------------------------------------------------------------------------------  
  
Ii. Import and export data from SQL Server and Excel
  1  To 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 is provided by ole db for jet.ProgramQuery 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
  --  Bytes -----------------------------------------------------------------------------------------------  
  
  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
  --  Bytes -----------------------------------------------------------------------------------------------  
  
  3  Export the data queried in SQL Server into an Excel file
 --  ========================================================== ====================  
  T  -  SQLCode:
  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:  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:
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  '  "
  --  Bytes ----------------------------------------------------------------------------------------------  
  
  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  '  )
 --  Bytes -----------------------------------------------------------------------------------------------  
  
Summary: with the above statements, we can easily convert data in SQL Server, access, and Excel spreadsheet software, which provides us with great convenience!