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のプログラミングのツボとコツがゼッタイにわかる本―最初からそう教えてくれればいいのに! 立山 秀利

Ubuntu12.04でatftpdを使う時の設定

僕はつい見えもしないものに頼って逃げる
君はすぐ形で示して欲しいとごねる
Mr. Children - NOT FOUND

開発環境用のUbuntu12.04上でatftpd(Advanced TFTP server)を動そうとした時に、少しハマったのでそのメモです。

まず、atftpdはaptitudeでinstallしておきます。

$ sudo aptitude install atftpd

デフォルトでは、inetd(Ubuntu12.04だとrlinetd)経由での起動になっていますが、不要なので単独daemonとして起動するように/etc/default/atftpdを変更します。
USE_INETDをfalseに変更して、これでOKかと思ったのですが、実際にこれで起動するとatftpdの起動に失敗します。解決策としては、同じく/etc/default/atfpdで、OPTIONSにポート番号指定(--port=69)を追加してやればOKのようです。

(参考)
TFTP Server atftpd fails to start - Ask Ubuntu

(/etc/default/atftpd 設定例)

USE_INETD=false
OPTIONS="--tftpd-timeout 300 --retry-timeout 5 --port=69 --maxthread 100 --verbose=5 /srv/tftp"

変更後、atftpdを起動し直します。

$ sudo /etc/init.d/atftpd start

ps等で起動しているか見てみて、atftpdが起動していればとりあえず起動は成功です。

念のため、動作確認するために、別のマシンor自分自身からtftpしてみます。
自分自身からアクセスするならtftp localhostして、適当なファイルをputして成功すればOKです。 うまくいかない場合は、エラーログ等参考に、root directory(/tftpbootとか)のpermissionやiptable等のフィルタ設定を見直しましょう。


4873114225 【関連書籍】
Linuxカーネル Hacks ―パフォーマンス改善、開発効率向上、省電力化のためのテクニック 池田宗広 大岩尚宏 島本裕志 竹部晶雄 平松雅巳 高橋浩和
リンカ・ローダ実践開発テクニック―実行ファイルを作成するために必須の技術 (COMPUTER TECHNOLOGY) 坂井弘亮
起動プログラム ブート・ローダ入門─クロックの初期化や外部メモリの設定からOSの起動まで TECH I シリーズ (TECH Iシリーズ) Interface編集部
組込みソフトウェア開発入門 ~組込みシステムの基本を‐ハードウェアとソフトウェアの両面から学ぶ! 星野香保子 並木秀明 菊池宜志 日比野吉弘

gnupackでCygwin導入した時に最初にすること

人生の半分はトラブルで、あとの半分はそれを乗り越えるためにある。
八月の鯨』より

Windowsにgnupackを使ってCygwin環境をセットアップした時のメモです。

gnupackについては、emacsとあわせてアーカイブ展開するだけで、ある程度設定済の環境がすぐに使えるということで、手軽にCygwinを構築するには非常に便利です。とはいえ、いくつか設定等変更しておくと便利な部分もあるので、そのあたりのメモです。ベースとしては、gnupack_devel-11.00.exeを使っています。


apt-cygの修正

今現在最新の11.00だと、ちょっと前にCygwinダウンロードサーバ側の変更に追従できていないためにエラーになります。とりあえず修正必要なのは二点。

  • mirrorサイトのディレクトリパス変更へ追従 (でないと、apt-cyg updateでsetup.iniがnot foundになり失敗する)
  • tar.xzへの対応 (元のapt-cygだとbzip2決め打ちになっているので新しいパッケージ解凍に失敗する)

apt-cygの公式版の更新は止まっていますが、forkしたものがいくつかgithubで公開されているので、それをとってきて、/app/script/apt-cygを置き換えるのが手っ取り早いです。
forkの状況等は以下のページが詳しいです。

apt-cyg - PIB

また、プロキシ環境でapt-cygで使いたい場合は、/etc/wgetrcを~/.wgetrcにコピーして、proxy設定部分を書き換えておきます。


Cygwinパスとの互換性

gnupackではwindowsフォルダのパスが標準cygwinと少し違います。とはいえ、cygwin環境用のスクリプトで、内部でWindowコマンドを呼び出しているものなどがあると、標準Cygwinスタイルのパス(/cygdrive/c/...)を期待している場合があります。いちいちスクリプトを変更するのもの何なので、互換性確保のためにsymbolic linkをはっておきます。

# mkdir /cygdrive/
# ln -s /c /cygdrive/c

minttyの文字コードをUTF-8に変更

gnupackの場合、windowsコマンド等との互換性優先のためか、minttyのデフォルト文字コードがsjis(cp932)になっています。他の環境やツールとの互換性を考えると、utf-8の方がいろいろ都合が良いので、設定を変更しておきます。gnupack展開先のトップディレクトリにあるconfig.iniを書き換えます。

(config.ini.diff)

18c18
<     LANG           = ja_JP.cp932
---
>     LANG           = ja_JP.UTF-8

また、.vimrcの設定も標準でsjisになっているのでこれも書き換えておきます。
(sjis設定で上書き設定している部分をコメントアウト)

~/.vimrc.diff

2c2
< set encoding=cp932
---
> "set encoding=cp932

Windowsコマンドの文字化け対策

minttyの設定をUTF-8に変更して困るのは、Windowsコマンド(ipconfig等)の出力の文字化けです。標準Cygwin環境もデフォルト文字コードがUTF-8なので同じことになります。対策として標準出力をUTF-8に変換するラッパーを通すようにします。
文字コード変換にはiconvを使えばいいのですが、gnupackのデフォルトでは入っていないので、予めapt-cygでlibiconvをinstallしておきます。その上で、.bashrcに以下のようなaliasを設定すればOKです。

(~/.bashrc)

function wincmd() {
    CMD=$1
    shift
    $CMD $* 2>&1 | iconv -f cp932 -t utf-8
}

alias ipconfig='wincmd ipconfig'
alias netstat='wincmd netstat'
alias netsh='wincmd netsh'
alias cscript='wincmd cscript'

他に、必要な物があれば同じような感じで追加すればOKです。
なお、netsh interface dumpのように、標準出力をファイル出力したものを、そのまま別コマンドの入力として使うような場合には、ダンプしたファイルの文字コードがUTF-8のためにエラーになってしまうことがあるので注意が必要です。(PowershellもUTF出力なので同じみたいですが)


pingの切り替え

Windows7で、minttyからping打とうとすると、実行権限がないというエラーがでて実行できません。

# ping 127.0.0.1
ping: socket: Operation not permitted

minttyを管理者として実行すればいいのですが、それも面倒なので、Windows版のping (C:\Windows\system32\PING.EXE)を使うようにします。(Windows版だとpermissionにひっかからない)
.bashrcにaliasをはればいいのですが、minttyの文字コードをUTF-8に変えていると、他のwindowsコマンドと同じく文字化けしてしまうので、その場合は、上述の例と同じく、.bashrcで文字コード変換のラッパーををかますようにalias登録しておきます。

(~/.bashrc)

alias ping='wincmd /c/Windows/system32/PING.EXE'

設定関係はこれくらいで、あとは、必要なパッケージを追加で入れていきます。ssh, mingw64-i686-gcc-core, git, pythonなどなど。その辺は、それぞれの目的に応じてということで。

なお、gnupackは便利なのですが、最近更新が止まっているせいか、新しいバイナリをあとから入れるといろいろ問題があるようです。詳しく見てませんが、gnuplotやgroffは動きませんでした。ソースからコンパイルすればいいのかもしれませんが、本格的に使おうという人は標準Cygwinの方が結局手間がないかもしれません。

4774149543

【関連記事】
WindowsでCUI環境


【関連書籍】
詳解 シェルスクリプト Arnold Robbins Nelson H. F. Beebe 日向あおい
Windows コマンドプロンプト ポケットリファレンス 山近 慶一
Windowsコマンドプロンプト スパテク242 Vista/XP/2000対応 (スパテクシリーズ) 飯島 弘文

本のおすすめ

4274065979

4844337858

482228493X

4904807057

4873114799


プロフィール

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

ブログ内検索