Excelでのcsvで扱う際のあるある対策 - 書式の自動設定

善意、純情の犯す悪ほど困ったものはない。第一に退屈である。
中野好夫 -「悪人礼賛

データベースやログのエクスポート時のフォーマットとしてよく使われるcsv。テキスト形式なのでエディタでも開けますが、Windows環境であればExcelで開いていると思います。Excelでcsv扱うのは、便利ではありますが、いろいろ不都合や不満点があります。
よくあるのは、次のようなもの。
  • 書式が日付や数値に勝手に変換されてしまう。
  • セルの横幅を内容にあわせて調整したい。
  • 大きな表の場合に、毎度オートフィルタとウィンドウ固定するのが面倒

例えば、以下のようなcsvを開こうとしたとします。
DATE,TIME,ID,ID2
2013/12/1,10:00:01,0-1,A
2013/12/20,12:20:00,1-1,01
2014/1/1,15:10:30,A-1,A1
これをExcelで開くと以下のような見え方になります。

csv-in-excel

特に、最初の書式変換がかなりやっかいです。単に表示の書式があっていないというだけならまだいいのですが、ファイルを読み込む段階でセルの値が変換されてしまっているので、あとから対象の列の書式を変更(例えば、文字列として指定)しても意図しない値になってしまいます。
日付の列の幅が足りずに「####」になっていたり、C3の"1-1"は1月1日になり、C4の"01"は"1"になっています。特に値が変わっているものは、読み込み時に変換されてしまっているので、ファイルを開いてから、書式設定を文字列にしても元の値にはなりません。。

特に、勝手に書式が変わる件は、csvをExcelで扱う時の定番問題なので、ググるといろいろヒットします。基本的な対策としてあげられているのは、外部データの取り込みのテキストファイルウィザードを使うというもの。

参考) ITPro :CSVファイルを読み込んだら「1-15」のような製品番号が「1月15日」に変わってしまった!

確かにこの方法で開けますが、非常に手間が多くなってします。そこで同じようなことをするラッパーを用意することにします。ついでに、オートフィルタやウィンドウ固定等、毎回ファイルを開く度に行う操作も自動化させます。

以下ような内容のVBScript(WSH)を用意し、csvを開く時は、このスクリプトにドラッグ&ドロップします。「送る(Sendto)」メニューに入れてもおけば便利かと思います。

Dim xlApp, WS, FS, Args, TempFile, I, Format(255)

Set Args = WScript.Arguments
If Args.Count <> 1 Then WScript.Quit
If LCase(Right(Args(0),4)) <> ".csv" Then WScript.Quit

Set WS = CreateObject("WScript.Shell")
TempFile = WS.ExpandEnvironmentStrings(WS.Environment.Item("Temp"))
Set WS = Nothing
Set FS = CreateObject("Scripting.FileSystemObject")
TempFile = FS.BuildPath(TempFile, FS.GetBaseName(Args(0)) & ".txt")
FS.CopyFile Args(0), TempFile

For I = 0 To 255
    Format(I) = Array(I + 1, 2)
Next

Set xlApp = CreateObject("Excel.Application")
With xlApp
    .WorkBooks.OpenText TempFile, , , , , , , , True, , , , Format
    .DisplayAlerts = False
    .ActiveWorkBook.SaveAs Args(0), 6
    .DisplayAlerts = True
    .ActiveWorkBook.Saved = True
End With
' テンポラリtxt削除
FS.DeleteFile TempFile

Set xlSheet = xlApp.Worksheets(1)
'オートフィルタ
xlSheet.Cells.AutoFilter(1)
'ウィンドウ固定
xlSheet.Activate
xlSheet.Range("C2").Select
xlApp.ActiveWindow.FreezePanes = True
'サイズ自動調整 
xlSheet.Cells.Select
xlSheet.Cells.EntireColumn.AutoFit

xlSheet.Range("A1").Select
xlApp.Visible = True

Set xlApp = Nothing
Set FS = Nothing

VBAの中身はこちらのやりとり(CSVファイルを読み込んだときに文字 列として読みたい - Google Groups)をベースにさせて頂きました。textとして読み込むと同時に、Window固定とオートフィルタ設定を追加しています。
これで先ほどのcsvを開くと以下の様になります。

csv-with-excel-2

データ化けもなくなり、オートフィルタやウィンドウ固定もプリセットされるようになりました。なお、ウィンドウ固定の位置や、文字列以外にしておきたい列がある場合は、スクリプトの該当箇所を修正する必要があります。必要ならファイル名等で場合分けもできますので、そのへんは各環境や目的のファイルにあわせてということで。

しかし、csvの書式問題ってずいぶんと昔からある不満点のはずなので、せめてExcelの設定で読み込み書式を選択できるようにする(例えば、自動検出と文字列固定とか)はあってしかるべきと思います。自動認識もうまくはまる時は便利ですが、それ以外の時は単なる迷惑以外の何者でもないです。ぜひ、改善してほしいものです。

【追記】テキストとして開くと、項目内改行がされないみたいです。上のvbsだと元ファイルまでおかしくなってしまいましたので、そういったファイルには残念ながら使えません。csvで開くと書式化け問題になるし。。もっといい方法ないものか。。

4873113474

【関連記事】
WindowsでCUI環境

【関連書籍】
Excel Hacks 第2版― プロが教える究極のテクニック140選 David Hawley Raina Hawley 羽山 博
Windows自動処理のためのWSHプログラミングガイド 五十嵐 貴之
最速攻略 VBScriptサンプル大全集 Windows7/Vista/XP/2000対応 結城 圭介
続ExcelVBAのプログラミングのツボとコツがゼッタイにわかる本―最初からそう教えてくれればいいのに! 立山 秀利

コメント

非公開コメント

本のおすすめ

4873115655

4274065979

4822236862

4274068579

4822255131

B00SIM19YS


プロフィール

  • Author:proger
  • 組み込み関係で仕事してます

ブログ内検索