:::

如何解決 CSV匯入PostgreSQL發生的錯誤? / How to resolve the problems while importing CSV data into PostgreSQL?

image

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


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

pgAdmin-About

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

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

image

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

image

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

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

image

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

匯入失敗 / Import Failed Prompt

2016-11-05_134402

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

An error has occurred:

ERROR: ….
CONTEXT: ….

2016-11-05_134416

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

An error has occurred:

Copy failed!

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


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

2016-11-05_135143

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

256px-LibreOffice_4.0_Calc_Icon.svg_

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

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

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

2016-11-05_140726

當遇到以下錯誤訊息:

ERROR: extra data after last expected column

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

image

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

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

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

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

image

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

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

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

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

image

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


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

2016-11-05_134402

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

ERROR: invalid input syntax for integer: ….

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

image

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


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

2016-11-05_141520

如果遇到以下錯誤訊息:

An error has occurred:

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

這表示:

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

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

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

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

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

image

如果出現的問題是:

An error has occurred:

 

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

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

image

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

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

image

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格式就是了。