神(紙)ExcelからデータをCSVで抽出した時のメモ

はじめに

スプレッドシートで罫線を多様するのは日本人の特徴とはよく指摘されることですが、そのように作成されたファイルに貴重なデータが埋め込まれていることはよくあります。

個人的にはExcelの中にデータ用シートと出力用シートが分離されていないものは再利用性がないと思っているのですが、技術レベル・作業時間等の制約から全てがそのように処理できるわけでもなく、また人によってデータ構造に対する考え方が違ってきたりもして、シートを分割すれば問題がないとはいえないのが難しいところです。

罫線を効率良く利用する際に併用されるセルの結合に注目し、帳票から有用な情報を抽出するために作業した際のメモを残しておきます。

参考資料

• 【エクセルVBA】一瞬で結合セルを解除して値を埋めるマクロ

ここではVBAコードに加えた箇所だけを掲載していますので、VBAコードの本体は参考資料のリンク先を確認してください。

対象となるExcelファイル

今回は次のようなCSV形式で出力した場合に、それなりに意味のある処理ができそうなExcelファイルを対象としています。

これはExcel標準のテンプレートそのままですが、このタイプのファイルをCSV形式で出力すると次のようなファイルが得られます。

クラス スケジュール,,,,,スケジュールの開始時刻,時間間隔,クラスの一覧
,,,,,,8:00,15 分,
,時刻,日曜日,月曜日,火曜日,水曜日,木曜日,金曜日,土曜日
,8:00,0,0,0,0,0,0,0
,8:15,0,MTH-113,0,MTH-113,0,MTH-113,0
,8:30,0,MTH-113,0,MTH-113,0,MTH-113,0
...

内部は名前の管理やらトリッキーな数式が設定されていたり、条件付き書式が多用されているなど、Excel地獄へまっしぐらといった感じですが、先頭の数行はヘッダーとして特殊な扱いが必要かもしれませんが、本体のデータが記述されている行は規則正しく、スクリプトで再利用することが可能です。

対象とはしないExcelファイルの特徴

行ベースで処理できななそうな構造を持つ次のようなファイルは対象外です。

【対象外の例】画面上に表が点在しているもの (Excel標準テンプレート)

これはかなり一般的だとは思いますが、表が画面上の上下左右に分散しているようなものは対象外です。

この場合でも前半の収支計算部分は使えませんが、後半の詳細部分はデータが規則正しく並んでおり、データを抽出することはできそうです。

このCSV出力をRubyのCSVクラスでパースして、各行を配列に分割すると次のようなデータ構造になります。

引数にCSVファイルを取るrubyコード

# !/usr/bin/ruby
#
require 'csv'

exit 1 if $ARGV.length == 0

file = open($ARGV[0])
csv = CSV.new(file)
csv.each {|line|
   p line
}

このスクリプトの出力は以下のとおりです。

実行例

["このワークシートでは、個人の毎月の予算を作成します。このワークシートの使用方法に関する役に立つ指示がこの列のセルに表 示されます。下向き矢印で開始します。", nil, nil, nil, nil, nil, nil, nil, nil, nil]
["このワークシートのタイトルが右のセルに表示されます。次の説明はセル A5 にあります。", nil, "個人用月次収支", nil, nil, nil, nil, nil, nil, nil]
[nil, nil, nil, nil, nil, nil, nil, nil, nil, nil]
["1 か月の予測収入ラベルは、右のセルにあります。セル C5 に収入 1 を、C6 に臨時収入を入力すると、1 か月の収入合計が C7 に計算されます。次の説明はセル A7 に表示されます。", "1 か月の予測収入", nil, nil, "予測収支\n(予測収入から支出を減算)", nil, nil, "¥3,405.00 ", nil, nil]
[nil, "収入 1", "¥4,300.00 ", nil, nil, nil, nil, nil, nil, nil]
[nil, "臨時収入", "¥300.00 ", nil, "実収支\n(実収入から支出を減算)", nil, nil, "¥3,064.00 ", nil, nil]
["予測収支はセル H4、実収支は H6、差額は H8 にそれぞれ自動的に計算されます。次の説明はセル A9 に表示されます。", "月収合計", "¥4,600.00 ", nil, nil, nil, nil, nil, nil, nil]
[nil, nil, nil, nil, "差額\n(実額から予測額を減算)", nil, nil, "(¥341.00)", nil, nil]
["実月収ラベルは、右のセルにあります。セル C10 に収入 1 を、C11 に臨時収入を入力すると、1 か月の収入合計が C12 に計算さ れます。次の説明はセル A14 に表示されます。", "実月収", nil, nil, nil, nil, nil, nil, nil, nil]

特定のキーワードを検出して、次にくる数字をデータとして処理することもできると思いますが、"¥3,064.00 "のような文字列で出力されている点など、難易度は高そうです。特にこの部分については、操作は可能ですが、凝った処理が必要になると思われます。

こういったシートは作成者が複数存在したり、人によって列や行を追加して見た目は同じだけど違う構造のファイルを独自に作ったりしていて、なかなか綺麗に整理することが難しいと感じています。
ニーズはあると思いますが、こういった帳票を前提とした処理にはいまのところ取り組んでいません。

処理の概要

ExcelからはCSV形式での出力を行ない、後段の処理はRuby,Python言語等のスクリプトで行ない意味のありそうな処理を実現します。

類似するPDFファイルからのテキスト情報抽出について

当初はpdf2txtスクリプトとかありますよね、と思ったのですが、PS・PDF系は印刷出力に特化した言語系なので、意味のあるデータを取り出す事は難しい場合がほとんどです。OCRのような画像処理の方がまだ向いているとは思いますが、日本語文字列を含む場合には難しさはあると思います。

現実的な課題

全ての帳票が理想的な構造を持つわけではありません。セルの結合機能を利用して見栄えを揃えている場合には、期待したような出力を簡単には得られないですし、そのようなケースは多いでしょう。

結合したセルの取り扱い

ここがいまのところ一番苦労した部分です。

例えば、大分類・中分類・小分類のような構造(他には、部門・課・係、など)を持っているデータの場合には、次のようにセルの結合を多用しつつ罫線を利用している表があるとします。

このCSV形式での保存は、次のようになります。

CSV形式で保存したファイルの内容

No,大分類,中分類,小分類,作業項目,
1,開発,要件定義,準備,現行業務ヒヤリング,
2,,,,業務抽出・課題分析,
3,,,書類作成,提案書作成,
4,,,プレゼン,提案,
,,,,,
5,,工程2,項目1,作業1,
6,,,,作業2,

一見して空列の処理がポイントになることは想定できるかと思います。

これは後処理のスクリプト側で空データの場合には、前行の内容で埋める、という処理(この場合は2列目には"研究"を埋めることになる)を繰り返すことで埋めることが可能ですが、実際に試してみると”結合されたセル"と、"空のセル"には意味的に違いがある場合が多いです。(オリジナルのデータ作成者は意識せずに見栄えだけを追求しているとは思いますが、結果的に結合されたセルには複数行をグループ化する役割が付与されています)

具体的には7行目のような空行と、それまで中分類として結合されたセルにまとめられている2〜6行目のデータは区別する必要が出てきます。(この例では単純に無視できますけれどね…)

というわけで、結合されたセルを展開し、各行にデータを埋め込みたいニーズはそれなりにあると思います。

似たようなアプローチを考える人はいるもので、参考資料に上げているリンク先のVBAスクリプトを利用すると、次のような結果を得ることができます。(ここには掲載しません。解説もありますので参照してください。)

空セルはそのまま、結合されたセルがなくなり、各セルに値が挿入されています。
このCSV出力は次のとおりです。

CSV形式で保存したファイルの内容


No,大分類,中分類,小分類,作業項目,
1,開発,要件定義,準備,現行業務ヒヤリング,
2,開発,要件定義,準備,業務抽出・課題分析,
3,開発,要件定義,書類作成,提案書作成,
4,開発,要件定義,プレゼン,提案,
,開発,,,,
5,開発,工程2,項目1,作業1,
6,開発,工程2,項目1,作業2,

こうなるとCSVファイルの取り扱いは、かなり見通しが良くなります。

この方法を利用すると、プログラマーではない、オリジナルのデータ作成者は、セルの結合化によって意味グループを形成すれば良くなるので、データを作成する負担がかなり軽減できると感じています。

処理前にファイルを確認する作業も、かなり見通しがよくなりました。意味のないセルの結合は解除し、あるいは反対の処理をするだけで、それなりの構造を持ったデータを抽出することができます。

改行を含むセルの取り扱い

結合されたセルには改行を含む文字列が入力されている場合があります。

CSVに出力する際に改行を含んでもRFC的には問題なく、およそ標準的なCSVライブラリではパースできるはずですが、改行を含んだままでは実用ではおそらく問題が発生すると思います。スクリプト側で処理をしても良いのですが、行末と区別するには字句解析が必要になるので、あらかじめ次のようなVBAマクロも利用して改行コードを別の文字に変換しています。

VBAマクロに追加しているコード

  For Each c In ActiveSheet.UsedRange
    c.Value = Replace(c, vbLf, " / ")
  Next

Rubyによるデータ抽出の例

簡単なCSVライブラリを利用したファイルの読み込み例は、前述したとおりです。
もう少し処理を進めるとデータ行を表現するためのクラスを作成するようになりました。

おおまかなクラスの構造は次のようになっています。

csventry.rb

class CsvEntry
  
  ## 定数としてRegexpオブジェクトを定義する
  REG_STARTING_NUM = /^[0-9]|^[１２３４５６７８９０]/
  REG_STARTING_NUM_WITH_SPACE = /^[0-9] |^[１２３４５６７８９０] /
  REG_WHITE_SPACES = / +|　+/

  attr_accessor row1, 
                row2,
                ...

  ## オブジェクトを再利用することも考えて初期化メソッドを準備
  def reset()
    @row1 = "0"
    @row2 = ""
    ...
  end

  ## 初期化メソッドではrowの各値のvalidationは積極的には行なわない
  def initialize(row=[])
    reset()
    if row.length > 2
      @row1 = row[0]
      @row2 = row[1]
      ...
    end
  end

  ## 各値が想定した値かどうか判定するロジックは、こっちのクラスに実装する
  def check_row1()
    ret = false
    ret = true if @row1 =~ REG_STARTING_NUM
    return ret
  end
end

こんな感じのクラスを定義して、利用しています。