Data Import between SQL Server and Excel and access

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:

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

Note: -- after Password =;, add the HDR = no option, indicating that 1st rows are data. The default value is yes, indicating that 1st rows are field names.

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, some actions are to be performed on behalf of the table. Here we will talk about the change 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:

/** // * Sheet has a worksheet sheet1, and sheet1 has two columns of ID and fname, while SQL database also has a table test ):*/

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 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 the display type in the Select column using convert. Otherwise, the data type is not as expected.

Special attention !!! : 1. If the exeltable is imported from the data warehouse, for example, the exelfile mytest.xls worksheet imported from the jobstable is [sheet1 $] in the preceding example of jobs by default, it should be changed to [jobs $].

2) if "server: Message 7399, level 16, status 1, line 1" appears

The ole db Provider 'Microsoft. Jet. oledb.4.0 'reports an error. The provider does not provide any information about the error ."

The above error is because your execl file is opened. Close the Excel file and try again.

3) the first row of the imported Exel table must have a column name for each column, as shown in

ID name age

1 tomclus 35

...

If no column name is available

1 tomclus 35

...

Errors may occur.

If not all columns are specified in the preceding example, or select *, errors will occur, such as incomplete columns or data type deployment.

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 No. '+

'From [Excel 8.0; database = '+ xlsname +']. [sheet1 $] ';

Adoq1.parameters. Clear;

Adoq1.paramcheck: = false;

Adoq1. SQL. Text: = sqltrs;

Adoq1.execsql;

// Chinese fields cannot contain spaces on either side

Appendix: (the following content has not been put into practice)

Familiar with SQL Server 2000, database administrators know that DTS can import and export data. In fact, we can also use Transact-SQL statements to import and export data. In a Transact-SQL statement, we mainly use the OpenDataSource function and OpenRowset function. For more information about functions, see SQL online help. You can easily convert SQL Server, access, and Excel data by using the following methods:

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 (data conversion service), and then select czdimport data (import data ).

○ 3 select Microsoft Access as the source in the choose a data source dialog box, and then type 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 database server, and click the required authentication method.

○ 5. In the specify table copy (specifying table copy) or query dialog box, click Copy tables ).

○ 6 in the select Source tables dialog box, click Select All ). Next, complete.

Use a Transact-SQL statement to import and export data:

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 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

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

Ii. Import and export data from SQL Server and Excel

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. 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

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

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 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.exe cute "master .. xp_mongoshell 'bcp" select col1, col2 from database 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

OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Extended properties = Excel 8.0; Data Source = c: \ training \ inventur.xls ')... [filiale1 $]

(Bestand, Produkt) values (20, 'test ')

Summary: with the above statements, we can easily convert data in SQL Server, access, and Excel spreadsheet software, which provides us with great convenience!

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 format exported 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 sentence is the database name + owner + Table Name

-- Directly export the "out" parameter

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

/** // * 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 to BCP and enclosed in double quotation marks.

-- Use the keyword "queryout" to query

Supplement:

1. How to convert the number into scientific notation when importing data in Excel into SQL:

For example:

Excel Data: 8630890

After the SQL statement is imported, it becomes 8.63089e + 006.

Note: In SQL, the Data Type of this field is nvchar.

You can refer to the following method to convert the imported data, but the data inaccuracy caused by precision problems cannot be processed. In addition, if there is a leading 0 in Excel data, as the imported data is a float number, leading 0 is lost.

Declare @ a float

Set @ A = 8.63089e + 006

Select cast (@ A as decimal (38 ))

-- Result: 8630890

Use your own instance to give you a piece of code:

File1 = request ("file ")

SQL = "insert into student (studyid, yourname, yourpass, yourclass, courseid) Select cast (student ID as decimal (18), name, cast (password as decimal (18 )), class, cast (course number as decimal (18) from OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "& file1 &"; user id = admin; Password =; extended properties = Excel 8.0 ')... [sheet1 $]"

Conn. Execute SQL

2. How to get the Excel table name (in ASP ):

Set APP = server. Createobject ("Excel. application ")

App. workbooks. Open ("" & file1 &"")

For I = 1 to app. worksheets. Count

Response. Write app. worksheets (I). Name

Next

During the test, I do not know the reason, and the time is not good. Further solutions are needed!