Help us understand the problem. What is going on with this article?

ls -lの結果からExcel関数を使ってファイル名とディレクトリ名とyyyy/mm/ddを取り出す

More than 3 years have passed since last update.

UNIX/Linuxサーバ等で取得したファイル情報を、使いやすい形に加工したい!
そんな時に使えるエクセルの関数です。

たとえば、UNIXサーバで「ls -l」を実行した結果、
下記のようなデータを取得し、Excelに貼り付けたとします。

Excel
アクセス権 所有者   グループ    サイズ   月 日 時間or年     フルパス
-rw-r--r--  root    root    5600    Dec 21  10:45   /val/log/test.log

エクセルの関数を使って、以下のようにファイル名、ディレクトリ名、日付(yyyy/mm/dd)を取得しましょう。
関数なので、マクロ等のスクリプト不要です。

Excel
アクセス権 所有者   グループ    サイズ   月 日 時間or年     フルパス        ファイル名     ディレクトリ名   更新日時
-rw-r--r--  root    root    5600    Dec 21  10:45   /val/log/test.log   test.log    /val/log/   2016/12/21

 ※セルは、A1セルから記載していると仮定します。
 ※Linuxの場合、表示形式が少し違うので、修正が必要です。

それでは、さっそく見ていきましょう!


更新日時をyyyy/mm/ddで取得する

=IF(ISERROR(DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1)))=TRUE,DATEVALUE(CONCATENATE(E1,"-",F1)),DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1)))

これで、「2016/12/21」 という形で取得できます。

Excel
アクセス権 所有者   グループ    サイズ   月 日 時間or年     フルパス        更新日時
-rw-r--r--  root    root    5600    Dec 21  10:45   /val/log/test.log   2016/12/21

解説

1. CONCATENATE(F1,"-",E1,"-",G1) / CONCATENATE(E1,"-",F1)

CONCATENATEは文字列の結合です。& でつなげるのと同義です。
CONCATENATE(F1,"-",E1,"-",G1)は、
「21−Dec-0.44779...」になります。
CONCATENATE(E1,"-",F1)は、
「Dec-21」になります。

 ※時間はシリアル値に変更されていますので、10:45が0.4479...になっています。

2. DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1)) / DATEVALUE(CONCATENATE(E1,"-",F1))

DATEVALUEは、日付を表す文字列を日付のシリアル値に変換します。
DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1))
は、「#VALUE!」
DATEVALUE(CONCATENATE(E1,"-",F1))
は、「42725(書式設定で日付に変換すると2016/12/21)」
になります。
前者が「#VALUE!」になるのは、年が入る場所に時間が入っているためエラーになっています。
後者は年を省いていますので、直近の年になっています。

3.IF(ISERROR(DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1)))=TRUE,DATEVALUE(CONCATENATE(E1,"-",F1)),DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1)))

これで仕上げです!
ISERRORは、対象がエラーの時にTRUEを返します。
つまり、2.で出てきた「#VALUE!」を判定できます。
VALUE!等のエラーが出た場合は、DATEVALUE(CONCATENATE(E1,"-",F1))
出なかった場合は、DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1))
といった感じに、結果を分岐して表示します。
今回は、年が時間になっていてエラーなので、DATEVALUE(CONCATENATE(E1,"-",F1))の結果「42725(書式設定で日付に変換すると2016/12/21)」を表示します。
エラーにならないケース(年が入っているケース)では、DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1))を使用して表示します。


ファイル名を取得する

=TRIM(RIGHT(SUBSTITUTE(H1,"/",REPT(" ",100)),100))

これで、「test.log」が取得できます。

Excel
アクセス権 所有者   グループ    サイズ   月 日 時間or年     フルパス        ファイル名     更新日時
-rw-r--r--  root    root    5600    Dec 21  10:45   /val/log/test.log   test.log    2016/12/21

解説

1. REPT(" ",100)

まずはこれ。空白を100回繰り返します。理由は後述します。

2. SUBSTITUTE(H1,"/",REPT(" ",100))

H1(/val/log/test.log)を置き換えます。
"/"を探して、空白100個に置き換えます。
下記のような状態になります。
「     var     log     test.log」(空白数はイメージです。実際は100個です。)

3. RIGHT(SUBSTITUTE(H1,"/",REPT(" ",100)),100)

右から100文字取得します。
「        test.log」
こんな状態になります。92個の空白と、test.logで100文字です。

4. TRIM(RIGHT(SUBSTITUTE(H1,"/",REPT(" ",100)),100))

TRIMは、スペースを一括削除します。
これで、「test.log」が取得できます。

※注意:ファイル名が100文字以上の場合、右から100文字以上取得できませんので、
100を200とかに変えると良いと思います。


ディレクトリ名を取得する

=LEFT(H1,LEN(H1)-LEN(I1))

これで、「/var/log/」と取得できます。
 ※「I1」には、前項で取得したファイル名を入れておきます。

Excel
アクセス権 所有者   グループ    サイズ   月 日 時間or年     フルパス        ファイル名     ディレクトリ名   更新日時
-rw-r--r--  root    root    5600    Dec 21  10:45   /val/log/test.log   test.log    /val/log/   2016/12/21

解説

1. LEN(H1)-LEN(I1)

LENで文字列の長さを取得しています。
G1(/val/log/test.log)の長さから
H1(test.log)の長さを引いています。
つまり、ディレクトリの文字列(/var/log/)の長さを取得しています。

2. LEFT(H1,LEN(H1)-LEN(I1))

LEFTで、左からディレクトリの文字数だけ切り取っています。
これで、「/var/log/」が取得できましたね。


いかがでしたでしょうか。

他にも色々な方法があると思いますし、シェルやPerlなどを使って、サーバ上で表示を整えても良いと思います。

どうしてもExcel上で形式を整える必要がある(多くは環境の制限などで)ケースで、使ってみてください。


今回使用した関数

ISERROR : セルの値がエラーかどうか調べる
DATEVALUE : 指定した文字列をシリアル値に変換する
CONCATENATE : 文字列を結合する

TRIM : スペースを一括削除する
RIGHT : 右から指定の文字数を取得する
SUBSTITUTE : 文字列を置き換える
REPT : 文字列を指定した数だけ繰り返す

LEFT : 左から指定の文字数を取得する
LEN : 文字列の長さを取得する


umeee
プログラミングはエンタテイメントだ! 楽しむことがたいせつ。 興味のおもむくままにやりましょう。 最近のロールはプロジェクトマネージャ、ITコンサルタントです。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした