測試環境為 CentOS 7 x86_64 (虛擬機)
主要想把 Microsoft Excel 轉成 MariaDB 的資料庫 ,Excel 檔案需事前先轉成 CSV 格式或是 OpenDocument .ods 檔案格式(建議),除了可以透過 Mysql 指令 外還可以透過 PHPMyAdmin 來快速轉檔.
如果是單一檔案內容上傳至單一欄位可以使用 LOAD_FILE 函數,詳細使用請參考 – https://benjr.tw/101779 .
SQL 語法 LOAD DATA INFILE (Import)
以 Linux 的 /etc/passwd 為範例,可以利用 SQL 語法 LOAD DATA INFILE (Import) 來將檔案內容轉換成到資料庫.
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> USE dbtest; Database changed
先將資料庫的資料表 (table) 建立好,欄位則是依據 passwd 格式所定 Username , Password , UID , GID , IDInfo , HomeDir , Shell.
MariaDB [dbtest]> CREATE TABLE passwd ( Username CHAR(20) , Password CHAR(20) , UID INT(11) , GID INT(11) , IDInfo CHAR(100) , HomeDir CHAR(100) , Shell CHAR(20) ); Query OK, 0 rows affected (0.05 sec) MariaDB [dbtest]> DESCRIBE passwd; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | Username | char(20) | YES | | NULL | | | Password | char(20) | YES | | NULL | | | UID | int(11) | YES | | NULL | | | GID | int(11) | YES | | NULL | | | IDInfo | char(100) | YES | | NULL | | | HomeDir | char(100) | YES | | NULL | | | Shell | char(20) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 7 rows in set (0.00 sec)
現在透過 SQL 語法 LOAD DATA INFILE 把 /etc/passwd 轉入到資料庫,更多關於 LOAD DATA 請參考 – https://benjr.tw/103012
MariaDB [dbtest]> LOAD DATA INFILE '/etc/passwd' INTO TABLE dbtest.passwd FIELDS TERMINATED BY ':' ENCLOSED BY '"' LINES TERMINATED BY '\n'; Query OK, 46 rows affected, 10 warnings (0.00 sec) Records: 46 Deleted: 0 Skipped: 0 Warnings: 10
如果要指定欄位.
MariaDB [dbtest]> LOAD DATA INFILE '/etc/passwd' INTO TABLE dbtest.passwd FIELDS TERMINATED BY ':' ENCLOSED BY '"' LINES TERMINATED BY '\n' (Username , Password , UID , GID , IDInfo , HomeDir , Shell) ;
指令說明:
- LOAD DATA INFILE ‘/etc/passwd’
指定載入檔案位置,需注意權限問題 (Errcode: 2 – Error reading file) – https://dev.mysql.com/doc/refman/5.6/en/global-error-reference.html , 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限. - INTO TABLE dbtest.passwd
指定資料要輸入的 資料庫.資料表 - FIELDS TERMINATED BY ‘:’
資料的分隔字元,以 /etc/passwd 來說 : 就是下一個欄位的資料. - ENCLOSED BY ‘”‘
指定包圍欄位資料的符號. - LINES TERMINATED BY ‘\n’
資料的換行字元,代表接下來的資料是新的一筆紀錄 (Record). - IGNORE 1 ROWS;
如果當你的資料第一行為欄位名稱,使用該參數即可忽略第一行的資料.
檢視一下,可以看到剛剛輸入的資料.
MariaDB [dbtest]> SELECT * FROM dbtest.passwd LIMIT 5; +----------+----------+------+------+--------+----------------+---------------+ | Username | Password | UID | GID | IDInfo | HomeDir | Shell | +----------+----------+------+------+--------+----------------+---------------+ | root | x | 0 | 0 | root | /root | /bin/bash | | bin | x | 1 | 1 | bin | /bin | /sbin/nologin | | daemon | x | 2 | 2 | daemon | /sbin | /sbin/nologin | | adm | x | 3 | 4 | adm | /var/adm | /sbin/nologin | | lp | x | 4 | 7 | lp | /var/spool/lpd | /sbin/nologin | +----------+----------+------+------+--------+----------------+---------------+ 5 rows in set (0.00 sec)
這指令一樣可以直接在 Linux command Line 或是 Bash script 來執行 (需注意 ENCLOSED BY ‘”‘ 這個地方,因為 Linux script 無法直接使用 ” 須以 \” 來表示).
[root@localhost ~]# mysql -u root -p111111 -e "LOAD DATA INFILE '/etc/passwd' INTO TABLE dbtest.passwd FIELDS TERMINATED BY ':' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"
資料的確輸入了第二次.
[root@localhost ~]# mysql -u root -p111111 -e "SELECT * FROM dbtest.passwd WHERE username='root';" +----------+----------+------+------+--------+---------+-----------+ | Username | Password | UID | GID | IDInfo | HomeDir | Shell | +----------+----------+------+------+--------+---------+-----------+ | root | x | 0 | 0 | root | /root | /bin/bash | | root | x | 0 | 0 | root | /root | /bin/bash | +----------+----------+------+------+--------+---------+-----------+
PHPMyAdmin
如果不想用指令,可以用 PHPMyAdmin (支援多種格式如 OpenDocument Spreadsheet , Microsoft Excel 可另存為這種格式) 來轉換.關於 MySQL (MariaDB) 資料庫管理系統 PHPMyAdmin 的安裝請參考 https://benjr.tw/323
MyPHPAdmin 的匯入 ( Import )功能,受限於 PHP 上傳檔案 2M 的上限,可以手動修改,請參考 – https://benjr.tw/101989
在 主頁(Home)/資料庫(Databases)/新建資料庫(Create Database) 新建一個資料庫 (database),在新建的資料庫 (Linux) 導入 (import) 新資料表 (Table)
PHPMyAdmin 支援大量的轉檔格式有
- CSV – 用逗號 (,) 將值隔開的檔案格式 (在 Microsoft Excel 或是 Linux 下的 LibreOffice 都可以直接將檔按轉換成 csv,轉檔之後要注意編碼問題,透過 Windows 的筆記本需另儲存為編碼: UTF-8).
依據需要在 格式選項勾選 “使用檔案取代資料表資料” 選擇 欄位分隔符號,內容分隔符號, 內容跳脫符號 ,如果在資料的第一行為資料表名稱時,需要勾選 檔案第一行包含資料表的欄位名稱 (若未選此項,首行將被認為是資料),按下執行即可完成匯入. - MediaWiki 表 – WiKi 的資料庫格式
- OpenDocument Spreadsheet – OpenDocument 的格式( Linux 下的 LibreOffice 可以儲存的格式 .ods 之一).
ODS 檔案匯入時會依據 worksheet 名稱來選擇相對應的資料表來匯入,如有 勾選 檔案第一行包含資料表的欄位名稱 (若未選此項,首行將被認為是資料) 時會有兩種情況.- 已存在的資料表時會依據第一行資料表的欄位名稱來匯入.
- 不存在的資料表時會依據第一行資料表的欄位名稱來建立相對應的資料表欄位,然後把相對應的資料匯入.
按下執行即可完成匯入.
需注意檔你的檔案格式有超連結 (hyperlink) 時,匯入時無法把它轉成文字檔,須先把 hyperlink 格式移除. - ESRI Shape File – 美國環境系統研究所公司（ESRI）所開發的一種屬於空間資料開放格式.
- SQL – SQL 的資料庫
- XML – XML 檔案格式
如果資料表名稱需要做修改,可以在 資料表 / 操作 / 資料表選項 來進行名稱修改.
如果是資料欄名稱需要做修改,可以在 資料表 / 結構 / 修改 來進行名稱修改.
在剛轉好的資料會顯示
這個資料表中沒有可以辦識(unique)的資料欄位 將無法執行修改、複製、刪除等相關的功能。
我們需要在 資料表 / 結構 的其中之一的資料欄位設定成為 unique .或是 Primary Key (主鍵) – 必須為 not null (不能為空) 並且要 unique (唯一) .關於 Primary , Unique , Index Key 請參考 https://benjr.tw/98266
設定好之後就可以在 資料表/瀏覽/編輯 進行 資料 新增或是修改 .