土办法将数据迁移导云数据库

[TOC]

约课小程序开发差不多了，要开始做数据迁移了。原先的约课系统是用云表格（伙伴办公）实现的。首先看一下官方文档：云数据库目前仅支持导入 CSV、JSON 格式的文件数据。有云开发控制台和HTTP API两种导入方式。伙伴办公可以导出数据为Excel文件。Excel导出为CSV很方便，所以首先尝试Excel转CSV方式，再用云开发控制台导入的方式。

Excel转CSV导入

CSV的字符集问题

Excel转CSV很简单，点几下鼠标就完事儿了。先从云表格中导出Excel文件，把表头改为目标数据对象的属性名。在Excel中导出为CSV文件。这时候遇到了一个小坑，没有注意字符集编码，直接导出了csv文件。然后折腾半天encoding问题。想i想不对呀，不能这么麻烦呀！又回过头去研究Excel，如果直接导出utf-8。果然，在导出文件格式中发现了“CSV UTF-8”这个类型。选择导出为“CSV UTF-8”，第一个小坑就算出来了。

CSV的数据类型问题

把Excel转成了csv，在开发者工具中导入集合，成功？导入是成功了，但是遇到了数据类型问题：csv是无类型的——都是文本型，处理程序自行处理（尝试转换为正确的数据类型。然而，什么是正确的类型不应该我来定义吗？实际上，我没的选择。主要是两种类型：

• 像数字的文本，比如手机号码。不论咋折腾，比如手机号加引号，导入工具都勤勤恳恳的把手机号码转成数值类型。
• 日期时间。读了文档，似乎通过csv导入是不能导入日期时间类型的——都作为普通的字符串了。

Tips

csv可以处理嵌套对象。只要把第一行的列名（键名）写为路径的形式（如"user.name"），导入时就导入成了嵌套对象的属性。

所以，用CSV导入的方法放弃。接下去尝试通过json导入。

Excel转JSON导入

有了CSV的尝试，在JSON导入前，重点先弄清楚几个问题：

• JSON文件格式的要求
• 如何导入日期时间类型数据
• 如何导入包含嵌入对象的数据

JSON格式的要求

官方文档说：

• JSON 数据不是数组，而是类似 JSON Lines，即各个记录对象之间使用 \n 分隔，而非逗号；

先去学习一下什么是 JSON Lines。

JSON Lines

JSON Lines官网的定义：

This page describes the JSON Lines text format, also called newline-delimited JSON. JSON Lines is a convenient format for storing structured data that may be processed one record at a time. It works well with unix-style text processing tools and shell pipelines. It’s a great format for log files. It’s also a flexible format for passing messages between cooperating processes.

简单来说就是把一个json对象拍平到一行——一个对象一行；多个对象之间用"\n"分隔，而不用“,”分隔。

Excel转JSON Lines

基本明白JSON Lines是什么之后，怎么把Excel转成JSON Lines格式呢？Pandas呀！动手，打开Jupyter，5行代码“搞定”：

import pandas as pd
df = pd.read_excel("xxxx.xlsx")
file= open(r'test.json', mode='w+', encoding='UTF-8')
df.to_json(file, orient="records", lines=True, force_ascii=False, date_format="iso")
file.close()

所谓5行代码，实际上Bing了半天。其中，有篇Blog写的挺好，可以参考：
Python convert normal JSON to JSON separated lines 3 examples

BUT……又遇到了新问题：

• 日期时间类型的数据无法正确导入（导入后是字符串类型）
• 内嵌对象没有办法“一键生成”（后面会详细说明一下）

其实也不是真没有办法——有什么是Python搞不定的呢。主要的问题是我不会呀。我的Python水平（事实上用水平这个词有点夸张了）是基本看得懂别人写的代码、简单的可以改一改这样的程度，Pandas只是之前大概看了几眼，知道它是干啥的。不为难自己，时间也不够，等我把pandas搞明白又要好几天了。所以，多年没写过程序的土人祭出终极武器——Excel！

土人的终极解决方案——Excel

用Excel生成批量脚本已经不是第一次干了，之前也帮忙ETL同事搞过复制几千万小文件的事情。放下面子，什么都好办了。毕竟能抓住老鼠的猫都是好猫，能解决问题的工具都是好工具。认怂以后，事情就简单了。在Excel里拼出JSON Line其实很容易（前提是数据量小，数据量大还是老老实实走正途），一些公式、拖拉复制、拷贝黏贴，很快就搞定了。

言归正传，分享一下怎么解决前面的两个问题吧。

如何处理日期时间

官方文档给出了例子：

• 时间格式须为 ISODate 格式，例如"date": { "$date" : "2018-08-31T17:30:00.882Z" }

开始没有看明白date和$date分别是啥意思。索性在数据库里插入一个带日期字段的对象，然后导出来看看。

{"_id":"3adec2825f2d0","createTime":{"$date":"2020-08-07T07:56:17.409Z"}}

这样就看懂了，把日期类型的属性（比如createTime）作为内嵌对象，内嵌对象中包含一个名为$date的字串属性，值是ISO时间表示法格式的字符串。要去学习一下ISO的日期时间格式

ISO时间格式

看文档：W3CSchool：关于JavaScript 日期格式化

简单来说，

• 日期部分：YYYY-MM-DD
• 时间部分：hh:mm:ss.ms
• 用T分隔日期和时间
• 时区：UTC用Z表示；其他时区用±时间偏移量表示

Javascript实验一下就一目了然了：

• UTC时间

var d = new Date("2019-05-02T12:00:00Z");
// Thu May 02 2019 20:00:00 GMT+0800 (中国标准时间)

• 中国标准时间

var d = new Date("2019-05-02T12:00:00+08:00");
// Thu May 02 2019 12:00:00 GMT+0800 (中国标准时间)

EXCEL的格式化日期时间

接下来就是在Excel里去拼接出时间属性。第一步先用TEXT函数把EXCEL里的日期格式化为文本

TEXT(I2, "yyyy-mm-dd""T""hh:mm:ss""+08:00""")

接着，用CONCAT函数拼出属性：

=CONCAT("""expireTime"": {""$date"": """, TEXT(Y2, "yyyy-mm-dd""T""hh:mm:ss""+08:00"""), """},")

然后就得到了想要的JSON“属性”了：

“expireTime”: {"$date": “2019-09-02T19:47:40+08:00”},

如何导入嵌套对象

官方文档中有一段特别坑的描述：

• JSON 数据每个键值对的键名首尾不能是 .，例如 “.a”、“abc.”，且不能包含多个连续的 .，例如 “a…b”；
• 键名不能重复，且不能有歧义，例如 {“a”: 1, “a”: 2} 或 {“a”: {“b”: 1}, “a.b”: 2}；

给人一种错觉是可以通过键名来处理嵌套对象（"a.b": 2）。然而只是一种错觉，实际上云开发控制台导入工具只是把a.b作为了普通的属性名，导入了一个诡异的文档。

• 导入的文件

{"b": {"b": 1}, "a.b": 2}

• 导入结果

{
    "_id": "cc2411ac5f2cccad",
    "b": {
        "b": 1
    },
    "a.b": 2
}

实在不理解为什么文档中要举这么一个例子（求解）。不管了，反正结论是老老实实按照标准的JSON去写！

• 在最前面插入一列，值填充 {
• 嵌套对象的属性，同样插入一列，值填充"some_property":{
• 嵌套对象结束后，插入一列，值填充}
• 结尾插入列，值填充 }

所有的属性都拼接完成后，把所有行都复制相同的公式。

最后一步，“导出”成JSON Lines。很简单，选中所有行列，ctrl+c，在notepad或者vs code里面贴进去，并保存为json文件。到这里基本就算大功告成了，剩下的就是去云开发控制台去导入数据了。

校验JSON的合法性

还是单纯了，导入时出错了：

invalid character ‘Â’ looking for beginning of value

仔细检查了好几次Excel里的公式也没有看出什么问题。从报错上看，肯定是包含了什么特殊的不可显示字符。把json贴近VS CODE里面格式化一下，看着挺正常的，也没有看到报错。

无奈，去网上找了个json验证工具，BEJSON的JSON验证工具。把json文本贴进去，果然有错误。看了一下报错的位置，是个“空格”。这段是从云开发控制台导出来的呀！不管了，一删了事。再去导入，终于成功了。

结语

纠结了好几天的迁移数据问题，最后还是靠Excel搞定的，哎！不过还是那句话，能抓住老鼠的猫都是好猫，能解决问题的工具都是好工具。把整个的学习过程记录分享给大家做个参考。