Import data in execl into the database in batches and execl into the database

Import data in execl into the database in batches and execl into the database

This article uses the NPOI plug-in to read the data in the execl file and load the data to the DataTable in the memory.

1 /// <summary> 2 /// convert Excel to able 3 /// </summary> 4 /// <param name = "extension"> </param> 5 /// <param name = "stream"> </param> 6 /// <returns> </returns> 7 public static System. data. dataTable GetExcelDataTable (string extension, System. IO. stream stream) {8 NPOI. SS. userModel. IWorkbook workBook; 9 if (extension = ". xls ") 10 workBook = new NPOI. HSSF. userModel. HSSFWorkbook (stream); 11 else if (exte Nsion = ". xlsx") 12 workBook = new NPOI. XSSF. UserModel. XSSFWorkbook (stream); 13 else14 throw new Exception ("File Format error! "); 15 var sheet = workBook. getSheetAt (0); 16 var row = sheet. getRow (0); 17 System. data. dataTable dt = new System. data. dataTable (sheet. sheetName); 18 foreach (var I in row) {19 var name = I. stringCellValue; 20 if (string. isNullOrEmpty (name) 21 break; 22 dt. columns. add (name); 23} 24 dt. primaryKey = new DataColumn [1] {dt. columns [0]}; 25 var rowCount = sheet. lastRowNum + 1; 26 for (var I = 1; I <rowCount; + + I) {27 row = sheet. getRow (I); 28 string [] cells = new string [dt. columns. count]; 29 for (var j = 0; j <cells. length; ++ j) {30 var cell = row. getCell (j); 31 if (cell! = Null) 32 cells [j] = cell. toString (); 33} 34 if (dt. rows. find (cells [0]) = null) {35 dt. rows. add (cells); 36} 37} 38 return dt; 39}View Code

In the above method, set the data in the first column of the execl table to the primary key of the DataTable, and use the dt. Rows. Find () method to remove duplicate items, mainly to avoid data merging later.

After the DataTable is loaded into the memory, at the business layer, modify the first row of data in the execl file (that is, the column name in the DataTable file) to the column names of the temporary tables created later.

1 public static async Task BatchImport (System. data. dataTable dt) {2 dt. columns ["item id"]. columnName = "GOODSID"; 3 dt. columns ["Product Name"]. columnName = "GOODSNAME"; 4 dt. columns ["Product main Chart"]. columnName = "GOODSMAINIMG"; 5 dt. columns ["product details page link address"]. columnName = "GOODSDETAIL"; 6 dt. columns ["item level 1 category"]. columnName = "GOODSLEVEL"; 7 dt. columns ["promotion link"]. columnName = "SHORTLINK2"; 8 dt. columns ["commodity price (unit: yuan)"]. columnName = "GOODSPRICE"; 9 dt. columns ["Monthly product sales"]. columnName = "GOODSMONTHSALE"; 10 dt. columns ["Income Ratio (%)"]. columnName = "GOODSINCOME"; 11 dt. columns ["Commission"]. columnName = "GOODSCOMMISSION"; 12 dt. columns ["seller name"]. columnName = "SELLER"; 13 dt. columns ["seller id"]. columnName = "SELLERID"; 14 dt. columns ["store name"]. columnName = "STORENAME"; 15 dt. columns ["platform type"]. columnName = "PLATFORMTYPE"; 16 dt. columns ["coupon id"]. columnName = "COUPONID"; 17 dt. columns ["Total coupons"]. columnName = "COUPONTOTAL"; 18 dt. columns ["coupon remaining amount"]. columnName = "COUPONSURPLUS"; 19 dt. columns ["coupon denomination"]. columnName = "COUPONCONTENT"; 20 dt. columns ["coupon Start Time"]. columnName = "CONPONSTRATETIME"; 21 dt. columns ["coupon End Time"]. columnName = "COUPONENDTIME"; 22 dt. columns ["coupon link"]. columnName = "coupondomainlink"; 23 dt. columns ["Product coupon promotion link"]. columnName = "COUPONLINK"; 24 using (var con = await mssql. tbk. getConnection () {// The method to be executed will be followed by 25 await mssql. tbk. createTmepTable (con); // create a temporary table 26 await mssql. tbk. batchImport ("# TMD", dt, con); // import the data in the DataTable to the temporary table (# TMD) 27 await mssql. tbk. combine (con); 28 await mssql. tbk. dropTempTable (con); 29} 30}View Code

Create a temporary table. The name of the temporary table must start with #. The structure must be the same as that of the execl file. There must be no more or less fields. Otherwise, some data is lost when importing data.

1 /// <summary> 2 /// create temporary table 3 /// </summary> 4 /// <param name = "con"> </param> 5/ // <returns> </returns> 6 public static async Task CreateTmepTable (sqlClient. sqlConnection <System. data. sqlClient. sqlConnection, System. data. sqlClient. sqlParameter> con) {7 await con. executeNonQueryAsync ("create table # TMD ([GOODSID] bigint not null primary key, [GOODSNAME] [varchar] (250) not null, [GOODSMAINIMG] [varchar] (1024) not null, [GOODSDETAIL] [varchar] (1024) not null, [GOODSLEVEL] [varchar] (50) not null, [SHORTLINK2] [varchar] (1024) not null, [GOODSPRICE] [varchar] (100) not null, [GOODSMONTHSALE] [varchar] (100) not null, [GOODSINCOME] [varchar] (100) not null, [GOODSCOMMISSION] [varchar] (100) not null, [SELLER] [varchar] (100) not null, [SELLERID] [varchar] (100) not null, [STORENAME] [varchar] (100) not null, [PLATFORMTYPE] [varchar] (50) not null, [COUPONID] [varchar] (100) not null, [COUPONTOTAL] [varchar] (100) not null, [COUPONSURPLUS] [varchar] (100) not null, [COUPONCONTENT] [varchar] (100) not null, [CONPONSTRATETIME] [varchar] (100) not null, [COUPONENDTIME] [varchar] (100) not null, [COUPONSHORTLINK] [varchar] (1024) not null, [COUPONLINK] [varchar] (1024) not null) "); 8}View Code

After a temporary table is created, import the DataTable data to the temporary table.

1 /// <summary> 2 // import execl data to the temporary table in batches 3 /// </summary> 4 /// <param name = "tableName"> </ param> 5 /// <param name = "dt"> </param> 6 /// <returns> </returns> 7 public static async Task BatchImport (string tableName, system. data. dataTable dt, sqlClient. sqlConnection <System. data. sqlClient. sqlConnection, System. data. sqlClient. sqlParameter> con) {8 await con. insertBulkCopyAsync (tableName, dt, System. data. sqlClient. sqlBulkCopyOptions. default); 9}View Code

The focus is on writing the data in the temporary table to the database master table (the structure of the master table and the temporary table can be the same? Please refer to the code

1 /// <summary> 2 // merge temporary table data to database master table 3 /// </summary> 4 /// <param name = "con"> </param> 5 // <returns> </returns> 6 public static async Task Combine (sqlClient. sqlConnection <System. data. sqlClient. sqlConnection, System. data. sqlClient. sqlParameter> con) {7 await con. executeNonQueryAsync ("merge into tmd ts USING # TMD t ON ts. GOODSID = t. goodsid when matched and ts. GOODSID = t. goodsid then update set ts. GOODSNAME = t. GOODSNAME, ts. GOODSMAINIMG = t. GOODSMAINIMG, ts. GOODSDETAIL = t. GOODSDETAIL, ts. GOODSLEVEL = t. GOODSLEVEL, ts. SHORTLINK2 = t. SHORTLINK2, ts. GOODSPRICE = t. GOODSPRICE, ts. GOODSMONTHSALE = t. GOODSMONTHSALE, ts. GOODSINCOME = t. GOODSINCOME, ts. GOODSCOMMISSION = t. GOODSCOMMISSION, ts. SELLER = t. SELLER, ts. SELLERID = t. SELLERID, ts. STORENAME = t. STORENAME, ts. PLATFORMTYPE = t. PLATFORMTYPE, ts. COUPONID = t. COUPONID, ts. COUPONTOTAL = t. COUPONTOTAL, ts. COUPONSURPLUS = t. COUPONSURPLUS, ts. COUPONCONTENT = t. COUPONCONTENT, ts. CONPONSTRATETIME = t. CONPONSTRATETIME, ts. COUPONENDTIME = t. COUPONENDTIME, ts. coupondomainlink = t. coupondomainlink, ts. COUPONLINK = t. couponlink when not matched then insert values (t. GOODSID, t. GOODSNAME, t. GOODSMAINIMG, t. GOODSDETAIL, t. GOODSLEVEL, t. SHORTLINK2, t. GOODSPRICE, t. GOODSMONTHSALE, t. GOODSINCOME, t. GOODSCOMMISSION, t. SELLER, t. SELLERID, t. STORENAME, t. PLATFORMTYPE, t. COUPONID, t. COUPONTOTAL, t. COUPONSURPLUS, t. COUPONCONTENT, t. CONPONSTRATETIME, t. COUPONENDTIME, t. coupondomainlink, t. COUPONLINK, 0); "); 8}View Code

The above method uses merge into and using, the combination of Merge and using is used for statistics and analysis of BI data. For example, if the child table does not have data, you must delete the corresponding data in the parent table. the method is to run a job and then use the cursor, Table value function, temporary table, and so on to obtain data cyclically and update the parent table. This is a waste of efficiency, and Merge will be used in this case.

Merge Syntax:

Merge IntoPrimary table T
Using# Temporary table D on T. Joined field = D. Joined Field
WhenMatched
Then updateSet T. Field = D. Field ...........
WhenNot matched -- cannot be updated when it is not matched (no matching is successful, but cannot be updated)
Then insert(D field ...) Values (D. Field );

For more information, please search

Delete the temporary table after the data is merged (the temporary table will be cleared when the database connection is disconnected. This step can also be saved)

1 /// <summary> 2 /// Delete temporary table 3 /// </summary> 4 /// <param name = "con"> </param> 5/ // <returns> </returns> 6 public static async Task DropTempTable (sqlClient. sqlConnection <System. data. sqlClient. sqlConnection, System. data. sqlClient. sqlParameter> con) {7 await con. executeNonQueryAsync ("drop table # TAOBAO_SELECTED"); 8}View Code

Writing here basically solves the problem of batch data import. If you have a better way to import tens of thousands of lines of execl files into the database, please leave a message and provide guidance.