根据单元格位置从excel批量插入到sql以选择字段 [英] Bulk insert from excel to sql for selective fields on the basis of cell location

查看：105
本文介绍了根据单元格位置从excel批量插入到sql以选择字段的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个SSIS程序包，在这里我必须从excel工作表中选择一些值，然后将它们插入SQL Server数据库表中，这是通过Execute sql任务来完成的。



这些步骤是：


  1. 从映射表，单元格中选择所有记录位置是动态的，因此将其保留在sql表中（大约有3000个单元格-我们必须从Excel中的选择性字段中而非全部选择值）

示例：




  1. 在每个记录上遍历 Foreach


  2. 创建带有单元格名称和工作表名称的查询



    示例：从[GenDet $ F3：F3]中选择*


  3. 执行查询以从Excel工作表中获取该单元格的值


示例：





它正在起作用-但问题是时间它正在采取。对于3000个字段，整个过程需要50分钟才能处理一个Excel文件。我必须在不到一分钟的时间内完成此操作。



请让我知道实现此目标的最佳方法。



谢谢！

解决方案

正如我在评论中提到的，我认为编写ac＃脚本来读取excel单元格中的数据并将它们分组到列表或DataTable中，然后执行一次大容量插入



C＃应用程序/脚本任务



需要的程序集



首先，您必须导入Excel Interop程序集：

 使用Microsoft.Office.Interop.Excel; 
使用System.Data.SqlClient;



将列标题字母转换为索引



现在，您应该定义以下将excel列字母转换为索引的函数：

  private int ParseColHeaderToIndex（string colAdress）
 {
 int []位=新int [colAdress.Length]; 
 for（int i = 0; i  {
 digits [i] = Convert.ToInt32（colAdress [i]）-64; 
} 
 int mul = 1; 
 int res = 0; 
 for（int pos = digits.Length-1; pos> = 0; pos--）
 {
 res + = digits [pos] * mul; 
 mul * = 26; 
} 
 return res; 
}



SQL批量插入函数



以下功能是对SQL执行批量插入操作

  public void InsertToSQLUsingSQLBulk（System.Data.DataTable dt，string connectionstring，字符串表名）
 {
 
 
试试
 {
使用（var bulkCopy = new SqlBulkCopy（connectionstring，SqlBulkCopyOptions.KeepIdentity））
 {
 
 foreach（dt.Columns中的DataColumn col）
 {
 bulkCopy.ColumnMappings.Add（col.ColumnName，col.ColumnName）; 
} 
 
 bulkCopy.BulkCopyTimeout = 600; 
 bulkCopy.DestinationTableName =表名； 
 bulkCopy.WriteToServer（dt）; 
} 
 
} 
的捕获量（异常除外）
 {
前抛出； 
} 
}



从excel读取到目标数据表



以下函数将excel路径和range DataTable作为参数，并返回具有目标结构（Id，AttributeKey，AttributeValue）

$的DataTable。 b
$ b 

  public System.Data.DataTable ReadFromExcel（System.Data.DataTable dtRanges，string strPath）
 {
 
字符串num = 0123456789; 
 
 //声明结果数据表
 System.Data.DataTable destination = new System.Data.DataTable（）; 
 destination.Columns.Add（ Id）; 
 destination.Columns.Add（ AttributeKey）; 
 destination.Columns.Add（ AttributeValue）; 
 
 //声明互操作对象
 Microsoft.Office.Interop.Excel.Application m_XlApp; 
 m_XlApp =新的Microsoft.Office.Interop.Excel.Application（）; 
 m_XlApp.Visible = false; 
 m_XlApp.DisplayAlerts = false; 
 
工作簿xlWbs = null; 
 xlWbs = m_XlApp.Workbooks.Open（strPath，Type.Missing，Type.Missing，
 Type.Missing，'，Type.Missing，Type.Missing，
 Type.Missing， Type.Missing，Type.Missing，
 Type.Missing，Type.Missing，Type.Missing，
 Type.Missing，Type.Missing）; 
 
 xlWbs.DoNotPromptForConvert = true; 
 xlWbs.CheckCompatibility = false; 
 xlWbs.Application.DisplayAlerts = false; 
 
 //在工作表上循环
 foreach（xlWbs.Worksheets中的工作表xlWks）{
 
字符串Name = xlWks.Name; 
 
 //关联与当前工作表相关的行
 
 foreach（dtRanges.AsEnumerable（）中的DataRowdrRow。Where（x => x [ Sheet_Name]。ToString （）==名称））
 {
 
字符串表= drRow [ Sheet_Name]。ToString（）; 
字符串范围= drRow [ Location_Value]。ToString（）; 
字符串字段= drRow [ Field_Name]。ToString（）; 
字符串ID = drRow [ Id]。ToString（）; 
 string rangeAlpha = range.Split（’：’）[0]; 
 int rowidx = 0; 
 int colidx = 0; 
 
 
 
 foreach（以字符为单位的字符数）{
 rangeAlpha = rangeAlpha.Replace（chr，'\0'）; 
} 
 
 rowidx = Int32.Parse（range.Split（’：’）[0] .Replace（rangeAlpha，））; 
 colidx = ParseColHeaderToIndex（rangeAlpha）; 
 
 
 DataRow dr = destination.NewRow（）; 
 
 if（xlWks.Cells [rowidx，colidx]！= null&&（xxWks.Cells [rowidx，colidx]作为Range）.Value2！= null）
 {
 
 dr [ AttributeValue] =（string）（xlWks.Cells [rowidx，colidx]作为Range）.Value2; 
} 
 else 
 {
 dr [ AttributeValue] =; 
} 
 
 
 
 dr [ AttributeKey] = drRow [ Field_Name]。ToString（）; 
 dr [ Id] = drRow [ Id]。ToString（）; 
 
 destination.Rows.Add（dr）; 
} 
 
} 
 
 xlWbs.Close（false，Type.Missing，Type.Missing）; 
 m_XlApp.Quit（）; 
 System.Runtime.InteropServices.Marshal.ReleaseComObject（xlWbs）; 
 System.Runtime.InteropServices.Marshal.ReleaseComObject（m_XlApp）; 
 
 
返回目的地； 
 
}



主程序



  public void Main（）{
 
 //初始化范围表
 System.Data.DataTable range = new System.Data.DataTable （）; 
 range.Columns.Add（ Id）; 
 range.Columns.Add（ Field_Name）; 
 range.Columns.Add（ Location_Value）; 
 range.Columns.Add（ Sheet_Name）; 
 
 //使用SQLDataAdapter添加行或从数据库中读取行
 
 
 //请注意，目标表必须存在于数据库中，且数据表的列相同
 $ b System.Data.DataTable目标= ReadFromExcel（范围， C：\\1.xlsx， dbo.destination）; 
 
 InsertToSQLUsingSQLBulk（目标，在此处传递SQL Server目标连接字符串）； 
 
 
 
}





更新1-提高性能



您可以通过将所有工作表内容放入二维数组中，然后循环遍历而不是数组来提高方法性能

  Excel.Range targetCells = xlWks.UsedRange; 
 object [，] allValues =（object [，]）targetCells.Cells.Value; 
 
 ... 
 
 
 if（targetCells.Cells [rowidx，colidx]！= null）
 {
 
 dr [ AttributeValue] =（string）（targetCells.Cells [rowidx，colidx]作为Range）.Value2; 
} 
 else 
 {
 dr [ AttributeValue] =; 
}

参考




I have a SSIS package where I have to select some values from an excel sheet and insert them into a SQL Server database table, i am doing it through Execute sql task.

These are the steps:

  1. Select all records from mapping table, cell location is dynamic so keeping it in sql table (around 3000 cells are there - we have to pick value from selective fields in Excel and not all)

Example:

  1. Iterate through Foreach on each record

  2. Create a query with cell name and Sheet name

    Example: Select * from [GenDet$F3:F3]

  3. Execute the query to get that cell's value from Excel sheet

Example:

  1. Insert the values into the sql database table

It is working - but the problem is the time which it is taking. For 3000 fields, this whole process is taking 50 minutes to process one Excel file. I have to do this in less than a minute.

Please let me know the best approach to achieve this.

Thank you!

解决方案

As i mentioned in the comments, I think that writing a c# script that read the data from excel cells and group them into a list or DataTable then perform a Bulk insert once will be more performant

C# application/script task

Needed assemblies

First you have to import Excel Interop assembly:

using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;

Convert column header letter to index

Now you should define the following function that convert the excel column alphabet to index:

private int ParseColHeaderToIndex(string colAdress)
{
    int[] digits = new int[colAdress.Length];
    for (int i = 0; i < colAdress.Length; i++)
    {
        digits[i] = Convert.ToInt32(colAdress[i]) - 64;
    }
    int mul = 1;
    int res = 0;
    for (int pos = digits.Length - 1; pos >= 0; pos--)
    {
        res += digits[pos] * mul;
        mul *= 26;
    }
    return res;
}

SQL bulk insert function

The following function is to perform bulk insert operation into SQL

public void InsertToSQLUsingSQLBulk(System.Data.DataTable dt, string connectionstring, string Tablename)
{


    try
    {
        using (var bulkCopy = new SqlBulkCopy(connectionstring, SqlBulkCopyOptions.KeepIdentity))
        {

            foreach (DataColumn col in dt.Columns)
            {
                bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
            }

            bulkCopy.BulkCopyTimeout = 600;
            bulkCopy.DestinationTableName = Tablename;
            bulkCopy.WriteToServer(dt);
        }

    }
    catch (Exception ex)
    {
        throw ex;
    }
}

Reading from excel into destination DataTable

The following function takes the excel path and the ranges DataTable as parameter and return a DataTable having the destination structure (Id, AttributeKey, AttributeValue)

public System.Data.DataTable ReadFromExcel(System.Data.DataTable dtRanges,string strPath)
{

    string num = "0123456789";

    //Declare result datatable  
    System.Data.DataTable destination = new System.Data.DataTable();
    destination.Columns.Add("Id");
    destination.Columns.Add("AttributeKey");
    destination.Columns.Add("AttributeValue");

    //Decalre Interop Objects
     Microsoft.Office.Interop.Excel.Application m_XlApp;
     m_XlApp = new Microsoft.Office.Interop.Excel.Application();
     m_XlApp.Visible = false;
     m_XlApp.DisplayAlerts = false;

     Workbook xlWbs = null;
     xlWbs = m_XlApp.Workbooks.Open(strPath, Type.Missing, Type.Missing, 
                                   Type.Missing, "'", Type.Missing, Type.Missing, 
                                   Type.Missing, Type.Missing, Type.Missing, 
                                   Type.Missing, Type.Missing, Type.Missing, 
                                   Type.Missing, Type.Missing);

    xlWbs.DoNotPromptForConvert = true;
    xlWbs.CheckCompatibility = false;
    xlWbs.Application.DisplayAlerts = false;

    //Loop over worksheets
    foreach (Worksheet xlWks in xlWbs.Worksheets) {

        string Name = xlWks.Name;

        //Assing rows relevant to the current sheet

        foreach (DataRow drRow in dtRanges.AsEnumerable().Where(x => x["Sheet_Name"].ToString() == Name))
        {

            string sheet = drRow["Sheet_Name"].ToString();
            string range = drRow["Location_Value"].ToString();
            string field = drRow["Field_Name"].ToString();
            string id = drRow["Id"].ToString();
            string rangeAlpha = range.Split(':')[0];
            int rowidx = 0;
            int colidx = 0;



            foreach (char chr in num) { 
                rangeAlpha = rangeAlpha.Replace(chr, '\0');
            }

            rowidx = Int32.Parse(range.Split(':')[0].Replace(rangeAlpha, ""));
            colidx = ParseColHeaderToIndex(rangeAlpha);


            DataRow dr = destination.NewRow();

            if (xlWks.Cells[rowidx, colidx] != null && (xlWks.Cells[rowidx, colidx] as Range).Value2 != null)
            {

                dr["AttributeValue"] = (string)(xlWks.Cells[rowidx, colidx] as Range).Value2;
            }
            else
            {
                dr["AttributeValue"] = "";
            }



            dr["AttributeKey"] = drRow["Field_Name"].ToString();
            dr["Id"] = drRow["Id"].ToString();

            destination.Rows.Add(dr);
        }

    }

    xlWbs.Close(false, Type.Missing, Type.Missing);
    m_XlApp.Quit();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWbs);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_XlApp);


    return destination;

}

Main program

public void Main(){

    //Initialize ranges table
    System.Data.DataTable ranges = new System.Data.DataTable();
    ranges.Columns.Add("Id");
    ranges.Columns.Add("Field_Name");
    ranges.Columns.Add("Location_Value");
    ranges.Columns.Add("Sheet_Name");

    //Add rows or read them from database using SQLDataAdapter


    //note that the destination table must exists in the database with identical columns of datatable

    System.Data.DataTable destination = ReadFromExcel(ranges, "C:\\1.xlsx", "dbo.destination");

    InsertToSQLUsingSQLBulk(destination, "Pass SQL Server destination connection string here");



}

Update 1 - Improve performance

You can improve the method performance by putting all worksheet contents inside an two dimension array, then loop over array instead of looping inside the Excel worksheet.

Excel.Range targetCells = xlWks.UsedRange;
object[,] allValues = (object[,])targetCells.Cells.Value;

...


 if (targetCells.Cells[rowidx, colidx] != null)
 {

     dr["AttributeValue"] = (string)(targetCells.Cells[rowidx, colidx] as Range).Value2;
  }
  else
  {
     dr["AttributeValue"] = "";
  }

Reference

这篇关于根据单元格位置从excel批量插入到sql以选择字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆