首页新闻找找看学习计划

不同结构的Excel导入数据库同一个表中

0
悬赏园豆:10 [待解决问题]

我的实现方式是利用NPOI将Excel表格读取到Datatable
根据需要调整Datatable的表结构
创建数据库临时表
利用SqlBulk将Datatable中的数据导入到临时表中
利用数据库中存储的预处理语句对临时表进行处理
将处理后的数据insert到数据库正式表中

目前遇到的问题
1、利用NPOI读取效率并不高,但能够满足需要
2、导入数据时,有时快,有时慢出现超时提醒
3、每次导入的数据1万条以内

代码如下:

        using (SqlConnection con = new SqlConnection(Variables.SysConStr))
        {
            con.Open();
            using (SqlTransaction tran = con.BeginTransaction())
            {
                try
                {
                        DataTable originalTable = ExcelImportExport.ImportExcel(filename, false, -1, 0, true, 24, fileBh);//读取原始数据保存到表




                            tempTableName = SystemMethods.CreateTempTable(tran, originalTable);
                            SystemMethods.ImportDataToTemp(tran,originalTable, tempTableName);
                            SystemMethods.ProcessData(tran, tempTableName, SjLy); 


                    }
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                   
                }
                finally
                {
                    if (con.State == ConnectionState.Open)
                        con.Close();
                }
            }
        }




    public static string CreateTempTable(SqlTransaction tran,DataTable dt)
    {
        string TableName = SqlServerHelper.ExecuteScalar(tran, "SELECT '#'+CAST(NEWID() AS NVARCHAR(40)) AS TableName", null).ToString();
        TableName = TableName.Replace("-", "_");
        //获取创建表的SQL 
        string sqlStr = "Create table " + TableName + " ( ";
        foreach (DataColumn dc in dt.Columns)   //循环列 
        {
            //获取创建表的SQL,具体自己再发挥 
            sqlStr = sqlStr + "[" + dc.ColumnName + "] nvarchar(200), ";
        }
        sqlStr = sqlStr.Substring(0, sqlStr.Length - 2) + ") ";
        SqlServerHelper.ExecuteNonQuery(tran, sqlStr, null);
        return TableName;
    }

    public static void ImportDataToTemp(SqlTransaction tran, DataTable dt, string TempTableName)
    {
        using (SqlBulkCopy bcp = new SqlBulkCopy(tran.Connection, SqlBulkCopyOptions.Default, tran))
        {
            bcp.DestinationTableName = TempTableName;
            bcp.WriteToServer(dt);
        }
    }

    public static void ProcessData(SqlTransaction tran,string TempTableName,string BankName)
    {
        string sqlStr = "SELECT FLEVEL,FVALUE FROM SystemProfile WHERE FCATEGORY='数据导入' AND FKEY='预处理' AND FTYPE='" + BankName + "' ORDER BY FLEVEL";
        DataTable dt = SqlServerHelper.ExecuteDataSet(tran, sqlStr, null).Tables["Table1"];

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            sqlStr = dt.Rows[i]["FVALUE"].ToString();
            sqlStr = sqlStr.Replace("#TEMP#", TempTableName);
            SqlServerHelper.ExecuteNonQuery(tran, sqlStr, null);
            System.Threading.Thread.Sleep(200);
        }

        sqlStr = "DROP TABLE " + TempTableName;
        SqlServerHelper.ExecuteNonQuery(tran, sqlStr, null);

    }
一直的初学者的主页 一直的初学者 | 初学一级 | 园豆:192
提问于:2020-02-09 21:37
< >
分享
所有回答(2)
0
MiaZhang | 园豆:206 (菜鸟二级) | 2020-02-10 10:03
0

1、利用NPOI读取效率并不高,但能够满足需要
2、导入数据时,有时快,有时慢出现超时提醒
3、每次导入的数据1万条以内

  1. 效率够就行。不回答。
  2. 有时快,有时慢,需要你找出原因,如果你是在生产环境测试,那是必然的,有时正好很多人用,有时正好很少人用。
    纯粹看这段代码的执行效率或者效果,你只能在测试环境下看。
    而如果要看生产环境,那么就需要去生产环境服务器看是否过载,CPU, DISK,MEMORY是占用情况。
    然后再决定如何优化。
    优化的方向- 简单的升级硬件
    软件方面当然也有很多可以考虑和优化的,但首先,需要知道瓶颈在哪儿。
  3. 1万条数据太少,几乎不构成任何问题。
爱编程的大叔 | 园豆:30089 (高人七级) | 2020-02-10 14:43

非常感谢你的回答
先说明一下:
1、我是业余开发,纯自学,基础薄弱,深的不懂😭
2、软件的使用场景不需要支持多人,单机就行
3、就是软件本身是为了对不同银行查询到的数据进行整理分析,难点就在不同银行的电子数据格式不同。
4、不同银行导出的数据存在各种各样的问题,例如没有可用于排序的列,只能按照excel表格中的顺序添加序号,部分银行信息存在制表符,空格等需要进行替换,金额存储为文本格式,但有的有千分位分割,需要转换,交易金额有的只是记录值,没有正负号,需要根据其他列添加正负号。
5、我目前的做法是将excel表格中的数据读取到datatable,进行一次处理导入到临时表中,这时的字段基本都是中文的,临时表的字段都是nvarchar(200),利用SqlBulk将datatable中的数据导入到临时表,针对不同银行的数据我做了预处理语句存储在数据库中,导入之后调用预处理sql语句对临时表进行处理,然后导入正式表中。
6、偶尔超时是在开发环境,生产环境没有进行测试。
————————————————
自己技术经验等方面的局限性,让我没有更好的方案,超时的问题不知道怎么回事,希望得到你的帮助和建议,谢谢😜

支持(0) 反对(0) 一直的初学者 | 园豆:192 (初学一级) | 2020-02-10 15:18

@一直的初学者:
没事,谁也不是天生就会。
1、先说一下你的做法,所谓的预处理SQL语句是指存储过程吗?
2、一般情况下,没有特别声明,超时时间是30秒,可以自行延长(虽然不推荐)
3、超时是出现在SQLBULK 插入的时候,还是运行存储过程 ProcessData的时候?既然是开发环境,你可以加入LOG,看看啥时出错,错在哪一句。这样才能进行后面的对症下药。

  1. 存粹过程其实挺麻烦的,起码我觉得写起来比C#麻烦多了。
  2. 你现在的做法等于是,保存一次数据,取出数据,处理,再保存一次,
    为什么不
    NPOI读EXCEL数据,处理数据,保存数据。
  3. 其实SQLBULK 只是在某些情况下能让性能变好点,而你的问题并不是性能问题,不妨试试不用SQLBULK,而是一条条数据处理(保存)看看会怎样。
  4. 简单归纳一下:
    先找出是哪一句代码超时。(通过LOG日志,可自行阅读相关文档)
    然后才能对症下药,药我在上面也略微提供了一些。
支持(0) 反对(0) 爱编程的大叔 | 园豆:30089 (高人七级) | 2020-02-10 17:33
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册