オープンソースのERP iDempiereへのデータ移行を行う際に、文字列のデータの中に改行コードが入っているとCSVデータが思わぬところで改行されてしまい、データ移行作業に支障をきたす場合があります。そこで、PostgreSQLで改行コードを取り除く方法について調査及び研究し、その成果をまとめてみました。
改行コードが含まれる文字列
iDempiereでは、テキスト入力フィールドでShift+Enterで改行する事ができます。
このような場合、改行コードが文字列に含まれる事になりますので、CSV形式などでエクスポートすると、CSVデータが思わぬところで改行されてしまう事があります。
ちなみに、このコンテンツで改行コードと言っているのは、下記の3種類の事です。
- chr(13)=\r : CR（キャリッジリターン）
- chr(10)=\n : LF（ラインフィード）
- chr(13)+chr(10)=\r\n : CRLF
改行コードが含まれる文字列のエクスポートとインポート
改行コードが含まれる文字列をCSV形式でエクスポートし、インポート先で改行した状態で復元されるために、JPIERE-0400:ユーザーインポートでは、次のような仕様にしました。
改行コードが含まれる文字列のエクスポート
改行コードが含まれる文字列をエクスポートするSQLで、次のように改行コードを別の文字列に置換します。
regexp_replace(regexp_replace(regexp_replace(u.Description,'\r\n','<CRLF>','g'),'\r','<CR>','g'),'\n','<LF>', 'g') AS Description
- regexp_replace関数を使用して、\r\nを<CRLF>という文字列に置き換えます。
- regexp_replace関数を使用して、\rを<CR>という文字列に置き換えます。
- regexp_replace関数を使用して、\nを<LF>という文字列に置き換えます。
【技術情報】regexp_replace関数
regexp_replace関数は、1番目の引数に文字列を渡します。2番目の引数には、1番目の文字列に含まれる置換対象の文字列を渡します。3番目の引数には、置換対象の文字列を置換する文字を渡します。4番目はオプションで'g'を渡す事で、1番目の引数の文字列に対象が置換する対象が複数含まれていた場合に、すべてを処理してくれます。
改行コードを置き換えた文字列のインポート
上記のSQLで改行コードを別の文字列に置き換えたデータをインポートする際に、置き換えた文字列をプレースホルダとして、改行コードを復元させてインポートします。
updateUser.setDescription(updateUser.getDescription().replaceAll("<CRLF>", "\r\n"));
updateUser.setDescription(updateUser.getDescription().replaceAll("<CR>", "\r"));
updateUser.setDescription(updateUser.getDescription().replaceAll("<LF>", "\n"));