使用 Vim 处理未格式化的数据

• 下载源文件 - 321.1 KB

这是一个关于未格式化数据处理的演示。尽管本例使用了 Vim，但编辑器的选择是个人偏好问题。

平台

本例是在运行 LinuxMint 的 Linux 机器上开发的。SQLITE 是本演示的数据库引擎。我的个人电脑是一台 Linux 机器，但我也在 Windows 10 机器上工作。

在 Windows 10 上运行此示例的建议方式是替换 Cygwin Bash 命令行，但需要进行一些配置。SQL Server 可以代替 SQLITE。我还没有使用 Windows 的 Linux 子系统，但我希望将来能使用它。如果使用 Cygwin Vim 构建，bash 命令应该与 Linux 相同。如果读者使用标准的 Windows GVim 构建，最好在 Vim 外部命令前加上 C:\cygwin64\bin\。Windows 版 GVim 与 Cygwin Bash 配合得很好，但最好将环境变量设置为指向各种 Cygwin bin 目录。请注意，附录中包含如何转换示例插入语句以在 SQL Server 上运行生成 SQL 的说明。

我日常在 GVim 中进行的大部分工作是在 Microsoft Windows 机器上，使用 Visual Studio C# 编程，以及为 SQL Server 编写 TSQL。这个演示会因为工作内容必须保密而显得像一个典型的课堂示例。不过，我在 Windows 上使用的 Bash 比在 Linux 上还多。

Vimrc 设置建议

如果你是 Vim 专家，愿原力与你同在！否则，我是否可以谦虚地建议在你的 Vimrc 文件中进行以下设置，以确保演示配方能在你的机器上成功运行？

:set ignorecase
:set linebreak
:set incsearch
:set showmatch
:set nowrapscan

背景

程序员经常需要为各种任务配置数据。特定的任务可能来自网站、电子表格、电子邮件或许多其他地方。

尽管你个人的技术经验无疑与我大相径庭，但相当一部分时间可能花费在处理这些其他数据上。需要格式化的数据未必是你的系统或程序能理解的格式。尽管如此，圆的钉子必须装进方的孔里，而你就是必须完成这件事的人。

本例从网页抓取数据并将其放入 SQLite 表，但这些未格式化文本处理技术跨越了特定的编程语言以及完成项目所需的各种非语言任务。

免责声明：本演示将使用来自劳工统计局（美国政府）的数据。将提供一个数据文件——以防网页将来发生变化，以便读者能够完成演示。

使用代码

创建一个用于进行破坏性编辑的临时目录。最好重新创建一个。该目录内的所有文件都应被视为可消耗的。在浏览器中打开以下网页：消费者价格指数 (https://www.bls.gov/cpi/tables/relative-importance/home.htm)。查看页面源代码，并将文件保存为 links__A.lisp。

抓取网站数据

在 GVim 或 Vim 中打开 links__A.lisp。加载 helper.vim 以进行美化。Register A 也已加载了网站根目录。

:source helper.vim

helper.vim 中重要的行是加载寄存器。这将在稍后用到。

:let @a = 'https://www.bls.gov'

如果读者希望在不逐行构建的情况下构建 wget 语句，只需加载 lesson_a_.vim。请注意，在加载 lesson_a_.vim 之前应先加载 helper.vim。

:source lesson_a_.vim

让我们逐行遍历 lesson_a_.vim 以进行进一步解释。如果读者仔细查看 html 源代码，应该注意到有许多包含我们想要提取的重要政府数据的文本文件。检查代码会发现，以 .txt 结尾的文件是要下载到临时目录（应该是当前工作目录）的文件。其他行是不需要的。

:%g!/txt/d

您会注意到这是一个危险的操作，所以您总是想使用生产环境的副本而不是生产环境本身。我们正在寻找一些包含我们想要的 txt 文件的 href。观察：如果可能，找到一个非正则表达式表达式来只提取我们想要的行。虽然搜索中可以使用正则表达式字符，但必须进行转义。查找一个模式，可以看到在所有相关行中都有 'a href=' 这个模式。我们只需要地址片段。去除不必要的垃圾字符。

:%s/.*a href="//g

和

:%s/".*//g

网站需要前缀到所有行。由于网站地址包含特殊字符，我们希望以不需要转义正则表达式字符的方式添加前缀。网站根目录是 https://www.bls.gov/cpi/tables/relative-importance。字符串本身是通过 helper.vim 加载到 register A 中的。可以使用 normal 命令按原样为每一行添加前缀，而无需转义字符。

:%normal 0"aP

再次浏览我们渲染的网页。可以发现我们只有网址。这些网址没有对应的操作。需要下载文件以便将它们整合。首先，我们将其制作成真实的网址，然后我们可以渲染这些行来下载数据。在前缀文本后加上一个单引号，这样网址就不会被 Bash 错误地转义。

:%s/^/wget '/g

现在用一个单引号完成 wget 命令，以结束该命令。

:%s/$/'/g

让我们去除烦人的标题文本。自首次编写此脚本以来，网页已发生变化。

:%g/<!--/d

在末尾添加一个空行作为回车符。读者应注意，命令的唯一范围是最后一行。

:$s/$/\r/g

wget 命令已构建完成

读者应注意 Vim 缓冲区尚未保存，是否保存缓冲区将由用户决定。选择缓冲区中的所有内容，复制，然后打开 Bash 命令窗口。将缓冲区内容粘贴到命令窗口中。读者应注意命令将按顺序执行。下面是一个操作截图。

数据文件现已下载，数据提取可以开始。下面显示的是在 Emacs 中孤立显示的已下载的 .txt 文件。

第二部分：准备下载的文件以供收集

假定 .txt 文件已下载。用指向临时文件夹的 PWD 打开一个新的 Vim 实例。再次加载 helper.vim 文件。

:source helper.vim

可以通过在新的 Vim 会话中加载 lesson_b_.vim 来跳过第二部分。确保工作目录是临时目录。第一部分下载的数据文件也需要驻留在临时目录中。

:source lesson_b_.vim

这是演示的第二部分。本演示配置下载的文件以供收集。如果您经常更改目录中的所有文件，拥有一个通用的设置脚本是个好主意。危险！这是一个非常危险的操作，所以请务必确保您使用的是生产环境的副本而不是生产环境本身。这类操作只能在一次性目录中进行。我的设置脚本名为 aw.vim，虽然我没有将此脚本包含在压缩的工作文件中，但内容将在下方显示。读者应执行这些内容。

请注意，在脚本中，您会注意到一些寄存器已为将来使用而设置。I 寄存器已初始化，用于从参数列表中的文件拉取文本。Register C 包含 Vim 命令，用于从各种文件中拉取文本并将此文本收集到 Register I 中。由于这将是破坏性编辑，因此设置了 autowriteall 和 nomore 命令。nomore 命令将用户提示推迟到最后。默认情况下，参数列表的范围是当前目录中的所有文件。Register B 包含注释文本。脚本内容如下。

" set up for argdo. all files by default.
:let @i = ''
:let @b = 'register C has the command to load pasteboard from args'
:let @c = ':argdo :normal ggVG"Iy'
:set autowriteall
:set nomore
:args *

脚本操作当前目录中的所有文件——不包括子目录（:args *）。我们需要将操作限制在 .txt 文件上。虽然操作是在一次性目录中进行的，但没有必要马虎。我们需要一个虚拟文件作为应用于列表中所有文件的操作的后门。在 Windows 上，需要一个虚拟文件，因为所有操作通常会对除最后一个文件之外的所有文件执行。不知道 Vim 的这种行为是否也适用于 Linux Vim 构建。然而，在 Microsoft Windows 上，Vim 需要一个空的虚拟文件作为参数列表的后门——无论是 Windows 自己的 GVim 构建还是 Cygwin 版本。

:silent :!touch zz.zzz

和

:args *.txt zz.zzz

缓冲区的视图。:args 命令显示了在执行任何 :argdo 命令之前将要操作的一些文件。

现在是时候仔细查看一些文本文件了。似乎每个文本文件都有一个共同的模式。我们只关心包含价格数据的行。应丢弃非价格行，因为最终目的地是数据库中的表。我们想要的行的通用正则表达式如下。虽然可能存在其他正则表达式可以专门匹配这些行，但我们只需要一个有效的。在我们的例子中

\d\+\.\d\+\s\+\d\+\.\d\+

下面显示了如何使用此正则表达式的命令。请注意，argdo 列表的范围是所有文本文件以及空的 zz.zzz 文件。在每个文件中，删除除包含价格数据的行之外的所有行。在下面的图片中，显示了 args 列表中的一个随机文件。

:argdo :%g!/\d\+\.\d\+\s\+\d\+\.\d\+/d

破坏性编辑本身最多是一种有条理的过程，数据应以增量方式进行转换，采取微小步骤，并留下一个面包屑踪迹，以便可以轻松回溯。数据侦察之后应进行破坏性编辑。将目录分叉成一个派生临时目录并不罕见——最新的目录指向一个较早的目录，而那个目录又指向更早的目录，依此类推。只有当一个人发现自己走进了死胡同时，渐进式操作的好处才能显现出来。

我们再次浏览各种文本文件。观察结果是，有些信息非常详细是我们想要的，而有些摘要项则包含总计。非摘要行似乎在行首至少有 4 个空格。保留这些行，丢弃其余的。下面显示了使用 :lvim 命令查找以 4 个空格开头的行的结果。

:argdo :%g!/^    /d

如果我们查看文本文件，1987 年至 1996 年没有行。在现实世界的例子中，这些异常值最终需要单独处理，而不是与其他 txt 文件一起处理。在本演示中，我们将忽略它们。让我们继续清理这些文件。让我们修剪行开头和结尾的空格。首先从左边

:argdo :%s/^ *//ge

然后从右边。下面再次显示了 args 列表中的一个随机文件。请注意，运行 argdo 命令时，您应该以 zz.zzz 结束，因为它是最后一个执行命令的文件。这就是我选择随机文件展示的原因。

:argdo :%s/ *$//ge

让我们收集这些文件，但首先我们必须将年份注入到每个文件中。文件中剩余的记录最终将是 SQLite 表的插入语句。从文件名中提取年份，并将此年份注入到每条记录的开头。下面显示了在 args 列表上运行 :argdo 时的实际视图。

:argdo :%s/^/\t/ge | :%normal 0"%P

我们不需要文件名中的 .txt。让我们删除它。下面是 1987.txt 文件的示例视图。

:argdo :%s/\(^\d\|\)\.txt/\1/ge

让我们将所有文本合并到一个文件中。Register I 已初始化，现在将用于合并文本。

:argdo :normal ggVG"Iy

将此文本保存到文件 master_records_file__dat.dat

:tabe master_records_file__dat.dat

将 Register I 中的文本放入新缓冲区 master_records_file__dat.dat。下面显示了合并的收集记录，但包含空行。

:normal "iP

删除任何空白行和存根行，然后保存。

:%g/^$/d
:%g/\t$/d

我将把是否保存此文件留给读者。本演示配方的第二部分到此结束。

第三部分：生成 SQL 语句

可以通过在 master_records_file__dat.dat 文件中加载 lesson_c_.vim 来跳过第三部分。

:source lesson_c_.vim

第三部分将 master_records_file__dat.dat 文件转换为 SQL 插入语句。本演示配方配置收集的数据并生成插入语句。请注意，以下步骤没有固有的顺序。数据清理是任何类型数据处理中一项必要的任务。在理想情况下，SQL 语句中不会有单个撇号。让我们来处理一下。请注意，Vim 缓冲区设置为自动换行。

:%s/'/&&/ge

此时，让我们使用插入模板来构建 SQL 语句。此模板称为 insert_template__C.sql，应包含在本演示配方的压缩工作文件中。我认为在开始 Vim 组合之前编写所有潜在的数据目标模板是件好事。这可以防止未来的分心，让您能够专注于将数据转化为可行的代码。模板内容如下。

insert into year_price_info
(
    year,
    expenditure_category,
    cpiu,
    cpiw
)
values
(
    CAST('year_value' as text),
    CAST('expenditure_category_value' as text),
    CAST('cpiu_value' as real),
    CAST('cpiw_value' as real)
)

通常，模板会合并为单行以注入数据。注入完成后，可以稍后拆分行以进行外观上的调整。下面显示了合并行的命令。

:normal ggVGJ

渲染

insert into year_price_info ( year, expenditure_category, cpiu, cpiw) values ( CAST('year_value' as text), CAST('expenditure_category_value' as text), CAST('cpiu_value' as real), CAST('cpiw_value' as real))

在构建数据时，这些常见的操作类型会一遍又一遍地重复。这些步骤的顺序可能会有所不同，但用户最好学习如何执行每项任务。虽然 Vim 的功能远不止我下面展示的，但以下是构建和使用列表时最常见的未格式化数据处理任务。

1. 前缀选定的行
2. 后缀选定的行
3. 将字符串注入到选定的行中。在某个字符串之前或之后注入此字符串是首选方法。将模板文本嵌入到选定的行中进行进一步操作是很常见的。
4. 在字符串上分割行
5. 在字符串上连接行
6. 注入一个序列，其中计数在每行上都不同。

现在回到 master_records_file__dat.dat 文件。每个单引号都被加倍，以便数据能够正确插入。让我们首先将 SQL 插入模板的一部分添加到行前面。

:%s/^/insert or ignore into year_price_info ( year, expenditure_category, cpiu, cpiw) values ( CAST('/ge

您是否还记得我们在数据中的每一行创建的制表符？现在是时候使用它了。这将完成第一列并开始第二列。

:%s/\t/' as text), CAST('/ge

现在我们来看第三列。您会注意到，通过检查将成为第三列的内容，存在一个唯一的字符串——两个或更多个句点，并在列末尾有零到多个空格。由于句点是正则表达式字符，因此在使用搜索和替换操作之前必须对其进行转义。

:%s/\.\.\+ */' as text), CAST('/ge

对于最后一列，请注意至少有两个空格，并且通常有很多空格。可以使用正则表达式来实现。

:%s/  \+/' as real), CAST('/ge

最后但同样重要的是每个记录的后缀。

:%s/$/' as real));/ge

现在，每行都已完成一个完整的插入语句。假设每行都能轻松插入数据库是愚蠢的。现在是时候为不同的行编号并区分它们了。如果其中一个插入语句有问题，将会很容易看出是哪一行有问题。既然如此，让我们确保个别错误不会毁掉整个批次。这些有问题行可以单独处理。

对每行进行后缀处理将创建一个新行，其中包含我们想要的打印语句。这些不是完成的行，所以请注意用作模板文本的 xxxxxx 字符串。xxxxxx 将在后续步骤中渲染。

:%s/.*/select 'this is statement xxxxxx';\r&/ge

让我们初始化我们的计数器。在本演示中，将使用Register I。

:let @i = 0

让我们利用 g 的强大功能来影响编号。该向 Vim Tips Wiki 致敬，以获取有关如何使用此精彩技术的指导。这是 Power of g 的链接。我将 g 的这种形式视为一个两阶段命令。g 部分选择行，execute 部分指定在该行上要执行的操作。当 g 命令匹配一行时，光标实际上会放置在匹配的行上。如果对行进行列操作，则可能需要指定行的列。此 g 命令无法在 html 标记中正确显示，因此我将展示最终 g 命令的图像。

无法正确渲染的两个控制字符是 CTRL-R 和第二个是 Return 字符。这两个字符都可以通过按 CTRL-Q 然后键入特定控制字符来正确渲染。鉴于 g 语句包含无法通过 html 渲染的控制语句，我提供此作为解决方法，以便完成演示。如果读者是 Vim 新手，我推荐加载 Vim 脚本 gcommand_for_lesson_c_.vim。

:source gcommand_for_lesson_c_.vim

如果您有 Vim 经验，我推荐以下方法。

:tabe gcommand_for_lesson_c_.vim
:normal 0v$"ay

g 命令现已加载到您的 A 寄存器中。当您进入命令模式时，键入 CTRL-R 然后键入 a 以将正确的命令从 register A 粘贴到命令行。

有必要解释一下 g 命令。在所有包含 xxxxxx 的行上，执行两个命令——用管道字符分隔。

1. 递增 Register I 中的计数器
2. 用 Register I 中包含的计数替换 xxxxxx 字符串。
3. 请注意，替换命令的范围是当前行。
4. 当一行匹配时，g 命令会将光标置于包含匹配的行上。

附录：在 SQL Server 上运行示例 SQL

使用 master_records_file__dat.dat 的最终输出，运行下面显示的命令。文本目标将被更改为 char(100)。请注意，如果用户查找文件 create_price_index__sql_server_version.sql，可以找到 SQL Server 的表定义。在更改插入语句后，该示例应能在 SQL Server 上运行。

:%s/ as text/, char(100)/g

当然，鼓励用户根据需要加载文件 convert_sql_to_SQL_Server.vim。

:source convert_sql_to_SQL_Server.vim

附件

有关包含文件的清单，请参阅压缩附件中的 README 文件。