「項目名＝値」のテキスト形式のデータを Excel シートの表形式に変換する

2024年5月1日 09:00

はじめに

SHIFT でインフラコンサルタントをしている k.toku です。

以前参画していたプロジェクトで、HULFT で出力されたテキストデータを Excel シートに表形式で取り込む必要が生じたのですが、Excel のワークシート関数を活用することなどによって簡単に取り込める方法があるので、ここで紹介します。

作業の目的

「項目名＝値」形式で複数行に書かれたテキストデータを Excel シートの表形式に変換します。ここではその一例として、HULFT の管理情報パラメータファイル生成コマンド utligen -i snd（配信管理情報）で生成された出力ファイルの結果を Excel シートに取り込みます。

HULFT 管理情報パラメータ出力ファイルの 1 ブロックが Excel シートの 1 行に変換されます。出力テキストファイルのブロック内で「項目名＝値」と書かれている行のうち、「=」の左側がタイトル（Excel の 1 行目）、右側が値（Excel の 2 行目）になります。出力テキストファイルの 2ブロック目以降のタイトルとは 1 ブロック目と共通です。テキストファイルの 2 ブロック目以降は Excel の 3 行目以降に値のみを列挙していきます。

HULFT 出力テキストファイル（utligen -i snd）の出力形式について

1  #
2  # ID=ABCD0001
3  #
4
5  SNDFILE=ABCD0001
    :
25  PREJOBID
26  END
27

1～3 行目：「#」で始まるコメント行
4 行目：空行
5～26 行目：設定値本体（「項目名=値」）
27 行目：空行（この空行も含めて1ブロックと考える。）

上記 27 行（=1ブロック）が ID の数だけ繰り返される。該当項目に値がなくても、「=」の右側を NULL として、行自体は出力される。

例）「DBID=」

注意事項（前提条件）

出力テキストファイルは同一項目による同一行数の繰り返しになっていること。

※utligen コマンドは、オプションの種類によって、同じ項目（行数）の繰り返しにならない場合があるようです。その場合は出力テキストファイルにダミーの項目（行）を挿入することで、行数を揃えておく必要があります。

大まかな作業手順

Excel ブックを立ち上げ、出力テキストファイルを読み込む。読み込む際に、テキストデータを「項目名」と「値」に分解する。
Excel ブックに新しいシートを作成し、関数により「項目名」と「値」を行列状に変換する。
Excel ブックに新しいシートを作成し、シートの行の列を入れ替える。（一般的には「項目名」が横方向に並んでいた方が扱いやすいため。）

手順詳細

Excel を立ち上げ、出力ファイルを読み込みます。ここでは読み込む元データファイルのファイル名を「snd.txt」とします。

1-1. Excel のメニューから「ファイル」をクリックします。

1-2. 「開く」をクリックします。

1-3. 「参照」をクリックします。

1-4. 「ファイルを開く」ダイアログで、ファイルタイプの選択リストから「すべてのファイル(*.*)」を選択します。

1-5. 「snd.txt」を選択し、「開く」をクリックします。

1-6. 「テキストファイルウィザード - 1 / 3」ダイアログで、「コンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」にチェックし、「次へ」をクリックします。

1-7. 「テキストファイルウィザード - 2 / 3」ダイアログで、区切り文字で「タブ」のチェックを外し、「その他」に「=」(半角のイコール)を入力してチェックを付け、「次へ」をクリックします。

1-8. テキストファイルウィザード - 3 / 3」ダイアログで、「データのプレビュー」ですべてのフィールドを選択（[SHIFT] キーを押しながら 2 番目のフィールドをクリック）後、「列のデータ形式」で「文字列」を選択したのち、「完了」をクリックします。

1-9. シートの見出しが「snd」となった下記シートが作成されます。 A 列に「項目名」が、B列に「値」が並んでいることを確認してください。 1～27 行目を 1 ブロックとして、28 行目以降もこの 27 行の繰り返しになっていることを確認します。また、日本語が文字化けしていないことを確認してください。日本語が文字化けしている場合は、手順 1-1 に戻り、手順 1-6 で文字コードに「65001：Unicode (UTF-8)」などを選択して再実行してください。

次の手順からは新しいシートを作成して、データを変換していきます。

2-1. 「⊕」をクリックして新しいシートを作成します。ここでは新しいシートの名前を「Sheet1」とします。

2-2. 「snd」シートの A1～A26 セルを「Sheet1」シートの A1～A26 セルへコピー＆ペーストします。

2-3. Sheet1 シートの B1 セルに次のように記述します。

　 　=T(INDIRECT("snd!B"&(COLUMN()-2)*27+ROW()))

※この数式の意味については後ほど解説します。一旦作業を先に進めます。

2-4. B1 セルを B2～B26 セルにコピーします。

2-5. このあと、B列をC列以降（右側）にコピーをしますが、先にコピーが必要な回数を計算します。

このデータの場合、27 行で 1 ブロックを構成しています。一方、「snd」シートの末尾を見てみると、全体で 540 行あることが分かります。（[Ctrl] + [End] でシートデータの末尾にジャンプすることができます。540 行目は空行です。）

540 ÷ 27 は 20 ですので、データは 20ブロックからできていることが分かります。

2-6. B 列に戻って、B 列をC 列から右に 19 回コピーします。列の数を確認しながらコピー＆ペーストする方法は、B 列を選択して [Ctrl] + C でコピー状態にしたのち、C 列を選択して [Shift] + [→] キー、または、[Shift] キーを押しながら列をマウスでクリックすると選択範囲の右上に "5C" などと表示されますので、今回はこれを "19C" になるところ（U 列）まで広げ、[Ctrl] + V とすれば完了です。

コピー&ペースト直後の状態。

さて、このままでもデータを行列に並べるという意味では終了ですが、一般的にはタイトルが 1 行目に横方向に並び、データが 2 行目から下方向に並んでいる方が使い易いので、次に行列の入れ替えを行います。

3-1. 「Sheet1」シートの表を選択してコピーを実行します。（表中の適当なセルをクリック後、 [Ctrl] + A → [Ctrl] + C を押下します。）

3-2. 「⊕」をクリックして新しいシートを作成します。ここでは新しいシートの名前を「Sheet2」とします。

3-3. 「Sheet2」シートの A1 セルで右クリックし、「形式を選択して貼り付け」を選択します。

3-4. 「形式を選択して貼り付け」ダイアログで、「貼り付け」から「値」を選択し、「行/列の入れ替え」をチェックし、「OK」をクリックします。

「OK」クリック直後の状態。

以上で手順は完了です。シートの列幅は必要に応じて調整してください。ここで Excelブック形式（xlsx 形式）でファイルを保存しておくことをお勧めします。

さて、ここからは途中で出てきた下記の式の解説です。

　 　=T(INDIRECT("snd!B"&(COLUMN()-2)*27+ROW()))

ワークシート関数、COLUMN() と ROW()（いずれも引数なし）は、それぞれその式があるセルの列と行を数値で返します。

例）A1～D4 セルに COLUMN() を入れた結果。

例）A1～D4 セルに ROW() を入れた結果。

INDIRECT 関数は、引数の文字列の内容を参照して返す関数です。

例）

T 関数は引数の値が文字列のときにはその文字列を、文字列以外のときは空白文字列を返します。

※元のデータの「=」の右側が NULL の場合に、INDIRECT の戻り値が 0 となるため、T 関数を入れています。

さて、「Sheet1」シートの B 列、B1～B27 セルには、「snd」シートの B1～B27 セルの値を持っていきたいので、「Sheet1」シートの B1～B27 セルに書かれるべき数式は、

　　B1:　=T(INDIRECT("snd!B1")
　　B2:　=T(INDIRECT("snd!B2")
　　　　　：
　　B27:　=T(INDIRECT("snd!B27")

となれば良いことになります。また、「Sheet1」シートの C列、C1～C27 セルには、「snd」シートの B28～B54 セルの値を持っていきたいので、「Sheet1」シートの C1～C27 セルに書かれるべき数式は、

　　C1:　=T(INDIRECT("snd!B28")
　　C2:　=T(INDIRECT("snd!B29")
　　　　　：
　　C27:　=T(INDIRECT("snd!B54")

となれば良いことになります。上記の "snd!B" の次に来る数字は、COLUMN 関数と ROW 関数を使って、

　　(COLUMN()-2)*27+ROW()

のように書くことができるので、これに INDIRECT 関数、および、T 関数を組み合わせたのが、

　　=T(INDIRECT("snd!B"&(COLUMN()-2)*27+ROW()))

の数式になります。例えば、1 ブロックの長さが 40 行の場合は、"*27" の箇所を "*40" にすれば良いことが理解できると思います。

以上、いかがでしたでしょうか。
手順上、若干面倒なのが、手順 2-5, 2-6 で B 列を C 列以降にコピーするところですが、ここはあらかじめシート 1 行目に 1, 2, 3, … と数字を打っておく方法もあります。
そのほかにもやり方を工夫することができると思いますので、いろいろと試しながら活用してみてください。

執筆者プロフィール：k.toku
国内大手メーカーにて、デスクトップパブリッシング、E コマース、災害対策等のアプリ開発、および、ミッションクリティカルシステムの運用サポートに従事。サーバ、ストレージから OS、ミドルウェアまで幅広い運用対応経験を持つ。 SHIFT に移籍後はインフラ関連のコンサル、テスト設計などに携わっている。

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/

PHOTO：UnsplashのCarlos Muza

最後まで読んでくださり、ありがとうございます！

御社サービスのご相談はお気軽に。

ソフトウェア品質のプロがお伺いします。