himorogiの日記

主にプログラミングに関することなど。少々ハード（電子工作）についても。

Excel WorkBook をテキストとして保存する場合の挙動の違い

Unicode テキスト(*.txt)

Excelから
ファイル[名前を付けて保存]
ファイルの種類[Unicode テキスト(*.txt)]
を選択すると、Unicode(UTF-16 LittelEndian/BOM付き/Tab区切り)テキストとして保存される。
この時、

  1. Cell 中に含まれるHT:horizontal tabulation("\t":u+0009)はwhite space(" ":u+0020)に置換される。
  2. Cell 中にcomma(",")若しくはdouble qutation('"')若しくはLF(u+000A)が含まれるときは、Cell の内容を'"' で囲う
  3. 改行は CRLF("\r\n"(u+000D+000A))

Unicode テキスト(*.txt)として保存された場合、Cell中のtab("\t":u+0009)は空白(" ":u+0020)に置換されるため、Unicode テキスト内に出現するtab("\t":u+0009)は必ず区切り文字である。
データ交換として、他のシステムに渡すため、区切り文字を相手のシステムに合わせて変更する必要がある場合は、単純にtab("\t":u+0009)をリプレースするだけで済む。
予めセル（フィールド、カラム）中のtabが空白に置換されるので、データ透過性は保持されないという問題はあるが、Excelの[名前を付けて保存]で選択できる出力形式の中では、データ交換形式としては一番マシ。

CSV(カンマ区切り)(*.txt)

Excelから
ファイル[名前を付けて保存]
ファイルの種類[CSV(カンマ区切り)(*.txt)]
を選択すると、CSV(Shift-JIS/Comma区切り)テキストとして保存される。
この時、

  1. Cell 中に含まれるHT:horizontal tabulation("\t":0x09)は white space(" ":0x20)に置換される。
  2. Cell 中にcomma(",")若しくはdouble qutation('"')若しくはLF(0x0A)が含まれるときは、Cell の内容を'"'で囲う
  3. 改行はCRLF("\r\n":0x0D0A)

Excel同士でのやり取りならば、ExcelのCSV(カンマ区切り)(*.txt)出力でも、Cell中の改行(LF)と、レコードの改行(CRLF)は識別されるが、データ交換後に他のアプリケーションやプログラムでファイルを開き→保存しなおすと、大抵の場合、改行コードは統一されてしまう(LF→CRLFか、CRLF→LFの何れかが行われることが多い)。
その結果、Excel上でセル内の改行と、レコードの改行が識別できていても、他のシステムで保存された後はセル（フィールド、カラム）中の改行とレコードの改行が識別できなくなってしまう。
Excel同士でやり取りする場合を除き、データ交換フォーマットとして使ってはいけない。

テキスト(スペース区切り)(*.prn)

ファイル[名前を付けて保存]
ファイルの種類[テキスト(スペース区切り)(*.prn)]
を選択すると、スペース区切り(Shift-JIS)テキストとして保存される。

  1. Cell 中にHT:horizontal tabulation("\t":0x09)が出現するとHT:horizontal tabulation("\t":0x09)以降の内容は無視される（出力されない）
  2. 区切りのwhite space(" ":0x20)はカラム毎に桁揃えされる（セルの内容の文字数が不揃いの場合、次のカラムの先頭の桁が揃うよう、セル内容に続く区切りのwhite spaceが増減する）
  3. Cell 中にcomma(",")若しくは double qutation('"') 若しくは LF(0x0A)が含まれていても、cell の内容を'"'で囲わない！！！
  4. 改行はCRLF("\r\n":0x0D0A)

即ち、[テキスト(スペース区切り)(*.prn)]で保存した場合、元の内容と一致しなくなる。
Excel同士の場合も含めてデータ交換のためのフォーマットとしては絶対に使ってはいけない。

おまけ：numbers の生成

スクリプトエディタ版

app = Application.currentApplication();
app.includeStandardAdditions =true;

function numbers(x){
	return ( "0000" + Math.floor( Math.random()*Math.pow(10,x) ) ).substr(-x)
}
app.displayAlert( numbers( 3 ) + String.fromCharCode(13) + numbers( 4 ) );

Automator版

function run() {	
	function numbers(x){
		return ( "0000" + Math.floor( Math.random()*Math.pow(10,x) ) ).substr(-x)
	}
	return numbers(3) + String.fromCharCode(13) + numbers(4);
}

結果は省略

文字列中の改行の扱い

Javascript Automator OSX

スクリプトエディタや、Automator の Javascript から、文字列中に改行を埋め込むには、"\n" ではなくString.fromCharCode(13)を使う必要があるようだ。
つまり、エスケープ文字は無視されるためString.fromCharCode(n)を使う。

スクリプトエディタの場合

app = Application.currentApplication();
app.includeStandardAdditions =true;

function loto(x,y){

	function nGen(){ return ("00" + Math.ceil( Math.random()*y )).substr(-2) }

	for( var list = [ nGen() ]; list.length < x; ){
		if( list.indexOf( a = nGen() )<0 ) list.push( a );
	};
	return list;
}

result  = loto( 5, 31 ).sort().toString() + String.fromCharCode(13);
result += loto( 6, 43 ).sort().toString() + String.fromCharCode(13);
app.displayAlert( result + loto( 7, 37 ).sort().toString() );

結果

01,02,23,28,29
09,18,29,32,38,43
05,13,18,24,28,33,37

Automatorの場合

function run(){

	function loto( x, y ){
		function nGen(){ return ("00" + Math.ceil( Math.random()*y )).substr(-2) }
	
		for( var list =[ nGen() ]; list.length < x; ){
			if( list.indexOf( a = nGen() )) list.push( a ) ;
		};
		return list;
	}
	
	result  = loto( 5,31 ).sort().toString() + String.fromCharCode(13);
	result += loto( 6,43 ).sort().toString() + String.fromCharCode(13);
	return	result + loto( 7,37 ).sort().toString();
}

結果：結果を表示するには、"JavaScript を実行"->"結果を表示"の順にアクションを配置する。

{"01,07,11,19,26
15,24,29,33,37,39
08,09,16,24,27,29,37"}

Automator で Javascript

Automator でワークフローを開いたら"JavaScript を実行"というアクションが追加されていた。

Yosemite になって Javascript for Automation が追加されたんだから当然のことではあるが。
早速、"JavaScript を実行"のアクションを選択してみたら、以下のようなスクリプトが表示された。

function run(input, parameters) {
	
	// Your script goes here

	return input;
}

input が関数の引数にも return のパラメータにも出てくるというのがよく分からない？
とりあえず"JavaScript を実行する"アクションに以下のような script を書いた。

function run() {
	return( "Hello World!" );
}

これに続けて"結果を表示"アクションを配置し、ワークフローを実行してみた。

{"Hello World!"}

LotoNumbers の生成

"JavaScript を実行"のスクリプトを以下のように書き換える。

function run(){

	function loto( n, c ){
		var	numbers = []; 
		
		function lotoGen(){ return ("00" + Math.ceil( Math.random()*c )).substr(-2) }
	
		for( numbers.push( lotoGen() ); numbers.length < n; ){
			if( numbers.indexOf( a = lotoGen() )) numbers.push( a ) ;
		};
		return numbers;
	}
	
	var result = [];
	result.push( "[miniLoto:" + loto( 5,31 ).sort().toString() + "]" );
	result.push( "[Loto6:" + loto( 6,43 ).sort().toString() + "]" );
	result.push( "[Loto7:" + loto( 7,37 ).sort().toString() + "]" );
	
	return result.toString();
}

これに続けて"結果を表示"アクションを配置し、ワークフローを実行した結果。

[miniLoto:07,13,17,24,29],[Loto6:01,13,18,22,37,41],[Loto7:01,03,12,18,26,35,37]

"\n" で改行しないので…

Javascript for Automation で droplet を作る方法

Javascript Javascript for Automation OSX Automator

…を知ったので、なんかネタないかと考えていたけど思いつかなかった。

function openDocuments(docs){
	app = Application.currentApplication();
	app.includeStandardAdditions =true;
	...
}

引数 docs に drag&drop された filepath のリストが格納される。
指定された fiile に対して何をするか…の部分は、結局 AppleScript のライブラリを覚えてないと do Shell script 一択になってしまい、あんまりMacの有難味がないような気がする。昔は頑張ってAppleScript触ってたけど、中断した時期が長く、最近では再び触らなくなっていたから。

実践

SQL コマンドファイルの作成

sampleSQLcmd.txt の内容
※ sqliteではカラム名に日本語などを含む場合はsingle-quote (')で囲う。

CREATE TABLE list ( id, title );
INSERT INTO list ( id, title ) values( 1, '天上天下唯我独尊' );
INSERT INTO list ( id, title ) values( 2, '有朋自遠方来不亦楽乎' );
INSERT INTO list ( id, title ) values( 3, '国破山河在城春草木深' );
SELECT * FROM list;

まずPowerShell consoleのコードページをUTF-8に設定

UTF-8にしたのは、Webでは一般的だから…くらいの意味しかない。

PS C:\Users\hoge\Desktop\dbtoolWithSQLite3> chcp 65001
Active code page: 65001

PowerShellのconsoleからSQLite3の.readコマンドをSQLite3に渡して実行

PS C:\Users\hoge\Desktop\dbtoolWithSQLite3> ".read ./sampleSQLcmd.txt" | ./sqlite3.exe ./sample
1|天上天下唯我独尊
2|有朋自遠方来不亦楽乎
3|国破山河在城春草木深

PowerShellのconsoleからsqlコマンドを直接渡すこともできる

PS C:\Users\hoge\Desktop\dbtoolWithSQLite3> ./sqlite3.exe ./sample "SELECT * FROM list"
1|天上天下唯我独尊
2|有朋自遠方来不亦楽乎
3|国破山河在城春草木深

sqliteの外部スクリプトではなくPowerShellスクリプトの中でsqlコマンドをヒアドキュメントで渡してみた

SQLiteViaConsole.ps1 の内容

chcp 65001
$cmd = @"
CREATE TABLE list ( id, title );
INSERT INTO list ( id, title ) values( 1, '天上天下唯我独尊' );
INSERT INTO list ( id, title ) values( 2, '有朋自遠方来不亦楽乎' );
INSERT INTO list ( id, title ) values( 3, '国破山河在城春草木深' );
SELECT * FROM list;
"@

./sqlite3.exe ./sample2 $cmd

実行結果

PS C:\Users\hoge\Desktop\dbtoolWithSQLite3> C:\Users\hoge\Desktop\dbtoolWithSQLite3\SQLiteViaConsole.ps1
Active code page: 65001
1|&#65533;V&#65533;&#65533;V&#65533;&#65533;&#65533;B&#65533;&#65533;&#401;&#65533;
2|&#65533;L&#65533;&#65533;&#65533;&#65533;&#65533;&#65533;&#65533;&#65533;&#65533;&#65533;&#65533;s&#65533;&#65533;&#65533;y&#65533;&#65533;
3|&#65533;&#65533;&#65533;j&#65533;R&#65533;&#845;&#1871;&#65533;t&#65533;&#65533;&#65533;&#1552;[

…文字化けしている
.ps1ファイルをサクラエディタで開き、ファイルタイプを確認したら、[UniBE BOM付き]となっていたため、UTF16BE <-> UTF8 の問題か？

code pageをUTF16BEにしてみる

SQLiteViaConsole+.ps1 の内容

chcp 1201

$cmd = @"
CREATE TABLE list ( id, title );
INSERT INTO list ( id, title ) values( 1, '天上天下唯我独尊' );
INSERT INTO list ( id, title ) values( 2, '有朋自遠方来不亦楽乎' );
INSERT INTO list ( id, title ) values( 3, '国破山河在城春草木深' );
SELECT * FROM list;
"@

./sqlite3.exe ./sample3 $cmd

実行結果

PS C:\Users\hoge\Desktop\dbtoolWithSQLite3> C:\Users\hoge\Desktop\dbtoolWithSQLite3\SQLiteViaConsole+.ps1
chcp.com : 無効なコード ページです
発生場所 C:\Users\hoge\Desktop\dbtoolWithSQLite3\SQLiteViaConsole+.ps1:1 文字:5
+ chcp <<<<  1201
    + CategoryInfo          : NotSpecified: (無効なコード ページです:String) []、RemoteException
    + FullyQualifiedErrorId : NativeCommandError
 
1|天上天下唯我独尊
2|有朋自遠方来不亦楽乎
3|国破山河在城春草木深

エラーは帰ってくるが、表示は正常…結果オーライ？
多分chcpではなくconsoleのencoding指定をやればいいと思うのだが、今日のところはこれまで。
まぁ、PowerShell scriptの中でsqliteコマンドを書き込むのは、ちょっとした実験をやりたいときだけだと思うので、無理して使う必要は無さそう。

System.Data.SQLiteがあるのになぜわざわざSQLiteをPowerShellのconsoleからコマンド渡しするのかというと…

  1. System.Data.SQLite経由でのSQLiteのドットコマンドの渡し方がわからなかった
  2. （DWH から持ってきた）カラム数とレコード数の大きなデータファイルを、INSERT INTO …で１レコード毎にちまちま渡すより.importコマンドでさらっと読み込みかった

後者のうち、煩雑さという意味では昨日のSystem.Data.SQLite版のsample codeと見比べてもらえば一目瞭然だし、またパフォーマンスという意味ではODBC driver版でも同じことだと思う。
なので、Shell版のSQLiteをPowerShell consoleからちゃんと使えるようにしたい。
もしかしたらSystem.Data.SQLiteならではのうまいやり方が、他にあるのかもしれないけど…

準備

PowerShell SQLite

PowerShell の console の encoding

PowerShell の console はデフォルトで

chcp 932 # MS-SJIS

一方 SQLite3 は Unicode のみ扱える
PowerShell の console から SQlite3 との間で日本語を文字化けせずにやりとりできるようにするため、最初に PowerShell console の code page を

chcp 1200 # UTF16LE
chcp 1201 # UTF16BE
chcp 65001 # UTF8

何れかにする。

PowerShell consoleからsqlite3などの外部コマンドとやり取りする場合…

PowerShellから外部コマンドを実行するとき、外部コマンドへの引数渡しではリダイレクトを利用できない（PowerShell では '<' が予約語になっている）ためGet-Content（=外部ファイルの内容をパラメータとして渡す場合とpipe を使う。

なお、外部コマンド終了の都度セッションが切断・終了されることに注意！
このため、コマンドラインのパラメータや、コマンドファイルは毎回 use [database 名]; に続くマルチステートメントでコマンドを構成する。

あまり重要ではないが…sqlite3.exe（shell コマンド）のプロファイルについて

sqlite3.exe を download したままだと、[ファイルのプロパティ]->[セキュリティ]->[ブロックの解除]button が表示される。つまりクラウドからダウンロードしたファイルなのでファイルがブロックされているが、その状態でも PowerShell から外部コマンドとして呼び出すのに不都合はなかった。