SQL Server reading and importing Excel data

Time:2021-3-1

1、 Introduction

Using openrowset and opendatasource functions of SQL server, you can read Excel data just like querying data table. However, it’s not so easy to make these two functions work normally. If you don’t understand or configure them well, you will doubt your life by reporting errors all the way.

2、 Configuration

2.1 component installation

If you want to use openrowset and opendatasource functions to read Excel data, you must first install the accessdatabase engine component on the target SQL server host.

1) In other words: if the database to be operated is local, then I can install accessdatabase engine locally; if the database to be operated is installed on a remote server, then I need to install accessdatabase engine on the remote server.

2) It should be noted that to read Excel data, you only need to install access database engine, not office.

3) Install the accessdatabase engine version according to the number of operating system bits of the target SQL server host. Excel in this office is 2013 Version (. Xlsx). Microsoft Access database engine 2010 redistributable is required.Download address

2.2 service configuration

On the target SQL server host, win + R calls out to run, and then enters the services.msc Call out the service. Change the login identities of SQL Server (MSSqlServer) and SQL full text filter daemon launcher (MSSqlServer) to local system accounts.

2.3 parameter configuration

Open the query analyzer on the target SQL server and execute the following statement:

--1. Open the import function (view parameters: exec sp_ configure)
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--2. Allow in process ACE.OLEDB .12.0
exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
--3. Allow dynamic parameters
exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

2.3.1. Open the system interface corresponding to the import function

2.3.2 allow to use in process ACE.OLEDB 12.0 and the system interface corresponding to the allowed dynamic parameters:

3、 Testing

3.1 test statement

Open the query analyzer on the target SQL server and execute the following statement:

--1. Using query analyzer to query Excel
--Note 1: if you are connecting to the local database, e: EDI: annual rebate expense table. Xlsx refers to the local file path.
--Note 2: if you are connecting to a remote database, e: EDI: annual rebate expense table. Xlsx refers to the file path of the remote server. You can copy the file to the remote server by mapping.
SELECT * FROM OPENROWSET(' Microsoft.ACE.OLEDB . 12.0 ','excel 12.0'; HDR = yes; IMEX = 2; database = e: EDI - annual rebate expense table. Xlsx ', [sheet 1 $])
SELECT * FROM OPENROWSET(' Microsoft.ACE.OLEDB . 12.0 ','excel 12.0'; HDR = yes; IMEX = 2; database = e: EDI - annual rebate expense table. Xlsx ','select * from [sheet 1 $]')
SELECT * FROM OPENDATASOURCE(' Microsoft.ACE.OLEDB . 12.0 ','Extended properties ='excel 12.0; HDR = yes; IMEX = 2'; data source ='e: EDI: annual rebate expense table. Xlsx ''... [sheet 1 $]

1) Pay special attention to the file path, please pay special attention to note 1 and note 2 of the above code comments.

2) Openrowset and opendata source function, the function is the same, but the writing is a little different.

3) The account to connect to the database and the server role should be sysadmin.

3.2 test results

The results are as follows:

4、 Cases

After reading Excel data in query analysis, it means reading Excel data in code. Next, through a relatively simple VBA code, write the annual rebate expense table. Xlsx to the BRC of the remote database_ In the annual rebatefee table.

4.1 establishment of data table

CREATE TABLE [dbo].[BRC_AnnualRebateFee](
    [customer number] [char] (10) not null,
    [product number] [char] (20) not null,
    [annual rebate] [numerical] (16,2) null,
 CONSTRAINT [PK_BRC_AnnualRebateFee] PRIMARY KEY CLUSTERED 
(
    [customer number] ASC,
    [product No.] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

4.2 file copy

Copy the [annual rebate expense table. Xlsx] to the E:: EDI folder under the remote server.

4.3 VBA program

1) Open excel2013 locally and save it in xlsm format.

2) Click “file” – > “options” – > “custom ribbon” and check “development tools”.

3) Click “development tools” – > “insert” – > “command button (ActiveX control)”.

4) Click “design mode”, and then double-click the command button to enter the code page.

5) Click Tools > reference, check Microsoft ActiveX data objects 2.0, and then click OK.

6) The command button code is as follows:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'variable definition
    Dim cn As ADODB.Connection, cmd As New ADODB.Command
    Dim strCn As String, strSql As String
    
    'Database connection
    strCn = "Provider=SQLOLEDB;Data Source=erpserver;Initial Catalog=TEST;User Id=edi;Password=edi;"
    Set cn = New ADODB.Connection
    cn.Open strCn
    If cn.State <> adStateOpen Then
        cn.Close
        Msgbox "data connection failed. ", vbokonly," prompt“
        Exit Sub
    End If
    
    'assign initial value to the command object
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = ""
        .CommandTimeout = 0
    End With
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'read annual rebate expense table
    strSql = "TRUNCATE TABLE BRC_AnnualRebateFee"
    cmd.CommandText = strSql
    cmd.Execute
    
    Strpath = (E): (EDI). (xlsx)
    strSql = "INSERT INTO BRC_ Annual rebatefee (customer number, product number, annual rebate fee) select customer number, product number, annual rebate fee from openrowset (' Microsoft.ACE.OLEDB . 12.0 ','excel 12.0; HDR = yes; IMEX = 2; database = & strpath &','select * from [Sheet1 $] '"'
    'strSql = "INSERT INTO BRC_ Annualrebatefee (customer number, product number, annual rebate fee) select customer number, product number, annual rebate fee from opendatasource (' Microsoft.ACE.OLEDB . 12.0 ','excel 12.0; HDR = yes; IMEX = 2; database = & strpath & "'... [Sheet1 $]"'The second way of writing
    cmd.CommandText = strSql
    cmd.Execute
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'close the connection
    cn.Close
    Set cn = Nothing
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 

Recommended Today

Redis design and implementation 4: Dictionary Dict

In redis, the dictionary is the infrastructure. Redis database data, expiration time and hash type all take the dictionary as the underlying structure. Structure of dictionary Hashtable The implementation code of hash table is as follows:dict.h/dictht The dictionary of redis is implemented in the form of hash table. typedef struct dictht { //Hash table array, […]