How to import data from Excel to SqlServer

Today, I made a function to import data from excel to SqlServer. In fact, the function is not complicated, but it was just a day before it was successful! However, my success is a bit coincidental.

I believe that it is not complicated for many people to import data from excel to the database. Even I think so. However, through comparison, we can find that there is something worth learning.

Import data into the database. The idea is to read the data in excel to an object and save it. Then, traverse the object and submit the content to the database. However, the test result (10 minutes, 10000 data records), this speed should be relatively slow. Although this function may only be used once in a few months, the efficiency is greatly improved, so we decided to deal with it.

First of all: In this test result, the delay mainly lies in the reading of excel. Due to some problems that occurred when using the xlsreadwrite control of delphi, I encapsulated a set with ole, today, we naturally use our own encapsulated content. After all, we write it on our own, but the test results are very poor.

Secondly, although xlsreadwrite is problematic during use, it is completely usable for simple read Operations (there are charts in excel before, there will be exception information). After using this control, the efficiency improvement is obvious. The time for reading excel content can be shortened to several seconds, but it takes about 3 minutes to submit data to the database, of course, when transferring data, you need to process whether the record exists. If so ..., if no data exists, the insert statement is used. However, although the efficiency is improved by 70% in three minutes, it is still unsatisfactory. After searching online, we found a better method.

Finally, uploading the excel content to the database is a good method just mentioned-directly uploading the content in the excel through SQL statements. The efficiency is indeed good and the speed is increased to 50 seconds, however, the implementation process is still quite painful.

(1) If the table test does not exist, MERS Mers is the name of the sheet in Excel:

Select * into test from OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'Data source = c: \ test \ xltest.xls; extended properties = Excel 8.0')... [MERs $]

(2) If the test table exists

Insert into test (field1, field2) Select field1, field2 from OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = c: \ test \ xltest.xls; extended properties = Excel 8.0 ')... [MERs $]

However, the following problems may occur:

(1) The ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null)" reports an error. The provider does not provide any information about the error.

Server Management-check whether Microsoft. Jet. oledb.4.0 exists in the connection server. If yes, remove all check items.

(2) File Access failure, disk error or network failure

The excel file must be on the machine where the command is run and on the same machine as sqlserver. Of course, I think network ing is also acceptable, but I didn't try it.

(3) ole error. The function configuration is not enabled and you need to run:

-- Enable Ad Hoc Distributed Queries first:
Exec sp_configure 'show advanced options', 1
Reconfigure
Exec sp_configure 'ad Hoc Distributed Queries ', 1
Reconfigure

-- Disable ad hoc distributed queries after use:
Exec sp_configure 'ad hoc distributed queries ', 0
Reconfigure
Exec sp_configure 'show advanced options', 0
Reconfigure

(4) The access interface 'Microsoft. Jet. OLEDB.4.0 'is configured to run in single-threaded unit mode. Therefore, this access interface cannot be used for Distributed queries.

Solution: Success

The reason is: no driver for jet. oledb.4.0 is provided in 64 SQL Engine.
Solution: download an ACE. Oledb.12.0 for X64 driver and change the connection string Microsoft. jet. Oledb.4.0 to Microsoft. ACE. OLEDB.12.0.