CSV ファイルの Excel 抽出時の注意点

CSV の正式な確認方法

xoBlos の業務で、入力データとして登場するファイル形式の一つ、CSV。Office を入れられている方からすれば、「Excel の一種!」というご意見が挙げられることもあるでしょう。

しかし Excel と思われてしまうのは、『アプリケーションの関連付けが Office(Excel)にされている』、或いは『CSV ファイルを開くのに Office(Excel)が推奨されている』等の面に縁るのです。

f:id:xoblos:20180605134734p:plain

CSV は上図、上部のようにメモ帳やテキストエディタで、開くこと/開いた見た目 = 正式/正確です。確認するだけでなく、編集するなら尚更です。Excel で開くと上図、下部の「No」のように Excel ならではの表現に書き換えられてしまうものもあるので、確かな情報が得られませんし‥‥このまま編集すると、誤った情報で更新しかねません。これは、xoBlos を通しても同じことが言えます。

CSV に対する制御シートでの抽出

xoBlos では、CSVExcel として見て抽出することができます。前節のような注意点はありますが、「CSV のヘッダ行に、XML に使用できない項目名がある!(前回記事」や、「形式は CSV であるけれど、レコードによって列の量が変動する!(前回記事」等の場合に、制御シートを用いる抽出方法が活躍します。

Excelから抽出』の手順が使えるということは、『高速抽出エンジン』が使用できる?‥‥とも思われるかもしれませんが、CSV に対しての高速エンジンの使用は推奨しません。以下の仕様を、理解しておきましょう。

CSVExcel として取り扱う上で、一回の処理に 3回ファイルは開かれている。

「C:\Users\ユーザ名\AppData\Local\Temp」に出来上がる一時ファイルは、
上記のとおり「3つ」出来上がる。
→文字コードの認識や、同容量の Excel を一時的に作り上げる分等によるもの。
⇒高速エンジン使用すると、一時ファイルは消えない為、
 C ドライブの容量が増えていく

CSV で困った時はこちら

Excel の親切機能に笑顔で対応する

親切が "仇" に。

f:id:xoblos:20180522114632p:plain

上図は一見して、特に難しくはない抽出用の制御シートですが、作成途中です。出力項目である「売上」は、セル「AUG2」を対象にしたく‥‥更に隣のセル「AUH2」からは、「bve」という項目名として抽出を行おうとしています。

しかし作成者は、頭を抱え込んでいます。一体どうしたのでしょうか。‥‥列が「AUG」という、果てしない範囲に困惑しているのでしょうか?いいえ、実際に 3000列程の項目を取り扱ったお客様も前例としてあります。

f:id:xoblos:20180522114717p:plain

作成者は、セル「AUG2」と値「bve」をひたすら入力しています。ところが上図の状態が繰り返されるだけで、いつまで経っても完成しません。

AUG2」という入力に対し、Excel は「August の 2(8月2日)」と解釈し、親切に日付書式に変えてくれます。

一方「bve」に対しては、Excel は「キー[b]とキー[v]は隣同士であるから、ユーザはタイプミスしたのかな?そうすると英単語の『be』と入力したかったのかな?」と解釈し、これまた親切に正しい文字列に変えてくれます。

f:id:xoblos:20180522114724p:plain

AUG2」を A1 表現しなければ良いのでは?という方。「(2,1229)」では、新たな問題に直面することでしょう。その為、セルの書式を予め文字列にしてから再入力することで、本件は解消します。‥‥「bve」は上図のように、入力文字の自動修正(オートコレクト)機能の設定に及びます。変更、削除等は皆様次第です‥‥

Excel に限らず、Word や PowerPoint 等といった Office 製品が、大抵デフォルトで設定されている事項です。「bve」等を実値として扱われる方は、この仕組みを不親切と捉えられるかもしれませんね。

要件の汲み取り方

期待する出力結果から判断する。

xoBlos で開発を行う際、要件が専門的で何を行えば良いか困惑することがあります。要件を提示する側とて、表現が困難な場合もあります。そのような時は一つの手段として、先ず期待する出力結果(帳票)から要件を汲み取ることをお勧めします。入力データにしろ出力データにしろ、「開発側の観点」「要件を提示する側の観点」は必ず分かれるものですが、お互いの認識一致への近道には、成り得る筈です。

f:id:xoblos:20180518145018p:plain

例えば上図 2つの入力データ――「主データを基に副データに対する直積情報を出力して欲しい」と挙げられた際に、困惑するパターンはどういうものがあるでしょうか。「直積は分かるけれど、xoBlos でどう組めば良いのだろう‥‥」「直積って何!?」等が挙げられるのではないでしょうか。

前述の 2パターンは、有識者に事前確認しても何ら差し障りは無いかもしれませんが‥‥。期待する出力結果があって xoBlos の機能も理解していれば、たとえ「直積」を知らずとも、たとえ要件に「直積」が挙げられていなかったとしても、開発に取り掛かることができるのです。

f:id:xoblos:20180518151223p:plain

複数の入力データがあり、一方が「基」となるデータであるとすると、大抵もう一方は『追加入力』或いは『変換入力』の活用を思い付く方もいらっしゃることでしょう。しかし 2つの入力ファイルは、お互いにマッチさせる要素(キー値)が何もありません

出力結果から、それぞれのデータがどういう状況(出力単位)であるのか注意深く見ていくと‥‥。主データの 1 情報は副データの全件分、"繰り返し" と "固定" で情報を紐付けています。いずれも、xoBlos に存在する機能やキーワードになりますね。

f:id:xoblos:20180518151822p:plain

繰り返すとは言え、一度生成した結果シートに連結して、次のデータを生成することはできません。ここではそれぞれの結果をシート分けすることにします。(このような手段もありますが‥‥)

ここまで来れば後は簡単ですね。中間データとして生成したブックに対し、全シートを抽出しましょう。中間処理にはもう慣れましたか?複数シート抽出の注意点も、復習しておきましょう!

生成時の「Sheet1」

「Sheet1」って?

f:id:xoblos:20180427162052p:plain

上図は、単純な新規生成処理で取り扱われるシート達です。制御シートの設定内容と、雛形シートの最低限のレイアウトを基に、結果シートが作成されます。通常、新規生成要件の場合はこの三つのシートの内、「制御シート」と「雛形シート」を同一のブックに用意します。そして「結果シート」は、業務ファイル(.xob)の『出力設定』に新しいブックのシートとして設定しますね。

f:id:xoblos:20180427162059p:plain

xoBlos に新しいブックを作成させるのは、上図のように普段、私達が手動で空白のブックを新規作成するのと同じことになります。Excel の設定によって変わりますが、新規作成時には必ず「Sheet~」のシートがいくつか用意されます。Excel の仕様上、シートが一枚も存在しないブックという形態は、作ることができないのです。その為 xoBlos では、結果シートに最低一枚の「Sheet1」が作成される仕様になっています。

既存ブック生成という手段

f:id:xoblos:20180427162107p:plain

新規ブック生成と既存ブック生成は、上図のように設定が変わってきます。後者は、生成キーワード『区分』に分類見出しで設定した入力項目の値と、ブック上の値をマッチングさせる時に使いますね。

さて、よく考えてみましょう。既存のブックは帳票や資料、つまり元々は私達が手動で作成したものであり、空の「Sheet1」等も無く、必要なシートしか存在しないことが殆ど。‥‥ということは、新規ブック生成の際にも、必要最低限のシートしか持たない既存ブックに対し、既存ブック生成の設定方法で生成させれば、「Sheet1」が残る問題は解消されることになるのです!

業務ファイル(.xob)の在り方
手順1:予め雛形シートしか無いブックを用意し、出力先へコピーしておく手順2:既存ブック生成の方法で、コピーしたブックを雛形として生成を行う

なお、「雛形シートと既存ブックのシートが同じ名前で、業務ファイルの『c.ひな形シート名』項に設定したら、どちらのブックが優先されるの?」という疑問にお答えしますと、前者の雛形シート(制御シートも持ったブック)の方が優先されます。

外から渡すパラメータの限界文字数って?

f:id:xoblos:20180419182246p:plain

限界文字数は取り扱い先で決まる。

xoBlos の業務に於いて、参照元も参照先も、足りない情報があればユーザ自らが xoBlos を通して伝えなければなりません。その為に、業務実行時のユーザ定義変数(パラメータ)というものが用意されています。

さてこの変数ですが、結局の所何文字まで入力することができるのでしょうか。‥‥無限、は正確な答えではありません。具体数値も決定できません。何故なら‥‥何文字入力できても、取り扱い先で取り扱えなければ不正でしかない為です。

Excel のセル内に出力させる32767 文字(Excel   の仕様)
Excel のシート名にあて込む   31 文字(Excel   の仕様)
参照パス/ファイル名に使う  234 文字(Windows の仕様)

異なるバイト文字の存在をチェックする

全体の文字/バイト数を比較する。

値のチェックは、前回記事のように中間処理を設けるのが適当です。必要な情報は、対象となる値の文字列数バイト数の 2つ。――そこに差があれば、全角と半角の入り交じりは確定し‥‥その比較結果を保持する為に、1列だけ変数項目(お皿)を用意しなければなりませんね。

制御シート(生成~抽出)

f:id:xoblos:20180405113953p:plain

雛形シート上に 1列(上図の列「B」)、そのお皿なる列を設けておきましょう。Excel 関数「LEN」は、対象(上図の列「A」)の文字の桁数を得ます。逆に Excel 関数「LENB」は、対象の文字のバイト数を得ます。Excel 上で、半角文字(半角カナを含む)は 1バイト、全角文字は 2バイトで数えます。これで列「B」も抽出に含めれば、条件付けすることができますね。

‥‥しかし残念なことに、バイト数を取り扱う関数(「LENB」「MIDB」「FINDB」「SEARCHB」等)は、現在の抽出高速エンジンには対応していません。(生成高速エンジンで出力することはできますが、それを正しく抽出できません。)処理速度が遅くなっても良い場合は、お勧めする方法です。

xobQuery

f:id:xoblos:20180405114001p:plain

SQL に慣れている方には、上図方法をお勧めします。xobQuery は XML to XML なので、中間処理としましても 1手順で済みます

構文「HEX」は、括弧内の文字列の文字コードを 16進数で返します。半角文字(半角カナを除く)は、一文字につき 16進数コードで 2桁を表します。その為、「2」で除算しています。半角のみなら文字列数とマッチしますし、全角が混在していればマッチしません。

累計/累積データの取り扱い

前回データと今回データを連結させる。

f:id:xoblos:20180315171939p:plain

上図のように、今回生成するデータを常に前回生成した最終データの直後に追加出力するという要件は、結構挙げられます。xoBlos は、こういった累計/累積を成す帳票要件に対し、①前回出力結果の抽出、②今回入力データの抽出、③①と②を連結して基本入力データとして 1から生成――の工程を基本としていますので、覚えておきましょう。③部分は、前回記事にて復習してください。

前回データ抽出の懸念

さて、前節までの基本事項を意識すると「それでは前回までに出力されたデータが膨大な量であったら、処理時間に影響が及ぶのではないか。」というご意見が挙げられることでしょう。‥‥もっともではありますが、xoBlos の生成で、データの繰り返しがある場合に設定が必須になるキーワード『行(列)ひな形の左上』の仕様上、どうあっても今回実行分の付け足しのみ――という作りは、通常のやり方では実現不可能なのです。

f:id:xoblos:20180315171947p:plain

生成結果に沿って、上図のような雛形シートを用意するのは適当ですが、次回実行も考慮すると他に何が必要でしょうか。‥‥ 業務を実行して、参照されるデータ件数が常に変動する場合、xoBlos が大事にしている左上~右下を特定するのに、前回の最終データが出力された場所を判断できる印を用意しなければなりません。その場合、常に参照先が固定になる Excel の標準機能『名前定義』が活躍することになりますが‥‥

f:id:xoblos:20180315171957p:plain

前節までに用意した雛形シートの、どの位置へ定義したとしても、ひな形行/列が消える以上、必ず使い続けられない問題に直面します。

‥‥しかし上図、名前定義をひな形行より下にしている後半 2つのケースをじっくり見ていると、共通の何かが言えることになります。それは‥‥「名前定義を置いた行の直前行さえ、毎回残っていれば良いのに!」

f:id:xoblos:20180315172006p:plain

上図、内容だけ素直に注目すると何事かと思われるでしょう。‥‥前節から、今必要としているのは空白一行です。‥‥では、入力データ(.xml)に空のデータが一つあれば、空の行が出せるのではないでしょうか。ダミーの意味で空のブックを制御シートと同じところにセットし、抽出対象としてしまいましょう。列項目は制御シートの設定から「ID」「Desc」と揃えられ、抽出対象のブックは空っぽなので値は Null がどちらとも入ります。

f:id:xoblos:20180315172014p:plain

冒頭とは変わり‥‥今回データと前節の空のデータを連結して、基本入力データとして生成してみましょう。期待する結果は満たせていません(空白行が常に出力されて見栄えが悪くなっています)が、抽出も生成も同じ制御シートで、前回データを一度も取り扱うことなく、累計/累積表を出力することができるようになります!

出力する順序どおりに基本入力ファイルを設定すれば、連結する必要はありません。

f:id:xoblos:20180315172028p:plain