【awk】Excelの空のセルが消える？ -Fオプションでズバリ解決！

１　Excelでの住所録データ活用

　自宅のWindows PCでの住所録（アドレス帳）データの活用に、表計算ソフトExcelを使っています。住所録は、氏名やその読み仮名、電話番号やメールアドレスなどのデータを保存したExcelファイルです。
　住所録から必要なデータを取り出すのに、VBAで、Excelマクロを作っています。氏名の読み仮名の一部だけの入力で、該当する氏名のデータを取り出せるマクロです。例えば、「やまだ」や「こうたろう」、「こうた」といった、読み仮名の一部入力で「山田孝太郎」のデータを取り出すマクロです。いわゆる、「部分一致検索」というやつです。

２　テキストファイルにしてUbuntuで活用

　この住所録データを Ubuntu Linux PC にも移設して、Ubuntuでも活用することにしました。Ubuntu ではExcelは使えないので、Excelデータをテキストファイルに変換し、そのファイルから必要データを取り出すことにしました。
　Excelワークシートには、A1セルに氏名、B1セルに読み仮名、C1セルに電話番号、D1セルにメールアドレスが、それぞれ入っています。
　ですから、次のようにデータが入力されています。
　　A1：山田孝太郎、B1：やまだこうたろう、C1：空欄（空のデータ）、D1:yamada@hoge.com
　このように、山田孝太郎さんについては電話番号が分からないため、C1セルは空欄となっています。
　このExcelデータを、「名前を付けて保存」機能を使って、タブ区切りのテキストファイルとして保存しました。この操作で、Excel形式からテキストファイルへと変換されました。ファイル名はdata.txtとしました。

３　懸案、問題が発生

　上記２で作成・保存したテキストファイルから、awkを使ってデータを取り出すのに、次のコマンドを実行しました。
　awk '{print "氏名："$1,"読み："$2,"電話："$3,"メール："$4}' data.txt
　このコマンド実行の結果、次のとおり出力されました。
　氏名：山田孝太郎 読み：やまだこうたろう 電話：yamada@hoge.com メール：
　あれれ？これは、変です。おかしいですよ。
　電話番号の項目に、yamada@hoge.comとメールアドレスが出力されています。そして、メールアドレスの項目が空欄になっています。これは、Excelの空セル（電話番号セル）が消えている格好です。
　これじゃダメです。こちらが期待しているのは、次のような出力です。
　氏名：山田孝太郎  読み：やまだこうたろう 電話：  メール：yamada@hoge.com
　つまり、３番めのフィールドには「電話」という項目名だけを表示し、その中味は空っぽの空データとしてもらいたいのです。

４　テキストファイルを疑う

　上記の出力になるのは、テキストファイルへの変換に問題があるのではないかと、最初は考えました。データが何も入力されていないセルは、一つのフィールド（データなしのフィールド）として確保されないため、次のセルのデータが繰り上がる、という考えです。
　けれども、この考えは間違いでした。変換後のテキストファイルを確認すると、入力がないセルも、データなしのフィールドとして保存されています。
　タブ区切りテキスト形式（.txt）で保存する場合、各フィールドはタブで区切られます。そして、空のセルは、2つのタブが連続する形で表現され、空のフィールドとして保存されます。上記テキストファイルには、「やまだこうたろう」と「yamada@hoge.com」の間に、タブが2つ連続する形で保存されていました。つまり、Excelのデータをテキスト形式で保存すると、データがなくてもそのセルはフィールドとして扱われ、その位置を保持します。テキストファイルになっても、元の構造はきちんと維持するようです。

５　当初の解決策

　抽出元のテキストファイルに問題がないとなれば、データ抽出を担当するawkの動作を考えることになります。
　対処法の一つとして考えたのは、空のセルにも、「データなし」などの文字列を入力して、空のままにしない、というものです。確かに、そうすればOKです。
氏名：山田孝太郎  読み：やまだこうたろう 電話：データなし メール：yamada@hoge.com
と出力されますから。実に単純な方法ではあるのですが、確実と言えば確実です。
　でも、そうするには、抽出元のテキストファイルを整形し直す作業が必要です。ちと面倒で、できれば避けたいところです。

６　空セルが消える真の原因

　Excelの空セルはどのように扱われるのか。ここでAIにヒントをもらい、awkの解説本を改めて読み直しました。
　三宅英明、大角祐介著「新しいLinuxの教科書」は、次のように解説しています。

「新しいLinuxの教科書」の抜粋
　awkは、各レコードを自動的にフィールドに分割し、それぞれのフィールドを$1などのフィールド変数に代入します。フィールド分割の際には、スペースもしくはタブが区切り文字とみなされます。スペース、タブはいくつ連続してもかまいません。

　おお、原因はこれですよ、これ。awkは、複数のタブが並んでいても、それをひとまとめにしてフィールド区切り文字として扱うのです。空のセルを含むExcelデータをタブ区切りテキストとして保存すると、空のセルは2つの連続タブとして保存されます。しかし、awkはこの2つのタブを1つの区切り文字と見なしてしまうため、第３フィールドの空データをスキップし、第４フィールドが第3に繰り上がってしまいます。その結果、空白セルが消えるわけです。

７　根本的解決策は、ズバリこれ！

　理屈が分かったところで、それじゃぁ、どうするか。解決策はないのだろうか。
　半ばあきらめかけていたところに、解決の方策が見つかりました。解決策が、実はあるんですよ。AIに解決してもらいました。
　AIの回答は、次のとおりです。
　「解決のためには、awkにタブだけをフィールドの区切り文字として認識させる必要があります。これには、-Fオプションでフィールド区切り文字を明示的に指定します。次のコマンドを実行することで、期待した出力が得られます。」
　んで、示されたコマンドは、次のとおりです。
　　awk -F'\t' '{print "氏名："$1,"読み："$2,"電話："$3,"メール："$4}' data.txt
　これを実行してみると、
　氏名：山田孝太郎  読み：やまだこうたろう 電話： メール：yamada@hoge.com
と出力されます。おお、これは、まさに期待どおりの結果です。電話の項目が、きちんと空のデータとして出力されています。そして、メールアドレスも、所定の第４フィールドとして出力されました。
　なんと、すばらしい！
　「このコマンドでは、-F'\t'によってフィールド区切り文字がタブ（\t）に設定されます。これにより、awkはタブを厳密な区切り文字として扱い、空のフィールドも正しく認識します。」というのです。これが、AIによる解説です。

８　おまけ（AIの威力）

　スペースやタブがいくつ連続していても、awkはそれをひとまとめにしてフィールド区切り文字とすることや、区切り文字の指定に-Fオプションを使うことは、本を読んで知ってはいました。けれども、じゃぁ、どうやって解決するのか、となると、今の自分の力ではどうすることもできませんでした。
　こういうときに、AIの威力をまざまざと見せつけられました。いやぁ、ありがたいことです。AIを鵜呑みにはできないので、基礎は自分で勉強しておく必要はあります。その上で、検討のための提案ならば、十分に、AIにしてもらうことができます。こうしたAI利用が、よろしいのではないでしょうか。

新しいLinuxの教科書　第２版

それでは、また次の記事で。
　　goosyun