ACLでの改行削除

執筆者 後藤 聡

執行役員

■国際認定CAATs技術者(ICCP)

大手監査法人において、IT戦略・計画策定支援、内部監査支援、財務諸表・内部統制監査の一環としてのIT監査等に従事。2018年より三恵ビジネスコンサルティング株式会社に入社。現在CAATs導入支援、業務改善支援等に従事。

ACLで分析を行うにあたり、分析の対象とするデータに改行が含まれており(※1)、この改行を 「なんとかして削除したい!」 といったケースに直面すること(※2)はないでしょうか。

(※1)実務では、仕訳データの『摘要文』や、工事契約データの『件名』・『契約内容』等といった項目に、改行を含んでいるケースをよく見かけます。

(※2)改行を含んだ状態でデータ分析を進めていったが、いざ、分析結果をCSV形式のテキストファイルで出力しようとすると、改行が邪魔をしてうまく出力できない等といったケースをよく見かけます。

今回は、ACLを使ってデータ分析を行うにあたり、分析対象データに含まれている改行を削除する方法について、ご紹介します。

実務上、ACLに取り込むデータの形式として、CSVとExcelの2パターンが多く使われているかと思いますので、ここではその2パターンについてみていきたいと思います。

■改行を削除するタイミング

データに含まれる改行を削除するタイミングとしては、以下の2つが考えられます。

 ①ACLにデータをインポートする時

データをインポートする時に、パラメータを指定することで、データ中に含まれる改行を削除します。

 ②ACLにデータをインポートした後

データをインポートした後に、関数を使ってデータ中に含まれる改行を削除します。

では、①・②のそれぞれについて、改行を削除する具体的な方法について、みていきましょう。

■①ACLにデータをインポートする時 に改行を削除する方法

ACLにデータをインポートする時の改行削除方法について、データの形式毎に整理すると、以下のようになります。(いきなり結論から記載してしまいますが。)

(参考)キャリッジリターン、ラインフィードとは?

 キャリッジリターンは、文字入力位置を先頭に戻すことを指示するための制御コードであるのに対し、ラインフィードは、文字入力位置を次の行に送ることを指示する制御コードとなります。多くのシステムでは、この2つを使って改行を制御しています。

以下のようなサンプルデータで、実際のACLの動きをみてみましょう。

<CSV形式の場合>

ACLで、区切り文字付きテキストファイルとしてインポートしようとすると、以下のように改行が入っている部分がズレて表示されてしまいます。

そこで、「文字列の引用符」において、”ラインフィードの削除(F)” にチェックを入れてみます。

(”ラインフィードの削除(F)”にチェックを入れることで、テキスト修飾子間で発生するすべての LF 文字(ラインフィード)をスペース文字で置換することができます。)

すると、改行部分がスペース文字に置換され、『工事件名』 に一つのデータとして表示させることができました!

上記では、改行コードとしてラインフィードが使用されているものとして、”ラインフィードの削除(F)”にチェックを入れましたが、インポート対象のデータに含まれる改行コードに何が使われているのか(キャリッジリターン or ラインフィード or キャリッジリターンとラインフィードの両方)に応じて、パラメータを指定することになります。

<Excel形式の場合>

ACLで、Excelファイルとしてデータをインポートしようとすると、区切り文字付きテキストファイルのような、改行を削除する為のパラメータが表示されません。

(すなわち、インポート時に改行を削除することはできません。)

ただし、Excelの場合には、データ中に改行が含まれていても、CSVのように行がズレることなく、データを取り込むことができます。

なお、インポート時には分かりにくいのですが、インポートしたデータには改行が含まれている点には注意が必要(※)です。

(※)例えば、改行を含んだ状態でインポートしたデータを、CSV形式のテキストファイルでエクスポートすると、行がズレて出力されてしまいます。

■②ACLにデータをインポートした後 に改行を削除する方法

改行を含んだ状態でACLにデータをインポートした後は、以下の関数を使ってデータ中に含まれる改行を削除することができます。

REGEXREPLACE()関数

REGEXREPLACE は、正規表現と一致する文字列のすべてのインスタンスを、新しい文字列で置き換えることができます。

では、改行を削除するために、REGEXREPLACEの各パラメータに、どのような値を指定すればよいのかみていきましょう。

なお、改行コードとして何を使用しているのかは、OSによって異なります。

標準的なOS毎の改行コードは、以下となります。

(※)正規表現とは、ある特定の文字列の集合を、特定の形式にする方法の一つです。

従って、先程のサンプルデータで、工事件名から改行を削除したい場合には、以下のようにすれば上記のOSいずれにも対応するように、改行コードを削除(ブランクに置換)することができます。

REGEXREPLACE(工事件名 , "\n|\r\n|\r" , " " )

(正規表現において、” | ” はOR条件として扱われます。)

では、ACL上の具体的な動きをみてみましょう。

先程のサンプルデータを、Excelファイルにて(改行を含んだまま)取り込んだ後のテーブルが以下(テーブル:件名に改行を含むデータ)となります。

このテーブルに、『工事件名』 より改行を削除(ブランクに置換)した演算フィールド(フィールド名称:『工事件名_改行削除』 )を追加してみましょう。

(DEFINE FIELD コマンドを使って、デフォルト値に REGEXREPLACE(工事件名 , "\n|\r\n|\r" , " " ) を指定します。)

DEFINE FIELD 工事件名_改行削除      COMPUTED  REGEXREPLACE(工事件名 , "\n|\r\n|\r" , " " )

以下のように、件名から改行を削除したフィールド(フィールド名称:『工事件名_改行削除』 )が追加されました。

しかし、パッと見ただけでは、改行が削除されているのかどうか、よく分からないので、このデータをCSV形式でエクスポートしてみましょう。

実際に、エクスポートしたデータが、以下となります。

『工事番号』 が 2103002 のレコードについて、『工事件名』 と 『工事件名_改行削除後』 を比較してみると、確かに改行が削除されていることが分かりますね。

■まとめ

このように、データの中に改行を含んでいる場合には、インポート対象のデータの形式に応じて、インポート時ないしはインポート後に、改行を削除することができます。

実務で、改行を含むデータを入手し、改行をどうしても削除したい場合には、是非、活用してみてください!

SanKei%E3%83%AD%E3%82%B4%2B%E3%83%AD%E3%

三恵ビジネスコンサルティング株式会社

SanKei Biz Consulting, Corp.

  • Facebook

Tel: 03-6268-9750 平日9時30分~17時30分
〒102-0074
東京都千代田区九段南1-5-6 りそな九段ビル5F

© 2020 三恵ビジネスコンサルティング株式会社