Efficient import of large data volumes using OPENROWSET and Microsoft.ACE.OLEDB

First, the environment used is: Java and SQL Server.

Recently, companies need to import large amounts of data. The original use of Apache POI, although the function can be implemented, but because the logic processing requires a lot of checks, processing speed is too slow, after using multithreading is not satisfactory. After searching the internet, we found OPENROWSET and OPENDATASOURCE, and found that using OPENROWSET, you can import Excel into the database very quickly. After the various checks, I can write SQL to achieve. The end result is that 6w data can be completed in 10 seconds. Of course, when the amount of data increases, the completion time does not increase significantly. The SQL that needs to be written is more efficient and on the other hand.

First, you may need to download a small program accessdatabaseengine_x64.exe.

Then you need to turn on configuration

Enable:

exec sp_configure ' show advanced options ', 1reconfigureexec sp_configure ' Ad Hoc distributed Queries ', 1reconfigure

Shut down:

You can then query the contents of the Excel file by OpenRowset. Of course, you can also change to select into and save to the database.

SELECT * from OPENROWSET (' microsoft.ace.oledb.12.0 ', ' Excel 12.0; Hdr=yes;database=e:\databack\copy of SD Expired contracts.xlsx ', [' Copy of SD Expired contracts$ ']

This is used only if you know the name of the sheet you need to import. And if you do not know, you need to use the following method, query out all the sheet name, and then by the user choose which to import.

EXEC sp_addlinkedserver ' excelsource ', ' ', ' microsoft.ace.oledb.12.0 ', ' E:\DataBack\Copy of SD Expired contracts.xlsx ', NULL, ' Excel 8.0 ' EXEC sp_addlinkedsrvlogin ' Excelsource ', ' false ' Goexecute sp_tables_ex ' Excelsource '

Here's my Java code:

This method the user obtains the sheet Name List.

public static list<string> getsheetnamelist (String Filepath,basedao basedao) {string excelsource = "Excelsource_" +stringutils.getuuidstring (); String Addsourcesql = "{Call sp_addlinkedserver (?," ', ' microsoft.ace.oledb.12.0 ',?, NULL, ' Excel 8.0 ')} "; SQLQuery query = Basedao.getsqlquery (addsourcesql); Query.setparameter (0, Excelsource); Query.setparameter (1, FilePath); Query.executeupdate (); String LOGINSOURCESQQL = "{Call sp_addlinkedsrvlogin (?, ' false ')}"; query = Basedao.getsqlquery (LOGINSOURCESQQL); Query.setparameter (0, Excelsource); Query.executeupdate (); String Sheetnamesql = "{call SP_TABLES_EX (?)}"; query = Basedao.getsqlquery (sheetnamesql); Query.setparameter (0, Excelsource); Query.setresulttransformer ( TRANSFORMERS.ALIAS_TO_ENTITY_MAP); list<map<string, object>> list = Query.list (); list<string> sheetlist = new arraylist<string> (); for (int i=0;i<list.size (); i++) {String SheetName = ( String) List.get (i). Get ("table_name"); if (Sheetname.endswith ("_xlnm#_filterdatabase")){}else{sheetlist.add (sheetname);}} return sheetlist;}

This method the user creates a temporary table that stores the contents of the Excel file. The field names for temporary tables are created from the Excel header.

public static string Uploadandcreatetable (String filepath,string Sheetname,basedao Basedao) {string importtablename = " Tbl_zz_ "+stringutils.getuuidstring (); String uploadfilesql = "Select IDENTITY (int, 1, 1) as%s,t.*,cast (NEWID () as VARCHAR ()) as%s to%s from OPENROWSET (' M Icrosoft. Ace. oledb.12.0 ', ' Excel 12.0; hdr=yes;database=%s ', [%s]) as T "; uploadfilesql = String.Format (Uploadfilesql,importvisitorutil.importindex, Importvisitorutil.importuuid,importtablename,filepath,sheetname); SQLQuery query = Basedao.getsqlquery (uploadfilesql); Query.executeupdate (); Replacespecialcharacter (BaseDao, Importtablename); Changecolumncollation (Basedao, importtablename); return importtablename;}

A problem was found in the code writing process, if the Excel header contains ":", in the writing of the SQL process will be followed by: Name This placeholder conflict, my colon replaced by a space.

private static void Replacespecialcharacter (Basedao basedao,string tableName) {String cha = ":"; String Cha_ = "%:%"; String sql = "SELECT column_name columnName from INFORMATION_SCHEMA. COLUMNS WHERE table_name =? and column_name like? "; SQLQuery query = basedao.getsqlquery (SQL), Query.setparameter (0, TableName); Query.setparameter (1, Cha_); Query.setresulttransformer (TRANSFORMERS.ALIAS_TO_ENTITY_MAP); list<map<string,object>> list = Query.list (); for (map<string,object> map:list) {String ColumnName = ( String) map.get ("ColumnName"); String newcolumnname = columnname.replace (Cha, ""); String tablecolumnname = String.Format ("%s.[%s]", TableName, ColumnName); String Changesql = "{call sp_rename (?,?, ' column ')}"; query = Basedao.getsqlquery (changesql); Query.setparameter (0, Tablecolumnname); Query.setparameter (1, newcolumnname); Query.executeupdate ();}}

Another problem is that the local database is installed with a collation that is inconsistent with the server, causing an error in the SQL runtime that was written and modifying the collation in advance.

private static void Changecolumncollation (Basedao basedao,string tableName) { String defaultcollation = "SQL_Latin1_General_CP1_CI_AS"; String dataType = "nvarchar"; String sql = "SELECT column_name as columnname,character_maximum_length as LENGTH from INFORMATION_SCHEMA. COLUMNS WHERE table_name =? <> collation_name? and data_type =? "; SQLQuery query = basedao.getsqlquery (SQL), Query.setparameter (0, TableName); Query.setparameter (1, defaultcollation); Query.setparameter (2, DataType); Query.setresulttransformer (TRANSFORMERS.ALIAS_TO_ENTITY_MAP); list<map<string,object>> list = Query.list (); for (map<string,object> map:list) {String ColumnName = ( String) map.get ("ColumnName"); Integer length = (integer) map.get ("Length"); String changesql = String.Format ("Alter TABLE [%s] alter COLUMN [%s]%s (%d) COLLATE%s", Tablename,columnname,datatype,len gth,defaultcollation); query = Basedao.getsqlquery (changesql); Query.executeupdate ();}} 

You can use the following SQL to query out all the columns in the table.

SELECT * from INFORMATION_SCHEMA. COLUMNS

The foreground can let users now have columns for real tables in each column in Excel. After the correspondence is organized, add fields to the staging table, modify the data, and so on as needed, and finally insert the data or modify the data using the Insert Select.

SQL syntax errors can be caused by the non-specification of column names when writing SQL. You need to add the brackets "[]" before and after the column name or table name. Jaya use String.Format (), more convenient.

Efficient import of large data volumes using OPENROWSET and Microsoft.ACE.OLEDB