向 Elastic Search 中批量导入 Excel

---

ES 批量（bulk）导入文件，需要一行指令一行数据穿插，如何从Excel文件生成 ES 的批量导入文件？用 Python 可以很简单的实现。

Elastic Search 的批量导入需要使用 Json 文件。如果我们需要导入的数据保存在 Excel 中，我们需要脚本将 Excel 转换输出为 Elastic Search 所需的 Json 文件。本文介绍一种转换方法。

Excel 的例子

我们以下面的 Excel 为例开始我们的文章：（此处可以下载：example.xlsx）

上面这个 Excel 包含了几类情形：

• 列名中包含空格
• 记录中包含空格和逗号

这些情形代表了一些常见的用途。

将 Excel 另存为 CSV

首先我们需要把 上一节中的Excel 另存为 CSV（CSV文件类型的解释可以参考 这篇文章），具体的做法是在Excel中选择另存为：

注意下拉菜单中选择 “CSV UTF-8“，以便后续处理。点击“确定”后会被问到一些警告信息：

选择“是”，我们就得到了以 “UTF-8“ 格式保存的 CSV 文件，内容如下：

序号,Model Name,操作系统,地址,金额
1,"iPhone 6s Plus, 黑色",iOS 10.3,杭州,4800
2,"iPhone 7 Plus, 红色",iOS 12.1,南京,5200
3,"iPhone XS Max, 银色",iOS 13.6,上海,8900

注意带逗号的项，比如 “iPhone 6s Plus, 黑色”，都自动被双引号括起来，以避免歧义。

使用 Python 读入 CSV

现在我们需要逐行读取 上一节 中得到的 CSV 文件，这篇文章 详细描述了如何用 Python 逐行读取 CSV 文件。这里我们直接使用这篇文章的方法。

实际读取过程中，需要解决两个问题：

中文编码问题

Python 2.x 对 UTF-8 的支持度不够好，所以不要跟自己过不去，直接用 Python 3 处理为上策，读取 CSV 代码如下：

import csv

with open('example.csv','r') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        print(row)

运行结果报错如下：

UnicodeDecodeError: 'gbk' codec can't decode byte 0xb7 in position 8: illegal multibyte sequence

报错的原因是，csv 文件在csv库函数中仍然被当做 GBK 编码解析，我们在 open() 的参数中指定 UTF-8 编码：

import csv

with open('example.csv','r', encoding='UTF-8') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        print(row)

得到这样的结果：

从图中看出，除了开头的一些乱码（ \ufeff ），大部分csv的信息已经正确的读出。证明在 这一节 中提到的几种情形都可以满足。现在要解决开头乱码问题。

UTF-8 with BOM

Excel 保存的 CSV 的编码格式是 UTF-8 with BOM，文件开头会有三个字节用于表示字节的大小端顺序（Byte Order），我们的脚本并不需要这三个字节，所以可以使用编辑器去除这三个字符，能方便完成此项任务的编辑器有：VS Code 和 Notepad++

• VS Code：

• NotePad++：

再次运行 这一节 中脚本的结果如下：

可以看出，Excel 的信息已经转换为我们想要的Json格式。

es批量导入的API描述在 这里 ，根据 这里的操作手册 ，我们需要得到合法格式的 Json文件。只要在 这里代码 的基础上，做少许修改，就能得到下面的代码：

import csv

i=0
with open('example.csv','r', encoding='UTF-8') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        i=i+1
        print('{"index": {"_index": "mobiles", "_type": "orders", "_id": "'+str(i)+'"}}')
        print(row)

执行结果如下：

这就是我们想要的用于 bulk 导入的 Json 文件。

实现批量导入

上节中 生成 Bulk 导入所需的Json文件后，可以使用下面的命令导入 ElasticSearch

$ curl \
    -X POST 'localhost:9200/mobiles/orders/_bulk?pretty' \
    -H "Content-Type: application/json" \
    --data-binary @import-es-example.json

除了 index 以外，es还有其他操作，比如 delete ， update 等，同样可以使用 这一节 中的脚本实现。