So-net無料ブログ作成

Excel と CSV (2) [Office]

Excel は、Excel で作成した情報をプログラム的に操作する機能をいくつか公開している。

ひとつは、マクロと呼ばれるもので、VBA (Visual Basic for Application) というプログラミング言語を使用して作成する。ちょっと古めの言語仕様ではあるけれど、標準的なユーザーインターフェースでは提供されない、繰り返しを多用するような作業を記述するくらいなら結構重宝する。

中にはちょっとしたシステムとも呼べるような巨大なマクロを組む猛者もいるようだけれど、残念ながら私はそこまでの根性はないようです。Excel のドキュメントを操作するための DOM (Document Object Model) の仕様も一応公開されてるので、暇つぶしに眺めてみると面白いかもしれない。ユーザーインターフェースが提供されないだけで、Excel の機能として備わっているのなら、それで問題は解決してしまうんだけどねぇ。

Excel の 「名前を付けて保存」 に相当する機能は Workbook オブジェクトの SaveAs メソッドに実装されている。

Workbook.SaveAs メソッド 

 「名前を付けて保存」ダイアログを開いて比べてみると個々のパラメータがどこに割り当てられているか想像がつくかもしれない。そして、ドキュメント中の TextCodepage は名前からして今回の目的を一瞬で解決してくれそうな雰囲気もあるのだけど、いきなり「Microsoft Excel のすべての言語で無視されます。」なんていう記述がある。

ダメもとで試してみたんだけれど、本当にダメでした。 

Excel には Unicode でファイルを書き出す機能がないのか?なんてことも考えたんだけど、FileFormat パラメーターに xlUnicodeText を指定すれば Unicode のテキストファイルが作成されるので、まったくダメというわけではなさそう。

DOM として機能が提供されないなら、自力でファイルを書き出せばいいのだけど、VBA でゴソゴソ作るのはちょっと辛いかもしれない。VBA は Excel のドキュメントを扱うにはそこそこの機能を提供してくれるけど、Excel から離れたことを実現しようとすると途端に面倒くさくなる。決してできない訳ではないだろうけど、苦労してまでやりたくはないというのが本音だったりする。

オートメーションを使用して Excel の外部から Excel を操作することもできるのだけど、使い方によっては「処理しているのが見えるくらいに遅い」ことがあるから、今回はいきなりパスということにします。面白いネタが見つかったら、報告することがあるかもしれませんが。

前フリもそろそろ飽きてきたので解決策にいきます。

Excel のファイルを OleDB あるいは ODBC を使用して操作することが可能です。Excel のワークシートをデータベースとして扱うと言ったらいいでしょうか?また、.NET Framework には ADO.NET という機能があり、各種データベースを統一的に扱うことができるようになっています。もうひとつ、PowerShell には オブジェクトの保持する情報を CSV として出力する Export-CSV というコマンドレットが用意されています。

答えは見えたかと思います。Excel ファイルのもつ情報を ADO.NET を使用して取り出してしまえば、あとは好きなように情報を扱うことができます。

PowerShell のスクリプトです。若干長く感じるかもしれませんが、ココに置くために改行を挿入したため冗長になっているだけで、結構シンプルです。今回は OleDB を使用していますが ODBC を使用することも難しくはないです。

function global:Excel(
[string] $FileName,
[string] $Sheet="Sheet1",
[switch] $NoHeader
) {
$opt= if ($NoHeader) { "no" } else { "yes"; }
$path=Convert-Path $FileName
foreach ($connect in
(
"Provider=Microsoft.ACE.OLEDB.12.0;"+
"Extended Properties='Excel 12.0; HDR=$opt;';"+
"Data Source=$path;"
),
(
"Provider=Microsoft.Jet.OLEDB.4.0;"+
"Extended Properties='Excel 8.0; HDR=$opt; IMEX=1;';"+
"Data Source=$path;"
)
) {
try {
#Write-Warning $connect;
$adp= New-Object System.Data.OleDB.OleDBDataAdapter(
"SELECT * FROM [$Sheet`$]",
$connect
);
$data= New-Object System.Data.DataTable;
[void] $adp.Fill($data);
$data;
break;
}
catch { }
}
}

Office 2003 以前の環境でも使用可能なように Jet をプロバイダーに使用するコードも含んでいますが、Excel 2007 以降を使用しているのであれば ACE が Excel の古いファイル形式 (.xls) もサポートしていますので削ってしまっても問題ないです。

使用している環境で、どのプロバイダーが利用できるかを確認するためには、下記のコマンドが利用可能です。Jet は既に開発を終えているようですので更新されることはないでしょうが ACE は新しいものも出ているようです。

(New-Object System.Data.OleDb.OleDbEnumerator).GetElements() | ft SOURCES_NAME

読み取ったデータから CSV を作成するにはこんな感じに記述する。

Excel .\Excelのファイル.xlsx |
Export-Csv <適当なフォルダ>\output.csv -NoTypeInformation -Encoding UTF8

もともとの目的が UTF-8 の形式を作ることでしたのでエンコーディングを指定していますが、他の形式も使用可能です。詳細についてはヘルプを参照してください。


Excel と CSV (1) [Office]

今日、職場でちょっとした相談を持ち掛けられた。「Excel で作った会員情報があるんだけど、CSV に出力するにはどうしたらいい?普通に CSV 出力すると一部文字化けるみたいなんでそれもなんとかしたい。」

まあ、ありがちな質問ではあるんだけど、web で検索してみても殆どまともな解答が得られない厄介な代物。

Excel をインストールすると、拡張子 .csv は Excel に関連付けられるので、デスクトップに散らばってる csv のファイルをダブルクリックするだけで Excel が立ち上がってくれるので結構便利ではあるのだけど、今ひとつ機能が追い付いていない感じがするのも否めない。

Excel において CSV を扱う場合の厄介な点がいくつかあり、慣れないとどうしてもハマってしまうことがある。

  • Unicode で作った CSV のデータを Excel に入力する場合、文字が化けることがある。
  • Excel から CSV のデータを出力しようとすると、Shift JIS になってしまうため、(全てではないけど)JIS 第三水準、第四水準に相当する漢字が扱えないことがある 。

長くなりそうなので、今日は 1 つめの対処方法から。

Excel は Unicode の CSV ファイルを扱えるようにはなっているけど、使い方を誤ると文字化けてしまうことがある。どんな解決策があるか web を眺めてみたら、結構とんでもない解答で締めくくられているものもあるね。最低なのが「一度 Shift JIS に変換して」ってやつ。Unicode には Shift JIS で表現できない文字も含んでいるので、文字化け程度で済まずにデータが破壊されてしまう可能性があるよね。

メールアドレスや電話番号、数式だけを集めたようなものではあまり関係ないのだけれど、人名や住所といったものを集めていくとどうしても Shift JIS 外の漢字を使わざるを得ない場合がある。Excel 自体はもともと Unicode に対応しているので、そういった文字でも普通に扱える。ただし、収集した情報を二次利用したい場合に一時的に CSV を仲介しようとすると上記のような問題点に出くわしてしまうことがある。

Excel は CSV で使用される文字コードを Shift JIS であるものとして動作する。これについては、Excel が登場して相当な期間が経っているので、その間(グローバリゼーションなんて言葉さえなかった頃)に作成されたデータに対する後方互換性を考えたら別段誤った動作ではないと思われる。そして、Shift JIS と区別することさえできれば Unicode の CSV データも扱うことができるようにはなっているのも事実。

Unicode で符号化したテキストの先頭にはバイトオーダーマーク (BOM) というものを付加することができる。簡単に言えば、BOM によってアプリケーションは符号化形式 (UTF-8 とか UTF-16 とか) を判別することが可能になっている。Shift JIS では BOM は付加できないので、BOM がなければ Shift JIS、BOM があるならその内容に従い Unicode として扱う...というのが Excel の流れなんだろうと想像できる。

Windows 環境限定の話になるのだけれど、Unicode テキストを扱うなら常に BOM 付きを選んでおいた方がいいような気がする。メモ帳ではそもそも BOM なしが選べないけれど、高機能なテキストエディタではこれが可能になっていることも多く、知らずに設定してしまって忘れた頃にハマることがないわけではない。

ちなみに、「(魚の)ほっけ」、「とびうお」、「(牛丼の)よしのや」という単語を漢字で表記すると Shift JIS 外の漢字を含むことになる。なので、Shift JIS ベースで作成された CSV ファイルでは決して使用できない単語になってしまうのだけれど、BOM 付きの UTF-8 ベースの CSV であれば何の問題もなく(入力が若干面倒くさいという点はあるけれど)使用することができるようになる。興味があったらお試しあれ。

次回は、Excel から UTF-8 ベースの CSV ファイルを作製する方法です。

 


この広告は前回の更新から一定期間経過したブログに表示されています。更新すると自動で解除されます。