PostreSQL 0 Comments Edit Copy Download

PostgreSQL有個COPY指令可以將csv格式的文件快速匯入資料表中，而這個操作可以用pgAdmin的import功能來完成。然而最近我在使用匯入功能時卻遭遇很多問題，讓匯入無法正常進行。以下歸納常見的幾個問題以及其解決方法。

怎麼用pgAdmin匯入CSV檔案？ / How to import CSV into a table with pgAdmin?

pgAdmin是PostgreSQL的圖形化介面管理工具。在Windows中安裝PostgreSQL的時候㑹預設一併安裝pgAdmin，以便我們輕鬆地管理PostgreSQL資料庫，而不需要跟複雜的SQL語法纏鬥。

在pgAdmin中，如果要將CSV檔案匯入其中一個資料表的作法如下：

首先在你要匯入資料的資料表上按右鍵，選擇「Import」。

在Import對話視窗中進行以下操作：

Filename: 選擇要匯入的CSV檔案。
Format: 選擇csv。
按下「Import」按鈕。

如果順利的話，原本的「Import」按鈕就會變成「Done」，而且不會跳出任何訊息。這樣就完成了。

匯入失敗 / Import Failed Prompt

可惜的是，大部分時候你看到的應該都是上面的訊息：

An error has occurred:

ERROR: ….
CONTEXT: ….

然後跳出匯入失敗的訊息：

An error has occurred:

Copy failed!

這是因為CSV檔案本身有問題，讓我們一個一個來看看怎麼解決。

如何編輯CSV檔案？ / How to edit CSV file?

CSV檔案是純文字的格式，可以用Notepad++編輯器來編輯。而且後面有一個CSV的UTF8去除BOM表頭的問題也需要用Notepad++來處理，因此我強烈建議大家使用Notepad++來處理。

Notepad++ Portable免安裝版本：http://portableapps.com/apps/development/notepadpp_portable

如果要用試算表的表格來整理，那我推薦使用LibreOffice Calc，因為它可以正確地將CSV存成UTF8的格式，而不會像Excel使用Big5造成讀取變成亂碼。

如果你的CSV只有英文跟數字，沒有任何中文，這樣就可以使用Excel來編輯。

CSV檔案中分隔符號的問題 / Seperators in CSV file

當遇到以下錯誤訊息：

ERROR: extra data after last expected column

這時候通常是分割符號的問題。

CSV的是Comma-Seperated Values的縮寫，意思是它預設是用逗點「,」來分隔每一格資料 (注意，這是英文的小寫逗點，而非中文的大寫逗號「，」)。但這種分割方式卻帶來了很多問題。

最常見的是資料本身就帶有逗號。這在整理書目文獻的時候很常見，如果要呈現作者的名字，常常㑹遇見將作者姓往前提、中間加逗號，然後再寫作者的名字。例如我的英文名字就是「Chen, Yung-Ting」。像這種時候，這個逗號就會造成CSV讀取欄位錯誤。除了姓名之外，使用者自行輸入的留言資料、甚至是千位以上數字「12,345」，也常常會混雜逗號在裡面。這些都是造成CSV讀取失敗的主要原因。

由於逗號實在是太常出現，很多CSV檔案乾脆改用分號「;」來分隔。因此，我們在使用CSV檔案的時候務必要仔細研究它的分隔符號是什麼，這樣可以大幅降低遭遇錯誤的可能性。

CSV檔案跟資料表欄位不符合 / CSV values is not match to the table's fields

一旦確定CSV使用的分隔符號，那麼我們應該可計算CSV裡面包含的欄位數量，確認看看是否跟資料表的欄位相符。舉例來說，如果資料表裡面有三個欄位、CSV使用的分隔符號為逗號「,」，那麼CSV的一行裡面應該也只能包含兩個「,」、以此分隔出三個欄位。

如果CSV的資料數量不符合資料表欄位數量，那麼匯入時就會失敗。

同樣地，如果CSV欄位前後順序沒有跟資料表相符，那麼匯入時也會失敗。例如資料表的欄位跟資料類型為 id (integer)、name (text)、age(integer)，如果CSV就得用同樣的順序來匯入。

請仔細檢查你的CSV資料跟資料表的欄位，這常常是造成匯入失敗的原因。

在pgAdmin匯入時可以指定要匯入的欄位，但我不太會使用，還是老老實實地將CSV檔案跟資料表作對應吧。

CSV檔案包含了欄位名稱 / First line is field names

當出現以下錯誤訊息的時候：

ERROR: invalid input syntax for integer: ….

這時候是因為匯入CSV資料的格式跟PostgreSQL資料表的欄位資料形態不符合。但這個問題很常在CSV第一行是欄位資料的情況中發生。

有些CSV檔案第一行為欄位名稱。這可能方便我們人工辨識，但是這會讓PostgreSQL誤以為這一行也是要匯入的資料，因此造成匯入時的失敗。像是這樣的欄位資料也是要移除的。上面那個錯誤訊息就是因為PostgreSQL誤以為欄位名稱是資料，嘗試將「id」當做資料匯入「id」欄位，造成資料形態不符合的錯誤。

CSV檔案包含了不可重複的主鍵 / CSV data contains unique primary key

如果遇到以下錯誤訊息：

An error has occurred:

ERROR: duplicate key value violates unique constraint "pk"
DETAIL: Key (id)=(1) already exists.

這表示：

資料表中有設定了唯一值的主鍵 (primary key)；
匯入的CSV資料中包含了主鍵欄位，例如「id」；
匯入的CSV資料中的主鍵，已經在資料表中出現了。

因此這樣就違反了資料表主鍵的限制。

這常常會發生在重複匯入相同的CSV資料的時候，或是在匯入CSV資料之前動過了資料表中的資料，資料表內部的主鍵已經往前數(原本最多是10，現在可能變成12)，而別的地方的伺服器還當主鍵是10，匯出的CSV檔案主鍵也是從10開始數，這樣就造成匯入失敗了。

不過如果是從空的資料表開始匯入，通常不會發生這個問題。

CSV包含了UTF-8 BOM / UTF-8 BOM problem in CSV file

如果出現的問題是：

An error has occurred:

ERROR: invalid byte sequence for encoding "UTF8": 0xff
CONTEXT: COPY csv_import_test, line 1

那麼這就是UTF-8 BOM表頭的問題了。

更糟糕的是，有時候它的錯誤訊息還看不出來是UTF-8 BOM的問題。上面的錯誤訊息看起來就像是單純的資料形態錯誤問題而已，但是資料本身沒有錯，錯的是看不到的UTF-8 BOM表頭。

UTF-8 BOM是一般文字編輯器看不到的隱藏標示，但是這個標示會造成各種問題，包括PostgreSQL匯入的時候就會失敗。UTF-8 BOM在許多地方都是個災難。只要是用Windows相關軟體編輯的UTF-8檔案，包括Excel、筆記本，用這些工具儲存的UTF-8檔案常常都會帶有BOM，然後就會造成其他工具讀取失敗。

UTF-8 BOM的去除方法有很多種選擇，我最推薦的是用Notepad++來處理。做法是在Notepad++開起帶有BOM的檔案之後，從選單列進入「編碼 / 編譯成UTF-8碼（檔首無BOM）」，然後再儲存這個檔案。雖然乍看之下跟原本的檔案沒有差別，但這樣就已經可以正常匯入PostgreSQL中了。

結語：不要用CSV來保存複雜資料 / Conclusion: Don’t Use CSV format to store complex data

這整篇講下來，相信你也可以發現CSV格式可說是問題多多。光是檢查資料裡面有沒有多餘的分隔符號，就讓人足以耗費掉一整天時間。UTF-8 BOM問題讓人防不勝防，難以辨識。而且更糟糕的是，因為CSV是用換行來識別不同筆的資料，因此一旦資料裡面包含了換行，那麼就肯定會造成匯入失敗。而換行符號又有\n、\r、\r\n甚至是\n\r的差別，不同作業系統使用的方式都不一樣，處理起來更是麻煩 (請見\r,\n,\r\n的区别這篇的討論)。

如果是PostgreSQL跟PostgreSQL之間的資料轉移，那麼我建議直接用它內建的匯入匯出功能就好，不要存成CSV。這時候資料保存的格式是SQL語法，而且工具會自動處理掉CSV可能遭遇的問題。做法請見我之前寫的「PostgreSQL的備份與復原」，直到現在我還是覺得這篇很實用。

若要在不同類型的資料庫之間轉移資料，那麼可能CSV還是比較通用的選擇。

題外話，若只是要匯出資料給人編輯的話，則可以嘗試在伺服器端將資料庫的資料轉存成Excel的xslx或是OpenDocument的ods格式。現在有很多工具可以做這樣子的轉換，例如使用PHPExcel或是PhpSpreadsheet。站在推廣開放文件格式的立場來說，我比較偏好使用PhpSpreadsheet轉換成ods格式就是了。

布丁布丁吃什麼？