将电子表格连接到云数据库的分步教程

WBOLT_COM 4 月 25, 2024 46 0

将电子表格连接到云数据库的分步教程

电子表格对于组织和管理客户数据很有价值，尤其是对于处理复杂关系较少的小型数据集的小型企业。

随着数据的增长和复杂程度的增加，需要访问数据的用户也越来越多，使用电子表格管理数据就变得非常低效。此外，观察和跟踪对电子表格所做的更改也更加复杂，往往会导致数据出现多个版本。

云托管数据库提供了一个访问、管理和组织数据的平台，从而实现了更好的数据管理。

本文演示了如何将流行的电子表格工具（Microsoft Excel 和 Google Sheets）连接到云托管数据库 MariaDB、MySQL 和 PostgreSQL，以改进数据管理。

准备工作

要学习本教程，请确保您具备以下条件：

已填充的 Google 表。我们提供了一个示例表供您用于本演示。
安装在 Google Sheet 上的 Coefficient。
安装了 Devart 插件的 Microsoft Excel 工作簿
安装了 pgAdmin4 和 MySQL Workbench。它们是与数据库交互的图形界面。

电子表格和数据库集成的要点

云托管数据库为企业提供数据库即服务（DBaaS），使其能够托管、部署和管理数据库，同时省去购买、配置和维护硬件所需的时间和资源。

其中一些数据库包括：

PostgreSQL – 一种强大的开源关系数据库，以其可靠性、可扩展功能和高性能而著称。它支持与众多工具和技术集成，可帮助您构建可扩展的应用程序。
MySQL – 一种流行的开源关系数据库，为用户构建 SQL 和 NoSQL 应用程序提供可扩展性、灵活性和可靠性。它提供了一个高性能、可用的数据库，能以经济的成本为关键业务应用提供支持。
MariaDB – MariaDB 是另一种开源关系数据库，可处理大量或少量数据，是大多数企业的可靠选择。虽然它与 MySQL 有许多相似之处，但它的可扩展性更强，查询速度更快，因此非常适合性能关键型工作负载。

云托管数据库具有自动备份、版本控制和灾难恢复等众多功能，可确保业务运营不中断。其他优势包括：

可扩展性
灵活性
业务灵活性
安全性
节约成本

准备和整理电子表格数据

新的电子表格数据可能包含错误，如重复数字、噪音、异常值和其他缺陷，这些都会降低数据质量并影响整合。

1. 准备数据

以下是一些为数据库集成组织和准备数据的方法：

使用模板 – Google Sheets 和 Excel 包含许多电子表格模板，可帮助加快数据格式化和整理。虽然找到一个适合你的业务用例的模板可能会感觉乏味或具有挑战性，但使用一个模板就能让你走上正确的道路。
格式化你的数据 – 格式化修改你的数据，帮助你可视化和理解数据。这一过程可能包括将一个复杂的表单拆分成多个表单，按字母或数字对列进行升序或降序排序以方便阅读，或更改单元格颜色以显示重要性。
数据清理 – 数据清理可去除异常值、重复值或特殊字符。它还可能涉及将单个文本列拆分为多列，以避免在整合过程中出现解析错误，或使用条件格式化来识别错误数据。
隐藏不必要的数据 – 有时，您的数据可能包含一些目前没有帮助但日后可能有价值的信息。Excel 和 Google Sheets 提供的功能可帮助您隐藏这些不必要的数据。

2. 为整合而构建数据

在为数据库集成准备电子表格时，以下是一些最佳做法：

记录元数据 – 元数据提供了有关当前数据结构及其来源的重要细节。记录元数据有助于确保准确映射所有数据点，从而成功实现数据库集成。
表示空值和零值 – 零值与空值不同，会影响数据质量。在准备整合数据表时，请准确记录零值，因为数据库可能会将其解释为空值，从而导致约束错误。
避免在字段名中使用特殊字符 – 从电子表格导入数据时，在列名中引入数字、特殊字符和其他 Unicode 字符可能会导致解析错误。命名字段时的最佳做法包括使用驼峰大小写（如 studentName）或下划线，使名称更具描述性。

数据结构确定后，您就可以将其与云数据库集成了。

如何与 MariaDB 集成：逐步过程

首先，创建 MariaDB 数据库（以 Kinsta 为例）。接下来，本指南将使用 Coefficient（一种用于导入电子表格数据的无代码连接器）将数据库实例连接到 Google Sheets。请务必安装此连接器。

将 MySQL 工作台连接到 MariaDB

首先，为你的 MariaDB 数据库实例提供外部连接详细信息。

打开 External connections 页面，复制 External hostname、Username、Password 和 Database name 字段。
外部连接页面显示连接外部主机所需的字段。在这里，连接 MySQL Workbench，它提供了与 MariaDB 实例交互的图形用户界面。通过添加新连接将 MySQL 工作台连接到数据库实例。
在 “Welcome to MySQL Workbench” 页面，单击左下角的 “MySQL Connection“。
在 “Setup New Connection” 页面，输入由 MariaDB 数据库实例提供的外部连接详细信息。
设置新连接页面显示外部连接详细信息。
单击页面底部的 Test Connection。出现关于服务器版本不兼容或非标准的连接警告。忽略该警告。现在您已将数据库实例连接到 MySQL Workbench。

接下来，使用以下 SQL 语句创建一个名为 diabetes_table 的表，并添加列。

CREATE TABLE `diabetes_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Pregnancies` varchar(45) NOT NULL,
`Glucose` int(11) NOT NULL,
`BloodPressure` int(11) NOT NULL,
`BMI` decimal(3,1) NOT NULL,
`DiabetesPedigreeFunction` decimal(4,3) NOT NULL,
`Age` int(11) NOT NULL,
`Outcome` tinyint(4) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3

将 Google Sheets 连接到 MariaDB

打开 Google Sheets。电子表格中已经包含了一个逗号分隔值 (CSV) 文件 (diabetes.csv)，共有七列。
Google Sheets 显示 diabetes.csv 文件。
单击 Extensions。
Google Sheets 菜单栏。
转到 Coefficient Salesforce、Hubspot Data Connector，然后单击 Launch。
Extensions 菜单。这一步将打开工作表右侧的 Coefficient connector，让你能在 Google Sheets 和 MariaDB 数据库之间导入和导出数据。
单击 Coefficient 中的 Export to，然后单击 MySQL。虽然你连接的是 MariaDB 数据库，但你点击的是 MySQL，因为 MariaDB 是 MySQL 的分叉。这意味着它是一个具有额外功能的 MySQL 数据库。
输入 MariaDB 实例提供的连接详细信息，然后单击连接 Connect。
Coefficient 显示连接到 MariaDB 所需的详细信息。
在 Source Data 部分，从 Tab 列表中选择 diabetes，从 Header row 列表中选择 Row 1。
Source Data 部分显示 Tab 和 Header 行字段。
在 Destination 部分，从 Table 列表中选择 Sheets-db diabetes_table。
从 Action 列表中选择 Insert，以插入电子表格数据。
Destination 部分显示 Table 和 Action 列表。在 Schemas 面板中，您将看到电子表格列。
显示电子表格列的 Schemas 面板。
将电子表格列映射到 MariaDB 表的标题，然后点击 Save。
Field Mappings 面板上的列映射到 MariaDB 表标题。
选择工作表中的特定行，然后单击 Next。
选择第 12 行测试映射，然后单击 Done selecting rows。
Google Sheets 表格显示选择了第 12 行。
单击在 MySQL 中插入 1 行，确认您的选择。电子表格现在有 Record ID 列、显示确定的 Result 列和显示导出时间的 Timestamp 列。
所选行已成功导出，并带有一些时间戳信息。
单击 “Done“。
现在，选择要导出的更多行。单击在 MySQL 中插入 X 行，然后单击 Done。
使用此查询在 MariaDB 表中显示导入的数据
```
SELECT * FROM <your_db_name>.diabetes_table;
```
注：上面代码块中的 <your_db_name> 代表数据库名称，因此请务必用实际数据库名称替换。

MariaDB 显示导入的数据。

将 Excel 表单连接到 MariaDB

确保您拥有 Devart 插件。该插件可让您将 Excel 表连接到 MariaDB，在 Excel 上导入和编辑数据，并将更改更新到数据库。插件自带安装指南。

打开空白 Excel 表。
点击顶部导航栏上的 Devart。如果安装了插件，就会看到 Devart 选项卡。
显示 Devart 选项卡的 Excel 表格。
单击 “Get Data” 打开 “Import Data Wizard“。
Devart 选项卡左侧显示获取数据按钮。
选择 MySQL 数据库作为 Data Source，然后输入 MariaDB 数据库详细信息以连接到该数据库。
Devart 选项卡显示左侧的 “Get Data” 按钮。
单击 “Test Connection“。出现 “Successfully connected” 消息。
单击 “OK“，然后单击 “Next“。
使用 Visual Query Builder 或自定义 SQL 查询，将 diabetes 表中的所有数据导入 Excel 工作表。
Import Data Wizard 显示将数据导入 Excel 工作表的自定义 SQL 查询。
单击 Finish。现在，您就有了一个包含云托管数据库数据的 Excel 表。
显示云托管数据库数据的 Excel 表。
要编辑和更新该工作表和数据库，请单击 Edit Mode。
Excel 表格显示 Devart 选项卡上 Edit Session 组中的 Edit Mode 按钮。如果在设置连接时选择不保存密码，则会提示输入数据库密码。
重新测试连接，确保输入密码后仍能连接。
选择两条新记录添加到数据库。
Excel 工作表显示两条新记录，以黄色突出显示。
单击 “Commit“，然后单击 “OK” 应用这些更改并将更改提交到 MariaDB 数据库。
执行查询，查看更新后的数据库。现在有两条新记录。
MariaDB 显示两条新记录。

建立与 PostgreSQL 的连接

在连接 Google Sheets 并将数据导入 PostgreSQL 数据库之前，必须建立可靠的连接，以确保数据导入过程无缝进行。

在 Kinsta 上创建 PostgreSQL 数据库，并使用连接详情连接图形用户界面（GUI）pgAdmin4。

与上一节一样，使用 Coefficient 将数据库实例连接到 Google Sheets。

将 Google 和 Excel 数据连接并导入 PostgreSQL

在 “Register – Server” 对话框中，提供 PostgreSQL 连接详细信息。详细信息包括：
- Hostname/address
- Port
- Maintenance database
- Username
- Password
PostgreSQL 连接详细信息。

使用下面的 SQL 语句为表 ID 值创建一个序列：

CREATE SEQUENCE IF NOT EXISTS public.diabetes_table_id_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 2147483647
CACHE 1
OWNED BY diabetes_table.id;

现在，创建一个名为 diabetes_table 的 PostgreSQL 表，其列的数据类型和限制条件与电子表格表一致。

CREATE TABLE IF NOT EXISTS public.diabetes_table
(
"Pregnancies" smallint NOT NULL,
"BloodPressure" smallint NOT NULL,
"BMI" numeric(3,1) NOT NULL,
"Glucose" smallint NOT NULL,
"DiabetesPedigree" numeric(4,3) NOT NULL,
"Age" smallint NOT NULL,
"Outcome" boolean,
id integer NOT NULL DEFAULT nextval('diabetes_table_id_seq'::regclass),
CONSTRAINT diabetes_table_pkey PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;

在 Google Sheets 中打开 diabetes.csv。
单击 Extensions，转到 Coefficient：Salesforce、Hubspot 数据连接器，然后点击 Launch。
接下来，要将电子表格数据导出到 PostgreSQL 数据库，请单击 Export to。
单击 PostgreSQL 旁的 Connect。
输入 PostgreSQL 连接详细信息，然后单击 Connect。

Coefficient 显示连接 PostgreSQL 所需的字段。
从 Tab 列表中选择 diabetes，从 Header row 列表中选择 Row 1，确定导出数据的方式。
Source Data 部分显示 Tab 和 Header row 列表。
从 Destination 部分的 Table 列表中选择 public.diabetes_table。
从 Action 列表中选择 Insert。
Destination 部分显示表格和操作列表。
将工作表的列映射到 PostgreSQL 表。
选择第二行，然后单击 “Done selecting rows“。
单击在 Insert 1 row in PostgreSQL 确认选择。电子表格现在有 Record ID 列、显示 OK 的 Result 列和显示导出时间的 Timestamp 列。
通过导出更多记录来测试集成。
执行查询以查看最近导入的数据
```
SELECT * FROM diabetes_table;
```
该查询显示 diabetes 表中的所有数据。.

连接 PostgreSQL 并将数据导出到 Excel

首先，您需要 PostgreSQL 的详细连接信息。

打开空白的 Excel 表单，点击 Devart。
点击 Get Data，打开 Import Data Wizard。
从数据源列表中选择 PostgreSQL database，然后在 Import Data Wizard 中输入连接详细信息以连接到数据库。

Import Data Wizard 会显示连接 MariaDB 所需的字段。
单击 “Test Connection” 检查连接是否成功。
选择对象并使用可视化查询来查询数据库。您可以使用 Visual Query Builder 或编写自己的自定义 SQL 查询来查询数据库。

Visual Query Builder 会显示 Objects 和 Filters 列表。
单击 Finish。您现在有了一个包含数据的 Excel 工作表。单击 Refresh，确保工作表是最新的。

单击 Devart 选项卡上导入组中的 Refresh 按钮。
单击 Yes 确认。
接下来，单击 Edit Mode 来编辑和更新此工作表和数据库。
在电子表格中添加新记录，然后单击 “Commit” 以提交更改。

Devart 选项卡上编辑会话组中的 “Edit Mode” 和 “Commit” 按钮。
现在，执行查询以查看更新后的数据库。您可以看到数据库中有一条新记录。