989 瀏覽數

MariaDB 大量資料轉檔

主要想把 Microsoft Excel 轉成 MariaDB 的資料庫 ,Excel 檔案需事前先轉成 CSV 格式或是 OpenDocument .ods 檔案格式(建議),除了可以透過 Mysql 指令 外還可以透過 PHPMyAdmin 來快速轉檔.

mysql 指令

透過 mysql 指令,需要先建立好資料庫 Database 與資料表格 table ,我的資料表為 Title , Detail ,Solution 各 20 個字元.

root@ubuntu:~# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database Linux;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Linux              |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use Linux;
Database changed
MariaDB [Linux]> create table Ubuntu (Title char(20),Detail char(20),Solution char(20));
Query OK, 0 rows affected (0.01 sec)

MariaDB [Linux]> show tables;
+-----------------+
| Tables_in_Linux |
+-----------------+
| Ubuntu          |
+-----------------+
1 row in set (0.00 sec)

接下來就是將 CSV 檔案的資料導入到資料表格內.

MariaDB [Linux]> LOAD DATA INFILE '/var/lib/mysql/Linux/Ubuntu.csv' INTO TABLE Ubuntu FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
Query OK, 5 rows affected, 10 warnings (0.02 sec)    
Records: 5  Deleted: 0  Skipped: 0  Warnings: 10
  • LOAD DATA INFILE ‘/var/lib/mysql/Linux/Ubuntu.csv’
    CSV 檔案位址,如果出現了 Can’t get stat of ‘/root/Ubuntu.csv’ (Errcode:13 “Permission denied”) 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限.
  • INTO TABLE Ubuntu
    讀取的檔案要寫入 Ubuntu 資料表格內.
  • FIELDS TERMINATED BY ‘,’
    資料是以 , (逗號)為區隔.
  • ENCLOSED BY ‘”‘
    資料多於一行時會以 ” (引號) 表示為同一欄位.
  • LINES TERMINATED BY ‘\n’
    同一欄位的換行會以 \n (newLine) 做區隔.
  • IGNORE 1 ROWS;
    因 CVS 的第一行是資料表的欄位名稱,前面已經定義好,所以不需要這內容.

資料表匯入後就可以使用 select 來查詢了.

MariaDB [Linux]> select * from Ubuntu;
+---------+-------------+----------------+
| Title   | Detail      | Solution       |
+---------+-------------+----------------+
| A       | AAA Test    | AAA Solution   |
| B       | BBB Test    | BBB Solution   |
| C       | CCC Test    | CCC Solution   |
| D       | DDD Test    | DDD Solution   |
| E       | EEE Test    | EEE Solution   |
+---------+-------------+----------------+
5 rows in set (0.01 sec)

PHPMyAdmin

關於 MySQL (MariaDB) 資料庫管理系統 PHPMyAdmin 的安裝請參考 http://benjr.tw/323

在 主頁(Home)/資料庫(Databases)/新建資料庫(Create Database) 新建一個資料庫 (database),在新建的資料庫 (Linux) 導入 (import) 新資料表 (Table)

PHPMyAdmin 支援大量的轉檔格式有

  1. CSV – 用逗號 (,) 將值隔開的檔案格式 (在 Microsoft Excel 或是 Linux 下的 LibreOffice 都可以直接將檔按轉換成 csv)
    依據需要在 格式選項勾選 “使用檔案取代資料表資料” 選擇 欄位分隔符號,內容分隔符號, 內容跳脫符號 ,如果在資料的第一行為資料表名稱時,需要勾選 檔案第一行包含資料表的欄位名稱 (若未選此項,首行將被認為是資料),按下執行即可完成匯入.
  2. MediaWiki 表 – WiKi 的資料庫格式
  3. OpenDocument Spreadsheet – OpenDocument 的格式( Linux 下的 LibreOffice 可以儲存的格式 .ods 之一).
    需要勾選 檔案第一行包含資料表的欄位名稱 (若未選此項,首行將被認為是資料),按下執行即可完成匯入.
  4. ESRI Shape File – 美國環境系統研究所公司(ESRI)所開發的一種屬於空間資料開放格式.
  5. SQL – SQL 的資料庫
  6. XML – XML 檔案格式

如果資料表名稱需要做修改,可以在 資料表 / 操作 / 資料表選項 來進行名稱修改.
如果是資料欄名稱需要做修改,可以在 資料表 / 結構 / 修改 來進行名稱修改.

在剛轉好的資料會顯示

這個資料表中沒有可以辦識(unique)的資料欄位 將無法執行修改、複製、刪除等相關的功能。

我們需要在 資料表 / 結構 的其中之一的資料欄位設定成為 unique .或是 Primary Key (主鍵) – 必須為 not null (不能為空) 並且要 unique (唯一) .關於 Primary , Unique , Index Key 請參考 http://benjr.tw/98266

設定好之後就可以在 資料表/瀏覽/編輯 進行 資料 新增或是修改 .

發表迴響