【筆記】PHP + PostgreSQL 將資料寫入檔案 - CSV 格式

環境

• Ubuntu 18.04
• PHP 7.4
• PostgreSQL 10.22

使用情境

    欲將 PostgreSQL 中的資料寫入檔案，並且希望使用 CSV 格式。其中部分字串資料包含 , 字元。

基本函數 

    使用 pg_copy_to() 函數來模擬 PostgreSQL CLI 中的 COPY 語法（使用 pg_query() 等函數無法使用 COPY）。

$db = pg_connect($connection_string);
$sql = "SELECT col1, col2 FROM table WHERE conds;";
# 第二個參數在 () 放入欲複製的語法，第三個參數為分隔符號
$data = pg_copy_to($db, "($sql)", ','); # 回傳資料為 Array

   然而 pg_copy_to() 無法設定分隔符號，也無法使用 PostgreSQL 語法的 CSV HEADER 等後綴詞，只會將反斜線(\)、逗號(,)及換行符號用反斜線(\)跳脫。分別會變成\\、\,及\r\n。這些跳脫格式不符合大部分程式讀取 CSV 檔的規則，常常造成一些欄位錯誤的問題。因此最好將這些資料轉換成更通用的格式。

正規語言做字串處理

    使用以下方式將字串手動轉換成 CSV 適用的格式，主要是將含有跳脫字元的欄位用雙引號包起來，並且將跳脫字元處理掉，也不能忘記雙引號的處理。

# 一共進行四次轉換
# 第一次將雙引號轉換成兩個雙引號以跳脫
# 第二次轉換將含有反斜線或兩個雙引號的欄位用雙引號包住
# 第三次轉換將 \r\n 取代為換行
# 第四次將跳脫用的反斜線去除
$pattern = array('/\\"/','/((?:\\\\.|[^\\\\,])*(?:\\\\.|\\"\\")[^\\\\,]*)/','/\\\\r\\\\n/','/\\\\(.)/');
$replace = array('""','"$1"', "\n", '$1'); 
$data = preg_replace($pattern, $data); # preg_replace 可傳入 Array，回傳 Array

寫入檔案

   將轉換後的結果寫入檔案。

$path = '/path/to/file.csv';
# 由於無法使用 HEADER 後綴，必須自己加上標頭
$title = 'col1,col2';
$fp = fopen($dir.'release_list_survey.csv',"w");
# 若資料中含有非 ASCII 字元，且使用者有用 EXCEL 開啟檔案的需求，開頭必須加入 BOM 才會自動使用 UTF-8 編碼
fwrite($fp,"\xEF\xBB\xBF");
fwrite($fp,$title."\n");
# 目前我使用的版本，pg_copy() 的結果本身就含有換行，因此 implode() 參數代入空字串，依版本可能需要改成 "\n"
fwrite($fp,implode("", $data));
fclose($fp);

    以上是今天的筆記。