把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,如需转载请自行联系原作者
目录
相关文章
|
18天前
|
数据采集 存储 JavaScript
自动化数据处理:使用Selenium与Excel打造的数据爬取管道
本文介绍了一种使用Selenium和Excel结合代理IP技术从WIPO品牌数据库(branddb.wipo.int)自动化爬取专利信息的方法。通过Selenium模拟用户操作,处理JavaScript动态加载页面,利用代理IP避免IP封禁,确保数据爬取稳定性和隐私性。爬取的数据将存储在Excel中,便于后续分析。此外,文章还详细介绍了Selenium的基本设置、代理IP配置及使用技巧,并探讨了未来可能采用的更多防反爬策略,以提升爬虫效率和稳定性。
|
21天前
|
SQL 关系型数据库 MySQL
数据库导入SQL文件:全面解析与操作指南
在数据库管理中,将SQL文件导入数据库是一个常见且重要的操作。无论是迁移数据、恢复备份,还是测试和开发环境搭建,掌握如何正确导入SQL文件都至关重要。本文将详细介绍数据库导入SQL文件的全过程,包括准备工作、操作步骤以及常见问题解决方案,旨在为数据库管理员和开发者提供全面的操作指南。一、准备工作在导
79 0
|
15天前
|
数据处理 Python
Python实用记录(十):获取excel数据并通过列表的形式保存为txt文档、xlsx文档、csv文档
这篇文章介绍了如何使用Python读取Excel文件中的数据,处理后将其保存为txt、xlsx和csv格式的文件。
37 3
Python实用记录(十):获取excel数据并通过列表的形式保存为txt文档、xlsx文档、csv文档
|
3天前
|
人工智能 Cloud Native 容灾
云数据库“再进化”,OB Cloud如何打造云时代的数据底座?
云数据库“再进化”,OB Cloud如何打造云时代的数据底座?
|
11天前
|
SQL 存储 关系型数据库
数据储存数据库管理系统(DBMS)
【10月更文挑战第11天】
37 3
|
18天前
|
easyexcel Java UED
SpringBoot中大量数据导出方案:使用EasyExcel并行导出多个excel文件并压缩zip后下载
在SpringBoot环境中,为了优化大量数据的Excel导出体验,可采用异步方式处理。具体做法是将数据拆分后利用`CompletableFuture`与`ThreadPoolTaskExecutor`并行导出,并使用EasyExcel生成多个Excel文件,最终将其压缩成ZIP文件供下载。此方案提升了导出效率,改善了用户体验。代码示例展示了如何实现这一过程,包括多线程处理、模板导出及资源清理等关键步骤。
|
17天前
|
SQL 存储 关系型数据库
添加数据到数据库的SQL语句详解与实践技巧
在数据库管理中,添加数据是一个基本操作,它涉及到向表中插入新的记录
|
20天前
|
SQL 监控 数据处理
SQL数据库数据修改操作详解
数据库是现代信息系统的重要组成部分,其中SQL(StructuredQueryLanguage)是管理和处理数据库的重要工具之一。在日常的业务运营过程中,数据的准确性和及时性对企业来说至关重要,这就需要掌握如何在数据库中正确地进行数据修改操作。本文将详细介绍在SQL数据库中如何修改数据,帮助读者更好
96 4
|
20天前
|
SQL 存储 关系型数据库
SQL文件导入MySQL数据库的详细指南
数据库中的数据转移是一项常规任务,无论是在数据迁移过程中,还是在数据备份、还原场景中,导入导出SQL文件显得尤为重要。特别是在使用MySQL数据库时,如何将SQL文件导入数据库是一项基本技能。本文将详细介绍如何将SQL文件导入MySQL数据库,并提供一个清晰、完整的步骤指南。这篇文章的内容字数大约在
42 1
|
21天前
|
存储 人工智能 Cloud Native
云栖重磅|从数据到智能:Data+AI驱动的云原生数据库
阿里云瑶池在2024云栖大会上重磅发布由Data+AI驱动的多模数据管理平台DMS:OneMeta+OneOps,通过统一、开放、多模的元数据服务实现跨环境、跨引擎、跨实例的统一治理,可支持高达40+种数据源,实现自建、他云数据源的无缝对接,助力业务决策效率提升10倍。