PostgreSQL COPY 匯入資料

今天來嘗試匯入資料的功能，這個超級方便，先想辦法下載資料吧！

打算用 行政院農業委員會資料公開平台下載動物認養資料（.csv）測試 https://data.coa.gov.tw/Query/AdvSearch.aspx?id=QcbUEzN6E6DL

如果用 INSERT 插入資料的陳述句，要寫上百條，上千條資料，苦惱、麻煩、不想做，還好有一個方便的功能 COPY 陳述句，這是 PostgreSQL專有的功能，可以匯入有分隔符號，一行一筆資料的檔案。

1. 準備好原始要匯入的檔案 今天以 .csv 當範例
2. 建立好資料表（因為PostgreSQL 只會全部讀入，所以欄位資料一定要夠，並且依照原始檔csv內容去新建）
3. 利用 COPY 程式碼匯入資料

csv 是什麼？

csv 檔案是由逗號分隔欄位，一行表示一筆資料，如果遇到資料內容包含「,」分隔符號的話，會用 「"」包住資料。程式會忽略掉資料中的逗號，確保資料正確，格式大概長這樣。

victor,11th,"1,2,3",後端前進 PostgreSQL

用逗號分開，「1,2,3」的內容因為包含逗號，所以用"告訴電腦要忽略這段字串裡面的逗號，有時候可能會有這樣的欄位資料

像是PHP語法

$arr = array(1,2,3); //陣列
echo implode(",",$arr); // 輸出 字串 "1,2,3"

把陣列字串化以後，存入資料表的欄位中。 這時候就需要「"」符號，「"」是PostgreSQL 預設值，會忽略掉裡面的分隔符號，匯出資料時都可以自行設定符號 。但通常都是 「"」以及 「,」

匯入

COPY adoption_gov_animals
FROM 'C:\animals_file.csv'
WITH (FORMAT CSV, HEADER);

匯入語法就是這樣，把資料複製到資料表 adoption_gov_animals 中，但是我們還沒有建立adoption_gov_animals 資料表，要先建立資料表，在執行上方指令，並確認下載回來的csv檔案在SQL語法顯示的路徑。

WITH 關鍵字可以指定一些設定，包在括弧中的內容 FORMAT 是設定要匯入的檔案類型，HEADER表示 ，csv 檔案有標題，請從第二行開始匯入，如果是匯出就是相反的道理，匯出檔案並將欄位名稱寫在第一行。

建立資料表

CREATE TABLE adoption_gov_animals (
    id bigint,
    subid varchar(255),
    area_pkid smallint,
    shelter_pkid smallint,
    place varchar(255),
    kind varchar(10),
    sex varchar(10),
    bodytype varchar(10),
    colour varchar(20),
    age varchar(10),
    sterilization varchar(10),
    bacterin varchar(10),
    foundplace varchar(255),
    title varchar(255),
    status varchar(20),
    remark text,
    caption text,
    opendate timestamp,
    closeddate timestamp,
    updated_at timestamp,
    created_at timestamp,
    shelter_name varchar(255),
    album_file text,
    album_update timestamp,
    c_date timestamp,
    shelter_address varchar(255),
    shelter_tel varchar(255)
);

匯入資料

COPY adoption_gov_animals
FROM 'C:\animals_file.csv'
WITH (FORMAT CSV, HEADER);

出現這個錯誤

48281 AAACG1070510029 3 51 新北市新店區公立動物之家 狗 M BIG 黑黃色 F F 三重區永安南路一段196巷 OPEN 己完成體內外驅蟲，瘦弱 DOXY 1片Bid po fur 21days eyedrop eye clean 晶片號碼:"900138000767833 10/6:節育手術 105/9/12 八合一 狂犬病疫苗 106/3/29五合一 2016-07-14 2999-12-31 2018/05/11 2018/05/10 新北市新店區公立動物之家 http://asms.coa.gov.tw/amlapp/upload/pic/ee3e6c4a-548f-4692-b2bd-43509281725b_org.jpg 2018/05/11 新北市新店區安泰路235號 02-22159462

搜尋了這一筆資料，發現

己完成體內外驅蟲，瘦弱 DOXY 1片Bid po fur 21days eyedrop eye clean 晶片號碼:"900138000767833 10/6:節育手術 105/9/12 八合一 狂犬病疫苗 106/3/29五合一

欄位裡面有一個 " 如果這個情況，我會選擇手動刪除 省事，最多紀錄一下等等再補回去 請先備份一份原始檔

結果又出現錯誤

發現檔案最後兩行是空白它匯入不進去 QQ

手動刪除那兩行

刪除並儲存以後，在執行一次 SQL

感動！今天先到這裡～接下來準備整理這些資料，先把資料表留著備用。