將電子表格連線到雲資料庫的分步教學

電子表格對於組織和管理客戶資料很有價值，尤其是對於處理複雜關係較少的小型資料集的小型企業。

隨著資料的增長和複雜程度的增加，需要訪問資料的使用者也越來越多，使用電子表格管理資料就變得非常低效。此外，觀察和跟蹤對電子表格所做的更改也更加複雜，往往會導致資料出現多個版本。

雲託管資料庫提供了一個訪問、管理和組織資料的平臺，從而實現了更好的資料管理。

本文演示瞭如何將流行的電子表格工具（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 資料庫例項提供外部連線詳細資訊。

1. 開啟 External connections 頁面，複製 External hostname、Username、Password 和 Database name 欄位。
   外部連線頁面顯示連線外部主機所需的欄位。在這裡，連線 MySQL Workbench，它提供了與 MariaDB 例項互動的圖形使用者介面。通過新增新連線將 MySQL 工作臺連線到資料庫例項。
2. 在 “Welcome to MySQL Workbench” 頁面，單擊左下角的 “MySQL Connection“。
3. 在 “Setup New Connection” 頁面，輸入由 MariaDB 資料庫例項提供的外部連線詳細資訊。
   設定新連線頁面顯示外部連線詳細資訊。
4. 單擊頁面底部的 Test Connection。出現關於伺服器版本不相容或非標準的連線警告。忽略該警告。現在您已將資料庫例項連線到 MySQL Workbench。
5. 接下來，使用以下 SQL 語句建立一個名為 diabetes_table 的表，並新增列。
   Plain text

   Copy to clipboard

   Open code in new window

   EnlighterJS 3 Syntax Highlighter

   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

   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

   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

1. 開啟 Google Sheets。 電子表格中已經包含了一個逗號分隔值 (CSV) 檔案 (diabetes.csv)，共有七列。
   Google Sheets 顯示 diabetes.csv 檔案。
2. 單擊 Extensions。
   Google Sheets 選單欄。
3. 轉到 Coefficient Salesforce、Hubspot Data Connector，然後單擊 Launch。
   Extensions 選單。這一步將開啟工作表右側的 Coefficient connector，讓你能在 Google Sheets 和 MariaDB 資料庫之間匯入和匯出資料。
4. 單擊 Coefficient 中的 Export to，然後單擊 MySQL。雖然你連線的是 MariaDB 資料庫，但你點選的是 MySQL，因為 MariaDB 是 MySQL 的分叉。這意味著它是一個具有額外功能的 MySQL 資料庫。
5. 輸入 MariaDB 例項提供的連線詳細資訊，然後單擊連線 Connect。
   Coefficient 顯示連線到 MariaDB 所需的詳細資訊。
6. 在 Source Data 部分，從 Tab 列表中選擇 diabetes，從 Header row 列表中選擇 Row 1。
   Source Data 部分顯示 Tab 和 Header 行欄位。
7. 在 Destination 部分，從 Table 列表中選擇 Sheets-db diabetes_table。
   
8. 從 Action 列表中選擇 Insert，以插入電子表格資料。
   Destination 部分顯示 Table 和 Action 列表。在 Schemas 面板中，您將看到電子表格列。
   顯示電子表格列的 Schemas 面板。
9. 將電子表格列對映到 MariaDB 表的標題，然後點選 Save。
   Field Mappings 面板上的列對映到 MariaDB 表標題。
10. 選擇工作表中的特定行，然後單擊 Next。
11. 選擇第 12 行測試對映，然後單擊 Done selecting rows。
    Google Sheets 表格顯示選擇了第 12 行。
12. 單擊在 MySQL 中插入 1 行，確認您的選擇。電子表格現在有 Record ID 列、顯示確定的 Result 列和顯示匯出時間的 Timestamp 列。
    所選行已成功匯出，並帶有一些時間戳資訊。
13. 單擊 “Done“。
14. 現在，選擇要匯出的更多行。單擊在 MySQL 中插入 X 行，然後單擊 Done。
15. 使用此查詢在 MariaDB 表中顯示匯入的資料
    Plain text

    Copy to clipboard

    Open code in new window

    EnlighterJS 3 Syntax Highlighter

    SELECT * FROM <your_db_name>.diabetes_table;

    SELECT * FROM <your_db_name>.diabetes_table;

    SELECT * FROM <your_db_name>.diabetes_table;

    注：上面程式碼塊中的 <your_db_name> 代表資料庫名稱，因此請務必用實際資料庫名稱替換。

    
    MariaDB 顯示匯入的資料。

將 Excel 表單連線到 MariaDB

確保您擁有 Devart 外掛。該外掛可讓您將 Excel 表連線到 MariaDB，在 Excel 上匯入和編輯資料，並將更改更新到資料庫。外掛自帶安裝指南。

1. 開啟空白 Excel 表。
2. 點選頂部導航欄上的 Devart。如果安裝了外掛，就會看到 Devart 選項卡。
   顯示 Devart 選項卡的 Excel 表格。
3. 單擊 “Get Data” 開啟 “Import Data Wizard“。
   Devart 選項卡左側顯示獲取資料按鈕。
4. 選擇 MySQL 資料庫作為 Data Source，然後輸入 MariaDB 資料庫詳細資訊以連線到該資料庫。
   Devart 選項卡顯示左側的 “Get Data” 按鈕。
5. 單擊 “Test Connection“。出現 “Successfully connected” 訊息。
6. 單擊 “OK“，然後單擊 “Next“。
7. 使用 Visual Query Builder 或自定義 SQL 查詢，將 diabetes 表中的所有資料匯入 Excel 工作表。
   Import Data Wizard 顯示將資料匯入 Excel 工作表的自定義 SQL 查詢。
8. 單擊 Finish。現在，您就有了一個包含雲託管資料庫資料的 Excel 表。
   顯示雲託管資料庫資料的 Excel 表。
9. 要編輯和更新該工作表和資料庫，請單擊 Edit Mode。
   Excel 表格顯示 Devart 選項卡上 Edit Session 組中的 Edit Mode 按鈕。如果在設定連線時選擇不儲存密碼，則會提示輸入資料庫密碼。
10. 重新測試連線，確保輸入密碼後仍能連線。
11. 選擇兩條新記錄新增到資料庫。
    Excel 工作表顯示兩條新記錄，以黃色突出顯示。
12. 單擊 “Commit“，然後單擊 “OK” 應用這些更改並將更改提交到 MariaDB 資料庫。
13. 執行查詢，檢視更新後的資料庫。現在有兩條新記錄。
    MariaDB 顯示兩條新記錄。

建立與 PostgreSQL 的連線

在連線 Google Sheets 並將資料匯入 PostgreSQL 資料庫之前，必須建立可靠的連線，以確保資料匯入過程無縫進行。

在 Kinsta 上建立 PostgreSQL 資料庫，並使用連線詳情連線圖形使用者介面（GUI）pgAdmin4。

與上一節一樣，使用 Coefficient 將資料庫例項連線到 Google Sheets。

將 Google 和 Excel 資料連線並匯入 PostgreSQL

1. 在 “Register – Server” 對話方塊中，提供 PostgreSQL 連線詳細資訊。詳細資訊包括：
   • Hostname/address
   • Port
   • Maintenance database
   • Username
   • Password

   
   PostgreSQL 連線詳細資訊。

2. 使用下面的 SQL 語句為表 ID 值建立一個序列：
   Plain text

   Copy to clipboard

   Open code in new window

   EnlighterJS 3 Syntax Highlighter

   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;

   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;

   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;

3. 現在，建立一個名為 diabetes_table 的 PostgreSQL 表，其列的資料型別和限制條件與電子表格表一致。
   Plain text

   Copy to clipboard

   Open code in new window

   EnlighterJS 3 Syntax Highlighter

   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;

   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;

   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;

4. 在 Google Sheets 中開啟 diabetes.csv。
5. 單擊 Extensions，轉到 Coefficient：Salesforce、Hubspot 資料聯結器，然後點選 Launch。
6. 接下來，要將電子表格資料匯出到 PostgreSQL 資料庫，請單擊 Export to。
7. 單擊 PostgreSQL 旁的 Connect。
8. 輸入 PostgreSQL 連線詳細資訊，然後單擊 Connect。

   
   Coefficient 顯示連線 PostgreSQL 所需的欄位。

9. 從 Tab 列表中選擇 diabetes，從 Header row 列表中選擇 Row 1，確定匯出資料的方式。
   Source Data 部分顯示 Tab 和 Header row 列表。
10. 從 Destination 部分的 Table 列表中選擇 public.diabetes_table。
11. 從 Action 列表中選擇 Insert。
    Destination 部分顯示錶格和操作列表。
12. 將工作表的列對映到 PostgreSQL 表。
13. 選擇第二行，然後單擊 “Done selecting rows“。
14. 單擊在 Insert 1 row in PostgreSQL 確認選擇。電子表格現在有 Record ID 列、顯示 OK 的 Result 列和顯示匯出時間的 Timestamp 列。
15. 通過匯出更多記錄來測試整合。
16. 執行查詢以檢視最近匯入的資料
    Plain text

    Copy to clipboard

    Open code in new window

    EnlighterJS 3 Syntax Highlighter

    SELECT * FROM diabetes_table;

    SELECT * FROM diabetes_table;

    SELECT * FROM diabetes_table;

    該查詢顯示 diabetes 表中的所有資料。.

連線 PostgreSQL 並將資料匯出到 Excel

首先，您需要 PostgreSQL 的詳細連線資訊。

1. 開啟空白的 Excel 表單，點選 Devart。
2. 點選 Get Data，開啟 Import Data Wizard。
3. 從資料來源列表中選擇 PostgreSQL database，然後在 Import Data Wizard 中輸入連線詳細資訊以連線到資料庫。

   
   Import Data Wizard 會顯示連線 MariaDB 所需的欄位。

4. 單擊 “Test Connection” 檢查連線是否成功。
5. 選擇物件並使用視覺化查詢來查詢資料庫。您可以使用 Visual Query Builder 或編寫自己的自定義 SQL 查詢來查詢資料庫。

   
   Visual Query Builder 會顯示 Objects 和 Filters 列表。

6. 單擊 Finish。您現在有了一個包含資料的 Excel 工作表。單擊 Refresh，確保工作表是最新的。

   
   單擊 Devart 選項卡上匯入組中的 Refresh 按鈕。

7. 單擊 Yes 確認。
8. 接下來，單擊 Edit Mode 來編輯和更新此工作表和資料庫。
9. 在電子表格中新增新記錄，然後單擊 “Commit” 以提交更改。

   
   Devart 選項卡上編輯會話組中的 “Edit Mode” 和 “Commit” 按鈕。

10. 現在，執行查詢以檢視更新後的資料庫。您可以看到資料庫中有一條新記錄。

小結

雲託管資料庫提供了一個協同工作空間，允許您儲存、訪問、建立和管理資料的動態關係。

通過一些雲伺服器，您可以啟動 PostgreSQL 和 MySQL 資料庫例項，並使用所提供的連線詳細資訊連線到電子表格。有了這種連線，您就可以建立資料庫表，將電子表格欄位對映到雲資料庫的欄位，並開始匯出資料。