数据转换之 Easy Data Transform 软件的使用

前记

最近在做数据的清洗工作，不得不操作一些csv、xlxs等文本文件，虽然开始很欢快的全部导入数据库的临时表中操作，复杂一些的操作不能解决，我就用程序去批量操作。但这样一些小改动小调整还算顺手，但是操作量上来就发现有下面几个明显的问题：

1. 比较费时，一些小操作写SQL较费时的，写代码解决代价又较高
2. 验证也有代价，操作失误数据恢复也比较麻烦，总不能操作一步就备份一次。
3. 跟第二条差不多，有时候发现最终结果有问题，又不知道哪一步出现的问题，问题复现较麻烦，不能所见即所得
   于是我就期望能找到一个对轻量小操作方便、图形化操作、所见即所得的专业软件、最好还能支持脚本操作应对一些复杂点的操作，最后终于找到一个，那就是 ——Easy Data Transform

Easy Data Transform 是什么？

Easy Data Transform是款专业的表格数据转换工具，无需编程，即可实现Excel和CSV格式文件的互转，快速的将表格或者列表中的数据转换成自己所需的编码，拥有42种转换和60种文本编码可供选择，还提供了分割、合并、去除重复项目、过滤指定数据等操作。

Easy Data Transform功能

• 连接多个数据表
• 更改CSV文件定界符和编码
• 过滤和分析日志文件
• 合并和重复数据删除电子邮件和地址列表
• 重组数据表列
• 重新格式化日期和数字
• 更多的操作见下方的列表

Easy Data Transform特色

• 可视化
• 立即查看每次转化的结果。
• 逐步的
• 从简单的步骤建立复杂的转化。
• 简单的
• 不需要编程。
• 快速
• 眨眼间就能转换数千行。
• 强大的
• 42种转换和60种文本编码可供选择。
• 批量加工
• 在一次操作中转换任意数量的文件。
• 私有化

Easy Data Transform界面展示

软件布局

整个软件的布局如下图所示：

left pane 的一些组件介绍

组件主要有：

• input 输入
• Transform 转换
• Output 输出
  他们的组件组合非常灵活，能应对大部分数据处理情景
  

Transform菜单	中文	详细
Case	大小写转换	更改一列或多列中文本的大小写
Chop	切除	从一列或多列的开始或结束处删除字符。
Clone	克隆	生成输入数据集的精确副本。
New Col	创建定值列	添加一个新列，用给定值填充。
Remove Cols	移除列	移除所选列
Compare Cols	断言	创建一个新列，值是比较其他两列的结果（true|false）
Concat Cols	连接列	通过连接一个或多个现有列中的文本来创建新列。
Concat Rows	连接行	将多个连续行串联成一行。输入N则每N行连接为一行
Split Col	切割列	在选定列的分隔符处拆分文本以创建多个列或行
Split Rows	切割行	将每行拆分为多行。
Copy Cols	复制列	创建选定列的一个或多个副本。
Rename Col	列更名	此转换已弃用。改用Rename Cols
Rename Cols	列更名	更改列头名称
Reorder Cols	重排列	对列重新排序
Count	计数	统计所选列中每个文本项的出现次数。
Cross	组合	通过组合每个输入的每个可能行组合来创建输出。 例如，如果第一个输入有N1行，第二个输入有N2行， 那么结果将有N1 X N2行。也称为“笛卡尔积”或“交叉连接”。
Stamp	时间戳	将时间/日期戳添加为新行或新列
Date Format	日期格式化	更改一列或多列中的日期格式。
Num Format	数字格式化	更改一列或多列中的数字格式
Units	单位转换	改变时间、长度、温度等单位。
Decode	转码	转换为base64编码、URL编码、转义HTML或转义XML。
Dedupe	去重	删除重复行。
Extract	限长	在一列或多列中限制文本的长度，超出的部分删除
Fill	单元格填充	用下一个相邻的非空单元格填充选定列中的空单元格。 方向是填充到（而不是从）的方向。 例如：down 为上一个非空值填充到下方相邻的空单元格中
Spread	行转列	将一列展开为多个新列。也称为宽轴或交叉表。
Gather	列转行	将多个列聚集到新的键列和值列中。也称为unpivot、long pivot或group by。
Transpose	转置	交换（旋转）行和列，使每行成为一列，每列成为一行。对整体的转置
Header	标题	将数据集顶部的行移到标题中。
Filter	过滤	基于选定列中的数字、日期和文本值删除不符合的行。
If	条件赋值	根据一个或多个其他列中的值有条件地设置新列的值。
Lookup	查找	将要修改的数据集作为顶部输入，将要从中查找值的数据集作为底部输入
Interpolate	插值	根据离散函数逼近方式在另一个数据集中的数字采样值对为数据集插值，并将结果放入新列。
Join	连接	两个数据集合并，条件关联合并
Stack	并集	两个数据集合并，追加合并， 如果要按标题名称（例如，输入1中的“id”列与输入2中的“id”列） 和按列号（例如，输入1的第一列与输入2的第一列）对齐的列值，请选择“Header name”对齐）
Subtract	补集	从顶部数据集中移除键值在下部数据集中的行。
Intersect	交集	仅保留顶部数据集中的行，这些行的键值存在于较低的数据集中。
Unique	唯一	基于在选定列中仅保留唯一值来删除重复行
Insert	插入	向一列或多列追加/预加文本。
Pad	文本填充	将文本填充到一列或多列中的最小长度。
Replace	替换	替换一列或多列中的文本。
Substitute	值组装	将列值替换为文本。也可以称为 模板替换
Whitespace	空白符	整理所选列中的空白（空格、制表符、回车符等）。
Pivot	汇总	创建数据透视表以汇总一列或两列的值。
Stats	统计	按一个或多个选定列中的列或行计算数值的总和、最小值、最大值、平均值、中值、标准差或模式。
Summary	摘要	总结所选列中的值
Total	累计	添加一个新列，其中包含所选列的运行（累计）总计，还可以按某一列分组累计
Sample	子集	根据选择的规则提取一个数据的子集
Slice	切片	根据要保留还是删除切片来保留或删除连续的行。

join的详细解释

include bottom non-matching rows 为 “包含顶部非匹配行”，如果要在输出中包含顶部输入中的任何行，而底部输入中没有匹配值，请选择“包含顶部非匹配行”。

include top non-matching rows checked	include bottom non-matching rows checked	Also known as:
No	No	Inner join
No	Yes	Right outer join
Yes	No	Left outer join
Yes	Yes	Full outer join

要点

• Join并排（水平）合并两个数据集。要将一个数据集合并到另一个数据集之上（垂直），请使用Stack。
• 如果两个数据集中都有10k行或更少，Easy Data Transform将尝试根据列标题名称和内容猜测Top key列和Bottom key列的合理默认值。
• 所有值都被视为文本，比较区分大小写和空格。您可以使用Case来更改大小写和Whitespace，以便在交集之前删除空白。
• 如果一个键值在第一个数据集中出现了M次，在第二个数据集中出现了N次，那么您将得到M x N行的键值。可以使用Unique删除具有重复键值的行。
• 使用Concat Cols将多个列连接在一起（例如“first name”和“last name”列）以形成键列。
• 使用Row Num创建唯一的键列。
• 使用Sort对结果进行排序。
• 对交叉连接使用交叉变换。
• 对于在任一数据集中出现多次的键（重复项），将在“警告”选项卡中显示消息。
• “信息”选项卡中显示仅出现在2个数据集中的1个（未命中）中的键的消息。
• 级联多个联接以联接2个以上的数据集。

Whitespace处理空白符

操作按从上到下的顺序进行

• 选中要转换的列。
• 选中“修剪前导空格和尾随空格”以删除空格字符，如空格和制表符。
• 选中“用空格替换换行符”以用空格替换LF（\n）字符。
• 选中“用空格替换制表符”以用空格替换制表符（\t）字符。
• 选中remove carries returns可删除CR（\r）字符。
• 选中“将连续空间转换为一个空间”可将两个或多个连续空间替换为单个空间。
• 选中remove non-printable characters可删除Unicode类型的字符Other.*。这包括ASCII码0到31，例如制表符、换行符、回车符、bell和backspace。它不会删除空格。

常用的变量

变量信息	说明	示例
$(ComputerName)	计算机的名称	MyComputer
$(CurrentDate)	ISO格式的当前日期。	2020-08-18
$(CurrentDateTime)	当前日期时间，ISO格式。	2020-08-18T18:00:00
$(DataColumns)	数据集中的列数（不包括元数据）	10
$(DataRows)	数据集中的行数（不包括元数据）	10,000
$(DataValues)	数据集中的列数x行数（不包括元数据）	100,000
$(FileCreatedDate)	以ISO格式创建文件的日期。仅可用于文件输入。	2020-08-18
$(FileCreatedDateTime)	以ISO格式创建文件的日期时间。仅可用于文件输入	2020-08-18T18:00:00
$(FileName)	文件名，包括其扩展名。仅可用于文件输入。	myfile.csv
$(FilePath)	文件的完整路径（位置）。仅可用于文件输入。	C:\users\andy\Documents\myfile.csv
$(FileSizeBytes)	文件的大小（字节）。仅可用于文件输入。	1,234,567
$(FileUpdatedDate)	上次更新文件的日期，ISO格式。仅可用于文件输入。	2020-08-18
$(FileUpdatedDateTime)	上次更新文件的日期时间，采用ISO格式。仅可用于文件输入。	2020-08-18T18:00:00
$(UserName)	用户的名称（来自USER或USERNAME环境变量）。	Andy

JavaScript脚本编写中 的列的引用

有些转换允许您使用列变量来使用同一行上的列的值。列值可以被引用：

• 按列标题名称，例如“$(列名) ”列的 列名 ；或者
• 按列索引，例如，第一列为 $(1)。

要点：

• 列名区分大小写。
• 列名开头或结尾的空格将被忽略。
• 如果多个列具有相同的名称，则将使用左侧的第一个列。
• 按名称引用优先于按索引引用。例如，如果有一列名为“1”，那么 $(1) 将引用该列，而不是第一列。

正则表达式的应用

Easy Data Transform允许在Filter、If、Replace、Slice和Split Rows转换中使用正则表达式。它也是Javascript转换中Javascript语言的一部分。

正则表达式是一种强大的方式来匹配文本中的模式（包括日期和数字的文本表示）。例如，可以在Replace转换中使用正则表达式来交换名字和姓氏：

将当前的转换应用为批处理

要将当前转换模板文件应用于多个输入文件，请选择“File>Batch Process… .”。“批处理”窗口将出现，其中每个输入项有一列，每个输出项有一列。每个项目的别名显示在列标题中。
注：

• 所有输入和输出项都必须有别名。
• 输出项不能与另一个输出或输入项具有相同的别名。
• 不显示写入模式为禁用的输出项。

单击〖增加〗按钮，增加一个新的处理行。
单击“删除”以删除选定的处理行。
单击“清除”删除所有处理行。

在上图（粉色）输入列中，可以使用*和？文件名、文件扩展名和Excel工作表名称的通配符。例如。：

Input	Description
C:\Users\andy\Documents*.csv	Documents文件夹中所有扩展名为.csv的文件
C:\Users\andy\Documents\d?.csv	Documents文件夹中名为“d”加上一个字符的所有文件
C:\Users\andy\Documents\data.xlsx[*]	Documents文件夹中data.xlsx中的所有工作表
C:\Users\andy\Documents*.xlsx[data*]	Documents文件夹中所有.xslx文件中以“data”开头的所有工作表

注：

• 如果有多个输入列指定多个文件或工作表，则将为行中每个可能的输入文件/工作表排列创建一个输出。例如，第1列的3个输入文件x第2列的4张表=要处理的12个输出。

• Excel工作表名称不区分大小写。

• 不能对文件夹名称使用通配符。

• 批处理将忽略子文件夹中的文件。

• 输入到输入项或从输出项输出的所有文件应与原始文件类型相同。

在（上图的绿色标注的位置）输出列中，可以使用以下变量创建输出文件名。

Output 变量	含义	示例
{<input alias>}	列中正在处理的具有相应别名的输入文件的名称。	如果输入别名“orders”使用文件“C:\Users\andy\Documents\orders\u 2020.csv”，则将“{orders}”替换为值“orders\u 2020”。如果输入别名“orders”使用文件“C:\Users\andy\Documents\orders\u 2020.xlsx”和表“Sheet1”，则将“{orders}”替换为值“orders\u 2020\u Sheet1”。
{date}	日期处理以年_月_日格式进行	2020_04_18
{time}	时间处理以小时_分钟_秒_毫秒格式执行	15_21_56_599
{datetime}	日期/时间处理以年_月_日_时_分_秒_毫秒格式执行	2020_04_18_15_21_56_599

Easy Data Transform接受以下命令行参数：

Argument	含义
	要在启动时打开的.transform文件。
-cli	一旦对打开的文件的任何处理完成，就关闭应用程序。
-file =	将具有给定别名的输入或输出文件设置为指定的位置（路径）。输入的Excel文件应包括工作表名称，例如file.xlsx[sheet]。输出Excel文件可以选择包含图纸名称。文件类型应与原始文件相同。
-new_window	不要加载上次打开的.template文件，即使在“首选项”中选中了“启动时打开上一个文件”。
-verbose	向终端输出附加信息。

这允许您在批处理模式下处理.transform文件。例如：

要运行C:\Users\andy\Documents\myfile 1.transform，将输出与alias output1输出转换为C:\Users\andy\Documents\data1.csv：

C:\Program Files(x86)\EasyDataTransform\uv1\EasyDataTransform.exe "C:\Users\andy\Documents\myfile 1.transform" -file output1=C:\Users\andy\Documents\data1.csv -cli

要运行C:\Users\andy\Documents\myfile2.transform，请使用别名input1输入的输入，而不是从C:\Users\andy\Documents\data 2.xlsx的工作表1转换：

C:\Program Files(x86)\EasyDataTransform\u v1\EasyDataTransform.exe "C:\Users\andy\Documents\myfile2.transform" -file "input1=C:\Users\andy\Documents\data 2.xlsx[sheet1]" -cli

在任何带有空格的参数周围放置引号（""）（如上面的示例所示）。

要按计划执行此操作，请从调度程序（如 Windows 任务计划程序）调用 .bat 文件。

快捷键

Key	快捷方式	Action
A	Ctrl+A	在中间窗格中全选。
B	Ctrl+B	显示“批处理”窗口。
D	Ctrl+D	复制中心窗格中选定的分支。
I	Alt+I	从文件输入。
	Alt+Shift+I	从剪贴板输入。
N	Ctrl+N	新建 .transform 文件。
O	Ctrl+O	打开 .transform 文件。
	Ctrl+Shift+O	显示“最近打开”窗口。
	Alt+O	输出到文件。
S	Ctrl+S	保存.转换文件。
T	Ctrl+T	打开/关闭两屏模式
U	Ctrl+U	在明暗主题之间切换用户界面（如果可用）。
Y	Ctrl+Y	重做
Z	Ctrl+Z	撤消
Del	Del	删除中心窗格中的选定项。
,	Ctrl+,	显示首选项窗口。
=	Ctrl+=	缩放中心窗格，使所有项目都适合。
+	Ctrl++	放大中心窗格。
-	Ctrl+-	缩小中心窗格。
←	Ctrl+←	将中心窗格选择从项移动到输入到其中的最高[1]项。
	Alt+←	将键盘焦点移到中间窗格。
→	Ctrl+→	将中心窗格选择从项移动到它输出到的最高[1]项。
	Alt+→	将键盘焦点移到右窗格。
↑	Ctrl+↑	将中心窗格选择从项目移动到最高[1]同级[3]。
↓	Ctrl+↓	将中心窗格所选内容从项移动到最低[2]同级[3]。
1…9	Ctrl+1…Ctrl+9	选择输入项1到9（基于中间窗格中的高度）。
	Alt+1…Alt+9	选择输出项1到9（基于中间窗格中的高度）。
F1	F1	显示帮助。
F11	F11	将右窗格项设置切换为全屏。仅当右窗格中有1项时有效。

[1] 最高=最靠近中心窗格顶部。

[2] 最低=最靠近中间窗格底部。

[3] 如果两个项目有来自同一项目的输入，或者两个项目都没有输入，则视为同级项目

也可以使用键盘在中心窗格中添加变换（ Transform ）操作。只需选择要将转换添加到的项，然后开始键入名称。仅显示包含键入字母的符合条件的转换（忽略空格）。

小技巧：如果要查看所有变换名称的列表，请在开始键入之前按空格键。可以使用 Del 或 Backspace 键撤消键入的字母。

最后

希望本文对爱好进步的你有帮助，祝你玩的愉快！！！