开发者社区> 长征6号> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

把Excel的数据导入到数据库

简介:
+关注继续查看

将Excel作为数据源,将数据导入数据库,是SSIS的一个简单的应用,下图是示例Excel,数据列是code和name

第一部分,Excel中的数据类型是数值类型

1,使用SSDT创建一个package,创建Excel data source component,SSDT会在Connection Managers中创建一个Excel的connection

由于示例Excel的首行是列名,所以需要勾选"First row has column names",Excel connection manager 如下

2,数据源组件将Excel中的数据读取出来,并传递给其他组件,数据源组件其实是有输入和输出的,输入是指将Excel的数据导入到数据源组件中,输出是指将数据源组件中导入的Excel数据向下传递。

打开数据源组件的Advanced editor,通过Show Advanced Editor来打开

在Input and Output Properties选项卡中,External columns是Excel数据源组件的输入列,Output Columns是Excel数据源组件的输出列,每一列都是有DataType和CodePage。

默认情况下,SSIS的Excel链接器将Excel中的数字作为数值类型来对待

对于External columns,可以根据实际需要修改DataType和CodePage,对于数值类型,不需要关注CodePage,但是对于字符类型,CodePage就必须匹配,否则package在run时就会fail。

由于示例Excel的两列的值都是数字,SSIS默认设置DataType为数值类型,对于DataType,虽然可以修改,但是数据源组件并不负责DataType的转换,如果External columns 和Output Columns的DataType不相同,run的时候会抛出error。如果需要convert DataType,需要使用Data Conversion 组件。

3,在db中创建接收Excel数据的表tbExcel,数据类似是Nvarchar,接收的数据是数值型,这样并不会报错。

create table dbo.tbExcel
(
code nvarchar(10),
name nvarchar(10)
)

4,创建一个Oledb数据目标组件,打开Advanced Editor,看到Ole db Destination Input 也有两个:

External columns:是DB中的目标表的数据列及其属性信息,本例是指 tbExcel 表的列和属性

Input Columns:是上游数据源组件传递的数据列及其属性信息

 

5,设置数据源组件和数据目标组件的列的mapping,execute package,成功导入13 rows 数据

 

第二部分,将Excel中的数据类型修改为文本类型

6,如果Excel source 中的数据是文本类型,实现起来必须考虑CodePage。

修改示例Excel,将name列修改为文本类型

 

7,将Db中的目标表做修改,将数据列修改为varchar

复制代码
if object_id('dbo.tbExcel') is not null
drop table dbo.tbExcel

create table dbo.tbExcel
(
code varchar(10),
name varchar(10)
)
复制代码


8,在execute package的过程中,ssis抛出错误信息,也就是说Excel中的文本使用的数据类型是unicode 的,而varchar并不是unicode,所以必须进行转换,在package中加入Data converion组件进行转换

===================================

Package Validation Error (Package Validation Error)

===================================

Error at Data Flow Task [OLE DB Destination [166]]: Column "name" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [SSIS.Pipeline]: "OLE DB Destination" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

 (Microsoft.DataTransformationServices.VsIntegration)

 

9,execute package,仍然出错,错误原因是Code Page 不匹配,在数据传递的过程中,不能将code page为1252的数据传递到codepage为936的目标表中

Error at Data Flow Task [OLE DB Destination [203]]: The column "Copy of name" cannot be processed because more than one code page (1252 and 936) are specified for it.

 

下图是Ole db Destination Input,在External columns中查看name的属性,codepage是936,Input columns中查看name的属性,codepage是1252

 

由于列 copy of name是从Data Conversion中转换而来的,所以只需要修改一下转换后的数据列的CodePage就可以了。

 

10,设置列的mapping,再次Execute package,一路泛绿,成功导入13 rows

 

第三部分,一点小总结

  • Excel的文本,默认的数据类型是Unicode,长度是255
  • DB中的Varchar 不是unicode类型,nvarchar是unicode类型
  • 如果codepage不一致,可以通过data conversion组件进行转换

 

作者悦光阴
本文版权归作者和博客园所有,欢迎转载,但未经作者同意,必须保留此段声明,且在文章页面醒目位置显示原文连接,否则保留追究法律责任的权利。
分类: SSIS 组件





本文转自悦光阴博客园博客,原文链接:http://www.cnblogs.com/ljhdo/p/4451285.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
四十四、导入数据
四十四、导入数据
38 0
c# 导出数据到Excel模板
最近在做一个发邮件的功能,客户要求需要导出一个Excel附件,并给了附件的格式, eg: Last Name 姓 First Name 名 Chinese Characters汉字书写(仅大陆人填写)               实现方式有两种: 一、使用Microsoft.
1362 0
Excel导入导出数据库01
主要分为两部份 1.Excel操作类 1 引入 2 using System.Data.OleDb; 3 using System.IO; 4 using System.Collections; 5 6 /// 7 /// Exce...
1057 0
excel数据导入DB
一、excel的好处:可以方便非程序专业的人写数据,然后由程序导入。 二、导入方式:   1.使用excel宏读excel表中数据,用字符串拼接生成sql语句,把sql语句拷贝到DB工具中执行,生成表与数据   2.用java或VB或c++写工具,用来读取指定文件路径下的excel,自动生成sql语言,并调用 DB接口把数据写入DB(基础表数据,虚拟数据)   3.写成工具或网页,让数据录入员在这个界面上输入,可以根据实际情况限制输入数据的规范,保证数据的正确与安全性。
864 0
ASP导出Excel数据的四种方法
 一、使用OWC   什么是OWC?   OWC是Office Web Compent的缩写,即Microsoft的Office Web组件,它为在Web中绘制图形提供了灵活的同时也是最基本的机制。
753 0
+关注
1222
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载