日付を含むエクセルデータをエクスポートしたときのCSVデータの時間表示をシェルコマンドで取り扱う方法


※ 当ページには【広告/PR】を含む場合があります。
2021/04/14

ExcelデータからCsv形式へインポート/エクスポートする際に気をつけておかなかればならない一つの日付を取り扱う換算ルールを特集しました。

合同会社タコスキングダム|蛸壺の技術ブログ
Excelでの時間の計算をシェルスクリプトでどう取り扱うのか〜DATEの計算方法

エクセルでの日付時間とシェルスクリプトの扱うUNIX時間との日付のフォーマット形式を考慮して、シェルスクリプトでCSVインポート・エクスポートを行う方法を考察してみます。


合同会社タコスキングダム|蛸壺の技術ブログ【効果的学習法レポート】シェルスクリプトをこれから学びたい人のためのオススメ書籍&教材特集

はじめに

当サイトではオフィス業務のComputer-Aidedなハイブリッドな方法を模索し、より効率的なExcel業務を実現したい多忙なオフィスワーカー向けの主にAwkとSedを使うシェル講座です。

シェルスクリプトはどこでもどんなOSでも基本的に使えて、しかも一度使い方を覚えると、Excelと組み合わせて最高に効率の良いオフィスワークツールが作れることでしょう。

合同会社タコスキングダム|蛸壺の技術ブログ


合同会社タコスキングダム|蛸壺の技術ブログ【効果的学習法レポート】シェルスクリプトをこれから学びたい人のためのオススメ書籍&教材特集

合同会社タコスキングダム|蛸壺の技術ブログ【効果的学習法レポート】シェルスクリプトをこれから学びたい人のためのオススメ書籍&教材特集

Excelデータのエクスポートで日付列はどう変化する?

例えば適当なエクセルで以下のようなExcelデータシートを作成し、

合同会社タコスキングダム|蛸壺の技術ブログ

一旦これをCSV形式でエクスポートしてみると、以下のようになるはずです。

            
            44197,51,98875
44198,91,150457
44199,77,134982
44200,53,107022
44201,48,95401
44202,66,124309
44203,81,143109
44204,57,111326
44205,93,179488
44206,104,220456
44207,87,182094
44208,60,115022
44209,71,130943
44210,63,124072
44211,50,110650
44212,77,135557
44213,81,140073
44214,57,113078
44215,62,146033
44216,82,175592
44217,99,174301
44218,72,131045
44219,60,120744
44220,93,169973
44221,100,194532
44222,93,165395
44223,70,128044
44224,55,117064
44225,86,149561
44226,79,137043
44227,65,148774
        
日付の列だった一列目が入力した覚えのない数字に勝手に変わっていると最初は戸惑うかもしれませんが、前回の記事でも取り上げたようにこれがエクセルの日付表記の実体でもあり、バグではないので注意が必要です。

しかしこのままでは日付が人間の目からは何時なのか分かりませんので、前回解説したテクニックで一時的に日付を変更します。

            
            $ cat accounting.csv | awk -F"," '
    function parse_excel_date(rawtime) {
        #👇1900/01/00とのEpoch時間差を取得
        parsed_time = (rawtime - 1) * 60 * 60 * 24 - 2209075200;
        excel_date = strftime("%Y年%m月%d日", parsed_time);
        return excel_date;
    }
    BEGIN{ OFS="," }
    {
        print parse_excel_date($1),$2,$3;
    }
'
#👇出力
2021年01月01日,51,98875
2021年01月02日,91,150457
2021年01月03日,77,134982
2021年01月04日,53,107022
2021年01月05日,48,95401
2021年01月06日,66,124309
2021年01月07日,81,143109
2021年01月08日,57,111326
2021年01月09日,93,179488
2021年01月10日,104,220456
2021年01月11日,87,182094
2021年01月12日,60,115022
2021年01月13日,71,130943
2021年01月14日,63,124072
2021年01月15日,50,110650
2021年01月16日,77,135557
2021年01月17日,81,140073
2021年01月18日,57,113078
2021年01月19日,62,146033
2021年01月20日,82,175592
2021年01月21日,99,174301
2021年01月22日,72,131045
2021年01月23日,60,120744
2021年01月24日,93,169973
2021年01月25日,100,194532
2021年01月26日,93,165395
2021年01月27日,70,128044
2021年01月28日,55,117064
2021年01月29日,86,149561
2021年01月30日,79,137043
2021年01月31日,65,148774
        
これでエクセル時間が通常の日付表記に出力できてより見やすくなりました。


合同会社タコスキングダム|蛸壺の技術ブログ【効果的学習法レポート】シェルスクリプトをこれから学びたい人のためのオススメ書籍&教材特集

余談〜CSVデータ再インポートのとき

先程の日付を変換したあと、再度このCSVデータをExcelにインポートしたい場合に、日付の列の値をエクセル時間に戻す必要はありません。

Excelのインポーターが日付データとして自動で処理をしてくれるので、インポート時に勝手にエクセル時間に戻ります。
記事を書いた人

記事の担当:taconocat

ナンデモ系エンジニア

主にAngularでフロントエンド開発することが多いです。 開発環境はLinuxメインで進めているので、シェルコマンドも多用しております。 コツコツとプログラミングするのが好きな人間です。

合同会社タコスキングダム|蛸壺の技術ブログ【効果的学習法レポート】シェルスクリプトをこれから学びたい人のためのオススメ書籍&教材特集