Also talk about SQL Server table and Excel, access data export

Recently, I saw a lot of friends asking SQL Server table and Excel and access data exchange questions on the Forum. The question is very simple and someone wrote it very early. Article I have discussed this question, but I have read some articles and few people have understood it. They are all very general formats. It is estimated that beginners will be confused by the answers, let alone new things.

For this reason, I will explain in detail the process of mutual guidance. Of course, I will not talk about the general process of using wizard mutual guidance in the SQL Server Manager, the following are the direct use of T-SQL statements to achieve.

1. Export SQL Server as Excel:

To use T-SQL statements to export directly to the Excel worksheet, you have to borrow an extended stored procedure from the SQL Server Manager: xp_cmdshell, this process is used to execute a given command string in the form of an operating system command line interpreter and return any output in the form of text lines." The following is a definition example:

Exec Master .. xp_mongoshell ' BCP database name. DBO. Table name out c: \ book3.xls-C-Q-s "servername"-U "sa"-P "" '


-- Parameter: s indicates the SQL server name; U indicates the user name; P indicates the password.



-- Note: in fact, the exported format in this process is essentially the text format. If you do not believe it, change it in the exported Excel table and save it.

The actual example and description are as follows:
/**/ /*If you want to export the entire table to excel*/

Exec Master .. xp_mongoshell ' BCP northwind. DBO. Orders out c: \ book1.xls-C-Q-s "(local)"-U "sa"-P "" '


-- Note that the northwind. DBO. orders in the statement is the database name + owner + Table name.



-- Export the key using the "out"



-- -----------------------------------------

/**/ /*If you want to use a query to export some fields to excel*/

Exec Master .. xp_mongoshell ' BCP "select orderid, cutomerid, freight from northwind... orders order by orderid" queryout c: \ book2.xls-C-s "(local)"-U "sa"-P "" '


-- A query statement is added after BCP and enclosed in double quotation marks.



-- Use the keyword "queryout" to query

2. Import SQL Server tables in Excel:

In SQL Server, an opendatesource function is defined to reference the infrequently accessed OLE DB data sources. Our data import operations are built on this function.



First look at an example of T-SQL help, the description is as follows:
-- 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

If you directly reference this example for query, It is passable. The key lies in the two points in the statement that need to be modified. One point is at the data source, which is the actual storage location of the Excel table in double quotation marks. You need to modify the actual full path of the Excel table you want to query; the second is the final... xactions, in fact, represents some of the actions to be performed. Here we will talk about it. modify it to the worksheet name (with a $) in the Excel table surrounded by brackets, for example, [sheet1 $]. Of course, you can also change Excel 5.0 to excel 8.0, because 5.0 is an old version.

The following is an example:


/**/ /*1. Data inserted into the Excel database table is saved to the current SQL database table. (assume that the C disk has the Excel table book2.xls. there is a worksheet sheet1 in book2.xls, and sheet1 has two column IDs and fnames. At the same time, the SQL database also has a table ):*/

Insert   Into Test Select ID, fname

From   OpenDataSource ( ' Microsoft. Jet. oledb.4.0 ' , ' Data Source = "C: \ book2.xls"; user id = admin; Password =; extended properties = Excel 8.0 ' ) [ Sheet1 $ ]

-- If select * is used, the column order will be disordered, the data content will also be disordered, and the insertion will fail. Therefore, the column name is specified.

-----------------------

/**/ /*2. Insert data in the Excel table to the SQL database and create an SQL table (the definition and content of Excel are the same as above ):*/

Select   Convert ( Int , ID) As ID, fname Into Test7

From   OpenDataSource ( ' Microsoft. Jet. oledb.4.0 ' , ' Data Source = "C: \ book2.xls"; user id = admin; Password =; extended properties = Excel 8.0 ' ) [ Sheet1 $ ]

-- Convert is the best option in the Select column to convert the display type. Otherwise, the data type is not as expected.

The data import between SQL Server and Excel is complete. Do you understand? Access is basically the same as Excel, but the extended properties Declaration must be removed.

======================================

Delphi example (exported as an Excel table ):
Adoq1.close;

Adoq1. SQL. Clear;

Sqltrs: =

' Insert into ctable (name1, sex, ID) ' +

' Select ' +

' Name, gender, ID card number ' +

' From [Excel 8.0; database = '   + Xlsname +   ' ]. [Sheet1 $] ' ;

Adoq1.parameters. Clear;

Adoq1.paramcheck: = False ;

Adoq1. SQL. Text: = Sqltrs;

Adoq1.execsql;


// Note that there cannot be spaces between the left and right sides of the Chinese field name

Please indicate the source of the reference:

Cnblogs (Bonny. Wong) 2005.1.29