Excel ETL处理五要素 常见问题

经常 我们在处理小数据集是 不想用大刀，逮住了excel 就想 一直拖拖拉拉就把数据先处理一下，实在处理费力，再从 python numpy pandas 处理一通。

常见的 在处理五要素的时候，姓名基本没啥要处理的，
1身份z号 手机号 yhk号 经常是 显示为 E+17 或者 前面带有 ``` 字符
2申请日期经常是各种格式，有时候一不小心就变成乱码的数字 后者####。
3如果 excel 有外部链接存在，即使忽略，但是 在修改的时候还是非常糟心，要等很久，而且经常无法保存，excel就崩溃了
4还有就是 本身就是文本，但是使用 TEXT LEFT RIGHT MID IF 没有任何效果
5excel 读取 含有中文的数据显示乱码
6多条件的替换问题，if嵌套。
7批量 替换 匹配
8空值问题
9筛选问题
10脚本读取文件

其实就是以上的这些小情况，经常浪费大量时间，急需一招狠制敌

接着说 1
因为很多时候 excel会把一些类数值的身份z号和手机号当做数字，这个其实是数据源在使用python处理的时候没有来得及保存 对应列的 数据类型。
在出现 E+17,我们首先想到的是修改 单元格格式，比如保存为文本，假如正常那简直是万幸，如果不可以，可能会发现 修改后的数据末尾都变成了3个零。
另外一个终极方法
这里，有一个简单的 *** 作方法：选择数据源——右键设置单元格格式——自定义——选择0，按确定——结果出来了。结果很明显，是我们需要的效果，而且快捷，方便。

另外就是使用python pandas读取 ，设置 dtype=npstr

对于 身份z或者手机号 yhk号 数据首位出现 `字符的，可以使用 RIGHT MID 函数做数据的截取

对于数据出现 ###,一般修改单元格格式 ，或者 拉长单元格，或者 python读取一下就可以处理

最后就是说2了。日期
其实这个日期，就是格式太特么的多和复杂不固定。我们现在 使用的是 类似 2018-4-17 这种格式，所以只要不符合这种格式的，我们都会需要做一些转化，
最简单的 就是使用 TEXT 函数 ，一般这样用 =TEXT(E2,"yyyy-mm-dd")
基本就可以转化过来。
如果以上不起作用 参考 处理 4 的方法 不断尝试 ，最后确认函数真的不会起作用为止。
之后如果 不小心 变成一段数字了，修改单元格格式，转回日期一般是有效的。
如果日期 函数不起效果，我们使用截取 拼接的方式 使用 RIGHT LEFT MID
CONCATENATE() 这些函数 一般也可以胜任

如果 还是不行也可以 使用 YEAR() DATE() EXCEL 本身的日期处理函数
另外就是修改单元格格式 ，尝试一下各种日期格式是否可以满足要求
另外的杀手锏就是 python pandas 和numpy都有 时间处理的函数
可以参考
推荐阅读
>

1 、简介

DataPipeline ：隶属于北京数见 科技 有限公司，是一家企业级批流一体数据融合服务商和解决方案提供商，国内实时数据管道技术的倡导者。

通过平台和技术为企业客户解决数据准备过程中的各种痛点，帮助客户更敏捷、更高效、更简单地实现复杂异构数据源到目的地的实时数据融合和数据管理等综合服务。

从而打破传统 ETL 给客户灵活数据应用带来的束缚，让数据准备过程不再成为数据消费的瓶颈。

Kettle：是一款国外开源的ETL工具，纯java编写，可以在Windows、Linux、Unix上运行，数据抽取高效稳定。Kettle 中文名称叫水壶，该项目的主程序员MATT 希望把各种数据放到一个壶里，然后以一种指定的格式流出。

Informatica：是全球领先的数据管理软件提供商。

在如下Gartner魔力象限位于领导者地位：数据集成工具魔力象限、数据质量工具魔力象限、元数据管理解决方案魔力象限、主数据管理解决方案魔力象限、企业级集成平台即服务（EiPaaS）魔力象限。

Talend ：是数据集成解决方案领域的领袖企业，为公共云和私有云以及本地环境提供一体化的数据集成平台。Talend的使命是致力于帮助客户优化数据，提高数据可靠性，把企业数据更快地转化为商业价值。

以此为使命，Talend的解决方案将数据从传统基础架构中解放出来，提高客户在业务中的洞察力，让客户更早实现业务价值。

DataX ：是阿里巴巴集团内被广泛使用的离线数据同步工具 / 平台，实现包括 MySQL、Oracle、SqlServer、Postgre、HDFS、Hive、ADS、HBase、TableStore(OTS)、MaxCompute(ODPS)、DRDS 等各种异构数据源之间高效的数据同步功能。开源地址：>ETL是数据仓库中的非常重要的一环，是承前启后的必要的一步。ETL负责将分布的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转换、集成，最后加载到数据仓库或数据集市中，成为联机分析处理、数据挖掘的基础。
下面给大家介绍一下什么是ETL以及ETL常用的三种工具——Datastage，Informatica，Kettle。
一、什么是ETL？
ETL，Extract-Transform-Load 的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。
数据仓库结构
通俗的说法就是从数据源抽取数据出来，进行清洗加工转换，然后加载到定义好的数据仓库模型中去。目的是将企业中的分散、零乱、标准不统一的数据整合到一起，为企业的决策提供分析依据。
ETL是BI项目重要的一个环节，其设计的好坏影响生成数据的质量，直接关系到BI项目的成败。
二、为什么要用ETL工具？
在数据处理的时候，我们有时会遇到这些问题：
▶ 当数据来自不同的物理主机，这时候如使用SQL语句去处理的话，就显得比较吃力且开销也更大。
▶ 数据来源可以是各种不同的数据库或者文件，这时候需要先把他们整理成统一的格式后才可以进行数据的处理，这一过程用代码实现显然有些麻烦。
▶ 在数据库中我们当然可以使用存储过程去处理数据，但是处理海量数据的时候存储过程显然比较吃力，而且会占用较多数据库的资源，这可能会导致数据资源不足，进而影响数据库的性能。
而上述遇到的问题，我们用ETL工具就可以解决。ETL工具具有以下几点优势：
1、支持多种异构数据源的连接。（部分）
2、图形化的界面 *** 作十分方便。
3、处理海量数据速度快、流程更清晰等。
三、ETL工具介绍
1、Datastage
IBM公司的商业软件，最专业的ETL工具，但同时价格不菲，适合大规模的ETL应用。
使用难度：★★★★
2、Informatica
商业软件，相当专业的ETL工具。价格上比Datastage便宜一点，也适合大规模的ETL应用。
使用难度：★★
3、Kettle
免费，最著名的开源产品，是用纯java编写的ETL工具，只需要JVM环境即可部署，可跨平台，扩展性好。
使用难度：★★
四、三种ETL工具的对比
Datastage、Informatica、Kettle三个ETL工具的特点和差异介绍：
1、 *** 作
这三种ETL工具都是属于比较简单易用的，主要看开发人员对于工具的熟练程度。
Informatica有四个开发管理组件，开发的时候我们需要打开其中三个进行开发，Informatica没有ctrl+z的功能，如果对job作了改变之后，想要撤销，返回到改变前是不可能的。相比Kettle跟Datastage在测试调试的时候不太方便。Datastage全部的 *** 作在同一个界面中，不用切换界面，能够看到数据的来源，整个job的情况，在找bug的时候会比Informatica方便。
Kettle介于两者之间。
2、部署
Kettle只需要JVM环境，Informatica需要服务器和客户端安装，而Datastage的部署比较耗费时间，有一点难度。
3、数据处理的速度
大数据量下Informatica与Datastage的处理速度是比较快的，比较稳定。Kettle的处理速度相比之下稍慢。
4、服务
Informatica与Datastage有很好的商业化的技术支持，而Kettle则没有。商业软件的售后服务上会比免费的开源软件好很多。
5、风险
风险与成本成反比，也与技术能力成正比。
6、扩展
Kettle的扩展性无疑是最好，因为是开源代码，可以自己开发拓展它的功能，而Informatica和Datastage由于是商业软件，基本上没有。
7、Job的监控
三者都有监控和日志工具。
在数据的监控上，个人觉得Datastage的实时监控做的更加好，可以直观看到数据抽取的情况，运行到哪一个控件上。这对于调优来说，我们可以更快的定位到处理速度太慢的控件并进行处理，而informatica也有相应的功能，但是并不直观，需要通过两个界面的对比才可以定位到处理速度缓慢的控件。有时候还需要通过一些方法去查找。
8、网上的技术文档
Datastage < Informatica < kettle,相对来说，Datastage跟Informatica在遇到问题去网上找到解决方法的概率比较低，kettle则比较多。
五、项目经验分享
在项目中，很多时候我们都需要同步生产库的表到数据仓库中。一百多张表同步、重复的 *** 作，对开发人员来说是细心和耐心的考验。在这种情况下，开发人员最喜欢的工具无疑是kettle，多个表的同步都可以用同一个程序运行，不必每一张表的同步都建一个程序，而informatica虽然有提供工具去批量设计，但还是需要生成多个程序进行一一配置，而datastage在这方面就显得比较笨拙。
在做增量表的时候，每次运行后都需要把将最新的一条数据 *** 作时间存到数据库中，下次运行我们就取大于这个时间的数据。Kettle有控件可以直接读取数据库中的这个时间置为变量；对于没有类似功能控件的informatica，我们的做法是先读取的数据库中的这个时间存到文件，然后主程序运行的时候指定这个文件为参数文件，也可以得到同样的效果

楼主好，我现在正在做BI相关的东西。如果ETL和SQL来说，肯定是SQL效率高的多。但是双方各有优势，先说ETL，ETL主要面向的是建立数据仓库来使用的。ETL更偏向数据清洗，多数据源数据整合，获取增量，转换加载到数据仓库所使用的工具。比如我有两个数据源，一个是数据库的表，另外一个是excel数据，而我需要合并这两个数据，通常这种东西在SQL语句中比较难实现。但是ETL却有很多现成的组件和驱动，几个组件就搞定了。还有比如跨服务器，并且服务器之间不能建立连接的数据源，比如我们公司系统分为一期和二期，存放的数据库是不同的，数据结构也不相同，数据库之间也不能建立连接，这种情况下，ETL就显得尤为重要和突出。通过固定的抽取，转换，加载到数据仓库中，即可很容易实现。
那么SQL呢？SQL事实上只是固定的脚本语言，但是执行效率高，速度快。不过灵活性不高，很难跨服务器整合数据。所以SQL更适合在固定数据库中执行大范围的查询和数据更改，由于脚本语言可以随便编写，所以在固定数据库中能够实现的功能就相当强大，不像ETL中功能只能受组件限制，组件有什么功能，才能实现什么功能。
所以具体我们在什么时候使用ETL和SQL就很明显了，当我们需要多数据源整合建立数据仓库，并进行数据分析的时候，我们使用ETL。如果是固定单一数据库的数据层次处理，我们就使用SQL。当然，ETL也是离不开SQL的。

Pentaho Data Integration (Kettle)是Pentaho生态系统中默认的ETL工具。通过非常直观的图形化编辑器（Spoon），您可以定义以XML格式储存的流程。在Kettle运行过程中，这些流程会以不同的方法编译。用到的工具包括命令行工具(Pan),小型服务器(Carte)，数据库存储库（repository）(Kitchen)或者直接使用IDE(Spoon)。
Talend Open Studio是 Talend 开发的ETL工具——Talend 是一家主营数据集成和数据管理解决方案的企业。Talend 采用用户友好型，综合性很强的IDE（类似于Pentaho Kettle 的 Spoon）来设计不同的流程。这些流程可以在IDE内部测试并编译成Java 代码。您可以随时查看并编辑生成的Java代码，同时实现强大的控制力和灵活性。
两者都非常优秀，都属于用户友好型的交叉平台（基于Java的）工具。它们的主要差异在于Kettle 将 ELT 流程编译为 XML 格式，然而Talend Open Studio 则生成 Java 代码。
易用性：
Talend：有 GUI 图形界面但是以 Eclipse 的插件方式提供。
Kettle：有非常容易使用的 GUI，出现问题可以到社区咨询。
技术支持：
Talend：主要在美国
Kettle：在美国，欧洲（比利时，德国，法国，英国），亚洲（中国，日本，韩国）都可以找到相关技术支持人员。
部署：
Talend：创建 java 或perl 文件，并通过 *** 作系统调度工具来运行
Kettle：可以使用 job 作业方式或 *** 作系统调度，来执行一个转换文件或作业文件，也可以通过集群的方式在多台机器上部署。
速度：
Talend：需要手工调整，对特定数据源有优化知识。
Kettle：比 Talend 快，不过也需要手工调整，对 Oracle 和 PostGre 等数据源做了优化，同时也取决于转换任务的设计。
数据质量：
Talend：在 GUI 里有数据质量特性，可以手工写 SQL 语句。
Kettle：在 GUI 里有数据质量特性，可以手工写 SQL 语句、java脚本、正则表达式来完成数据清洗。
监控：
Talend：有监控和日志工具
Kettle：有监控和日志工具
连接性：
Talend：各种常用数据库，文件，web service。
Kettle：非常广泛的数据库，文件，另外可以通过插件扩展

您好，这样的：
J2ME j2me 在构建期间出错,该怎么解决
j2me 在构建期间出错在构建期间出错。 对项目 chatroom 运行构建器“Preverification”时出错。 Illegal Manifest Entry Key or Value "E:\j2me&
C# 意欲运行项目时出错
试图运行项目时出错各位高手大家好，我有一项目在调试时出现如下错误：“试图运行项目时出错:Could not load file or assembly '项目名称'or one of its dependencies The module was expected to contain an assembly
软件架构设计 运用maven、jaxb等技术构建可配置编译打包及可配置项目运行
使用maven、jaxb等技术构建可配置编译打包及可配置项目运行 一、说在前面apache ant是以个非常流行的项目构建打包开源工具。在很长一段时间里，ant简化了我们项目构建打包的过程，使得我们项目部署的速度有了大幅的提升。然而，熟悉ant使用的朋友可能与我都有一个切身的感受：当我们在使用ant进行项目
J2SE 运行WEB项目时出错，请教这是为什么
运行WEB项目时出错，请问这是为什么？>

几种 ETL 工具的比较(DataPipeline，Kettle，Talend，Informatica等)

四种工具的比较主要从以下几方面进行比对：

1、成本：

软件成本包括多方面,主要包括软件产品, 售前培训, 售后咨询, 技术支持等。

开源产品本身是免费的，成本主要是培训和咨询，所以成本会一直维持在一个较低水平。

商业产品本身价格很高，但是一般会提供几次免费的咨询或支持，所以采用商用软件最初成本很高，但是逐渐下降。

手工编码最初成本不高，主要是人力成本，但后期维护的工作量会越来越大。

2、易用性：

DataPipeline： 有非常容易使用的 GUI，具有丰富的可视化监控；

Kettle： GUI+Coding；

Informatica： GUI+Coding，有GUI，但是要专门的训练；

Talend：GUI+Coding，有 GUI 图形界面但是以 Eclipse 的插件方式提供；

3、技能要求：

DataPipeline： *** 作简单，无技术要求；

Kettle： ETL设计, SQL, 数据建模 ；

Informatica： ETL设计, SQL, 数据建模；

Talend：需要写Java；

4、底层架构：

DataPipeline：分布式，可水平扩展；

Kettle：主从结构非高可用；

Informatica：分布式；

Talend：分布式；

5、数据实时性：

DataPipeline：支持异构数据源的实时同步，速度非常快；

Kettle：不支持实时数据同步；

Informatica：支持实时，效率较低；

Talend：支持实时处理，需要购买高级版本，价格贵；

6、技术支持：

DataPipeline：本地化原厂技术支持；

Kettle：无；

Informatica：主要在美国；

Talend：主要在美国；

7、自动断点续传：

DataPipeline：支持；

Kettle：不支持；

Informatica：不支持；

Talend：不支持；

---