Excel到数据库的导入三部曲

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

前段时间考试系统要新添加一个功能,要把学生表的信息批量导入,也就是需要从excel中导入到数据库表,小女子不才,找了好长时间才解决。

  一、如果表是没有建立的,我们需要在数据库表中重新建立一个表盛放excel数据的时候:

  在sql server中的导入语句:

  SELECT * intocity2 FROM OpenDataSource( 'Micros  前段时间考试系统要新添加一个功能,要把学生表的信息批量导入,也就是需要从excel中导入到数据库表,小女子不才,找了好长时间才解决。

  一、如果表是没有建立的,我们需要在数据库表中重新建立一个表盛放excel数据的时候:

  在sql server中的导入语句:

  SELECT * intocity2 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'DataSource="f:\test.xls";User ID=Admin;Password=;Extendedproperties=Excel 5.0')...[Sheet1$]

  这里需要注意的是,如果直接写这个语句,会出现这样的错误:

  SQL Server 阻止了对组件'Ad HocDistributed Queries' 的STATEMENT'OpenRowset/OpenDatasource' 的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用sp_configure 启用'Ad Hoc Distributed Queries'。有关启用'Ad HocDistributed Queries' 的详细信息,请参阅SQL Server 联机丛书中的"外围应用配置器"。

  所以,我们这里需要启动服务:

  启动语句为:

execsp_configure 'show advanced options',1
reconfigure
execsp_configure 'Ad Hoc Distributed Queries',1
reconfigure

  当然,用完之后要记得关闭:

  关闭语句为:

execsp_configure 'Ad Hoc Distributed Queries',0
reconfigure
execsp_configure 'show advanced options',0
reconfigure

  因为考试系统是基于asp.net实现的,所以,一下是asp.net的实现代码,需要注意的是,因为语句中存在”,\等特殊符号,所以,我们需要使用转义字符来使这些特殊符号成为字符串类型,这里是一些常用的转义字符符号:http://baike.baidu.com/view/73.htm

   protectedvoid btntoLaad_Click(object sender, EventArgs e)
{
 
SqlConnectionmycon = new SqlConnection("server=.;database=qingniao;uid=sa;pwd=123");
string sqlstr = "SELECT * into cityFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=\"f:\\test.xls\";User ID=Admin;Password=;Extended properties=Excel5.0')...[Sheet1$]";
 
 
       SqlCommand cmd = new SqlCommand(sqlstr, mycon);
 
       mycon.Open();
 
       cmd.ExecuteNonQuery();
 
       mycon.Close();
}

  这样,数据库中会建立一个city表,来存储excel中的数据。

  二、将excel表导入到已经存在的数据库表

  这里需要注意的是,excel表中的数据必须要和数据库表中的数据一致

  比如,如果数据库表的字段为

  则相应的excel的表字段为:

oft.Jet.OLEDB.4.0', 'DataSource="f:\test.xls";User ID=Admin;Password=;Extendedproperties=Excel 5.0')...[Sheet1$]

 

  这里需要注意的是,如果直接写这个语句,会出现这样的错误:

  SQL Server 阻止了对组件'Ad HocDistributed Queries' 的STATEMENT'OpenRowset/OpenDatasource' 的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用sp_configure 启用'Ad Hoc Distributed Queries'。有关启用'Ad HocDistributed Queries' 的详细信息,请参阅SQL Server 联机丛书中的"外围应用配置器"。

  所以,我们这里需要启动服务:

  启动语句为:

execsp_configure 'show advanced options',1
reconfigure
execsp_configure 'Ad Hoc Distributed Queries',1
reconfigure

  当然,用完之后要记得关闭:

  关闭语句为:

execsp_configure 'Ad Hoc Distributed Queries',0
reconfigure
execsp_configure 'show advanced options',0
reconfigure

  因为考试系统是基于asp.net实现的,所以,一下是asp.net的实现代码,需要注意的是,因为语句中存在”,\等特殊符号,所以,我们需要使用转义字符来使这些特殊符号成为字符串类型,这里是一些常用的转义字符符号:http://baike.baidu.com/view/73.htm

   protectedvoid btntoLaad_Click(object sender, EventArgs e)
{
 
SqlConnectionmycon = new SqlConnection("server=.;database=qingniao;uid=sa;pwd=123");
string sqlstr = "SELECT * into cityFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=\"f:\\test.xls\";User ID=Admin;Password=;Extended properties=Excel5.0')...[Sheet1$]";
 
 
       SqlCommand cmd = new SqlCommand(sqlstr, mycon);
 
       mycon.Open();
 
       cmd.ExecuteNonQuery();
 
       mycon.Close();
}

  这样,数据库中会建立一个city表,来存储excel中的数据。

  二、将excel表导入到已经存在的数据库表

  这里需要注意的是,excel表中的数据必须要和数据库表中的数据一致

  比如,如果数据库表的字段为

  则相应的excel的表字段为:





protectedvoid btnExist_Click(object sender, EventArgs e)
    {
        SqlConnection mycon = new SqlConnection("server=.;database=qingniao;uid=sa;pwd=123");
        string sqlstr = " insert intocity1 SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'DataSource=\"f:\\test.xls\";User ID=Admin;Password=;Extendedproperties=Excel 5.0')...[Sheet1$]";
 
    
        SqlCommand cmd = new SqlCommand(sqlstr,mycon);
 
        mycon.Open();
 
        cmd.ExecuteNonQuery();
 
        mycon.Close();
    }

  这里同样需要开启服务,和第一种的方式一样。

  三、既然已经存在的表,一般都会存在一些设置,比如说主键、外键或者是其他,如果主键或者外键冲突,就会出现导入失败的问题。所以,我们需要对excel表中的数据进行判断。

  则先需要把数据导入到datatable中

  protected void btnLeadingIn_Click(objectsender, EventArgs e)
    {
 
        DataTable dt=new DataTable();
        dt = CreateExcelDataSource("F:\\abc.xls");
        SqlConnection sqlCon = con();
        sqlCon.Open();
 
 
        GridView1.DataSource = dt;
        GridView1.DataBind();
        for (int i = 0; i < dt.Rows.Count;i++)
        {
           //导入数据库,把数据写入数据库应该就是非常简单了,这里就不多写了
        }
 
    }
 
 
    public static DataTableCreateExcelDataSource(string url)
    {
 
        DataTable dt = null;
       
       // string connetionStr ="Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + url+ ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
        string connetionStr = "Provider=Microsoft.Jet.OleDb.4.0;"+ "data source=" + url + ";Extended Properties='Excel 8.0;HDR=YES; IMEX=1'";
        string strSql = "select * from[Sheet1$]";
        OleDbConnection oleConn = new OleDbConnection(connetionStr);
        OleDbDataAdapter oleAdapter = new OleDbDataAdapter(strSql,connetionStr);
        try
        {
            dt = new DataTable();
            oleAdapter.Fill(dt);
 
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            oleAdapter.Dispose();
            oleConn.Close();
            oleConn.Dispose();
        }
    }
 

  当然,我感觉我这里把datatable中的数据一条一条的取出来判断至少是非常耗时间耗内存的,而且这里最好加上回滚事物,因为在我们导入过程中会经常出现这样活那样的问题,采用事物,可以在出错的时候把数据回滚到没有导入之前的状态,防止意外事件发生,这里我就不往上加了。

  以上是三种是我们实现了的excel导入,当然,我想方法还不止这些,当然,除了导入,还有的就是从数据库表导出到excel表中,因为我做的那部分系统没有涉及到,所以这里就不再提了。

  下面说一下我在解决的过程中绕的弯路:

  一、我没有把导入数据库的种种做法弄清楚,比如是直接创建表呢还是在已经存在的表中导入,所以以至于刚开始总是找不到合适的方法。

  二、在后来的查找过程中,我发现我有一个很大的毛病,就是我的关键字是在“asp.net中、、、、、”,其实,既然是往数据库中导入,asp.net只是一个执行一下,所以,因为有了关键字的约束,查出来的资料少不说,而且还形成了一种思维定式,除了asp.net之外的其他都一概不看。

  三、对查不来的信息不能加以理解,只是能用就用,不能用就换,也就是我因为转义字符串那一块弄了很长时间的原因,因为当我们在sql执行查询语句没有错误的时候,需要把它放在asp.net中执行,因为这些语句需要string字符串来显示,而这个执行语句中有包含引号,所以需要转义字符,在解决引号的问题之后,我发现还是不正确,一直折腾了好久才发现是路径F:\test.xls中“\t“是table的转义字符,所以这里需要两个\来转义\,这就是应该写成这样“F:\\test.xls”,而当我写成这样的时候,我才想起来,其实最开始查询的时候所有的代码都是这样的,只是那些我没用上,当时也没多想,以为路径就应该是这样的,最终导致还继续在这个上面栽跟头。



 








====================================分割线================================



最新内容请见作者的GitHub页:http://qaseven.github.io/

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
8天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(下)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
47 0
|
8天前
|
Java 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口(上)
【MySQL × SpringBoot 突发奇想】全面实现流程 · xlsx文件,Excel表格导入数据库的接口
52 0
|
4天前
|
存储 数据挖掘 关系型数据库
DataFrame 与数据库交互:从导入到导出
【5月更文挑战第19天】本文介绍了如何在数据分析中实现DataFrame与MySQL数据库之间的数据交互。通过`pandas`的`read_sql`函数可将数据库中的数据导入DataFrame,处理后使用数据库游标执行插入或更新操作将数据导回。注意数据类型匹配、数据完整性和一致性。对于大量数据,可采用分块读取和批量操作提升效率。这种交互能结合数据库的存储管理和DataFrame的分析功能,提高数据处理效率。
20 2
|
8天前
|
easyexcel Java 关系型数据库
厉害了!12秒将百万数据通过EasyExcel导入MySQL数据库中
【5月更文挑战第12天】厉害了!12秒将百万数据通过EasyExcel导入MySQL数据库中
17 1
|
8天前
|
前端开发 关系型数据库 MySQL
【MySQL × SpringBoot 突发奇想】全面实现流程 · 数据库导出Excel表格文件的接口
【MySQL × SpringBoot 突发奇想】全面实现流程 · 数据库导出Excel表格文件的接口
38 0
|
9天前
|
存储 API C#
C# 实现格式化文本导入到Excel
C# 实现格式化文本导入到Excel
|
9天前
|
SQL 存储 Ubuntu
在ubuntu中将dict.txt导入到数据库sqlite3
这样,你就成功将 `dict.txt` 中的数据导入到名为 `mydatabase.db` 的SQLite3数据库中的 `words` 表格中了。请根据实际情况调整表格结构和数据导入命令。
19 0
|
9天前
|
数据库
如何把Excel导入到数据库中
如何把Excel导入到数据库中
13 0
|
6天前
|
关系型数据库 MySQL API
实时计算 Flink版产品使用合集之可以通过mysql-cdc动态监听MySQL数据库的数据变动吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
82 0
|
9天前
|
关系型数据库 MySQL 数据库
docker MySQL删除数据库时的错误(errno: 39)
docker MySQL删除数据库时的错误(errno: 39)
65 0