csvsql.cmd - access CSV file like SQL SELECT Statement

コマンドライン上でCSVFileをテーブルに見立てて、select文を書いて、結果を標準出力します。 100,000行や500,000行のCSVファイルから、「この条件を指定してデータを集計しろ、10分で」と言われた時、 方法の一つとして、「Select文が使えたらな・・・」と思う人なら、案外お勧めです。

by kei.nazaki 2008

csvsql.cmd (update 1.081222)

2008/12/22

Download - csvsql1081221.zip

Windows2000-SP4,WindowsXP-SP2,SP3の通常な?複数の環境で チェックする限りは、期待通り動作してます。

コマンドラインオプション等は以下の通り。

csvsql "QUERY" [ /csv | /text ]
"QUERY"
SQL文。記述はJETSQLに従う。DELETE,UPDATE,ALTER,INDEX関連の ステートメントはサポートされない。つまりSELECTぐらいしか使えない。 テーブル名にはファイル名(*.txt,*.csv)を使う。 例えば「"select * from sample.csv where age=28"」のように。 対象とするCSVファイルには列名の入ったヘッダ行が必須。
/csv
カンマ区切り(csv形式)で出力される。
/text
罫線付書式で出力される

また、SQLをファイルに書いておいて実行できるように、以下の書式もサポートしている。

csvsql /file:"FILENAME" [ /csv | /text ]
/file
QUERY をファイルに記述して呼ぶことができる。 例えば、「csvsql /file:"sample.sql"」のように。

・・・正直分かりにくいので、サンプルをいくつか並べる。

C:\temp>dir /b
a.sql
csvsql.cmd
sample1.csv

C:\temp>more sample1.csv
name,score
kik,55
non,78
bub,98

C:\temp>more a.sql
select count(*) as 件数 from sample1.csv

C:\temp>csvsql "select * from sample1.csv" /text
+------+-------+
| name | score |
+------+-------+
|  kik |    55 |
|  non |    78 |
|  bub |    98 |
+------+-------+

C:\temp>csvsql /file:"C:\temp\a.sql" /text
+------+
| 件数 |
+------+
|    3 |
+------+

C:\temp>csvsql "select * from sample1.csv where score > 80" > dummy.csv

C:\temp>csvsql "select * from dummy.csv" /text
+------+-------+
| name | score |
+------+-------+
|  bub |    98 |
+------+-------+

以下コード全体。

@if (false)==(false) echo off
cscript //nologo /E:JScript "%~f0" %*
goto :EOF
@end
// csvsql.cmd 1.081222
// coded by keisuke inazaki
//----------------------------

var $break = {};
Array.prototype.each = function(iterator) {
  try {
    for (var i = 0, length = this.length; i < length; i++)
      iterator(this[i],i);
  } catch (e) {
    if (e != $break) throw e;
  }
  return this;
}
Array.prototype.any = function(iterator) {
  var result = false;
  this.each(function(value, index) {
    result = result || iterator(value, index);
    if(result) throw $break;
  });
  return result;
}
Array.prototype.max = function(f){
  var result;
  var v;
  this.each(function(value){
    v=f(value);
    if(result==undefined || result< v) result=v;});
  return result;
}
Array.prototype.last = function(){
  return (this==undefined)? null: this[this.length-1];
}

String.prototype.strip = function() {
  return this.replace(/^\s+/, '').replace(/\s+$/, '');
}
String.prototype.blength = function(){
  var result = this.length;
  for(var i=0; i<this.length; i++) {
    if(this.charCodeAt(i) >= 128) result++;
    if(this.charAt(i).match(/[。-゚]/)) result--;
  }
  return result;
}
String.prototype.lpad = function(length,padding){
  var result = this;
  var length = length || 1;
  var padding = padding || ' ';
  while((length-result.blength())>0) result = padding + result;
  return result;
}

var CsvSql = {
  query: '',
  result : [],
  objConnection : WScript.CreateObject('ADODB.Connection'),
  toCsv : function(){
    if(this.result == null) return '';
    var o = [];
    for(var i=0; i<this.result.length; i++){
      o.push(this.result[i].join(','));
    }
    return o.join('\r\n');
  },
  toText : function(){
    if((this.result == null)||(this.result[0] == null)) return '';
    var m = [];
    for(var j=0; j<this.result[0].length; j++){
      var a=[];
      for(var i=0; i<this.result.length; i++){
        a.push(this.result[i][j]);
      }
      m[j] = a.max(function(v){return v.blength()});
    }
    var a = [];
    for(var i=0; i<this.result[0].length; i++){
      a.push('-'.lpad(m[i]+2,'-'));
    }
    var sep = '+' + a.join('+') + '+';
    var o = [];
    for(var i=0; i<this.result.length; i++){
      var a = [];
      for(var j=0; j<this.result[i].length; j++){
        a.push(' '+this.result[i][j].lpad(m[j])+' ');
      }
      if(i==0){
        o.push(sep+'\r\n|' + a.join('|') +'|\r\n'+sep);
      }else {
        o.push('|' + a.join('|') +'|');
      }
    }
    o.push(sep);
    return o.join('\r\n');
  },
  execute : function(){
    if(this.objConnection.State != 0) this.objConnection.Close();
    this.objConnection.Open(
      'Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq='+
      WScript.CreateObject('WScript.Shell').CurrentDirectory.toString()+';');
    var objRecordset = WScript.CreateObject('ADODB.Recordset');
    if(this.objConnection.State==0) throw new Error('Connection Open Fail');
    objRecordset = this.objConnection.Execute(this.query);
    if(objRecordset.State==1){
      this.result = [[]];
      e = new Enumerator(objRecordset.Fields);
      for(;!e.atEnd();e.moveNext()) this.result.last().push(''+e.item().Name);
      while(!objRecordset.EOF) {
        this.result.push([]);
        e = new Enumerator(objRecordset.Fields);
        for(;!e.atEnd();e.moveNext()) this.result.last().push(''+e.item());
        objRecordset.MoveNext;
      }
      objRecordset.Close();
    } else {
      this.result = null;
    }
    if(this.objConnection.State != 0) this.objConnection.Close();
  }
};
CsvSql.output = CsvSql.toCsv;

var Parameter = {
  usage: '',
  abort: function(s){
    WScript.StdOut.WriteLine(s || this.usage);
    WScript.Quit(1);
  },
  Named : {
    action: [],
    addAction: function(n,f){
      this.action.push({});
      this.action.last().name = n;
      this.action.last().func = f;
    },
    read: function(){
      e = new Enumerator(WScript.Arguments.Named);
      for(;!e.atEnd();e.moveNext()){
        if(!this.action.any(function(v,i){
            if(v.name==e.item()) {
              v.func(WScript.Arguments.Named(e.item()));
              return true;
            } else { return false; }
        })) Parameter.abort();
      }
    }
  },
  UnNamed : {
    action: [],
    addAction: function(c,f){
      this.action.push({});
      this.action.last().count = c;
      this.action.last().func = f;
    },
    read: function(){
      var args = [];
      e = new Enumerator(WScript.Arguments.UnNamed);
      for(;!e.atEnd();e.moveNext()) args.push(e.item());
      if(!this.action.any(function(v,i){
        if(v.count==WScript.Arguments.UnNamed.Length){
          v.func(args);
          return true;
        } else {return false;}
      })) Parameter.abort();
    }
  }
};

Parameter.usage += '\r\nUsage: csvsql "QUERY" [ /csv | /text ]';
Parameter.usage += '\r\n   or: csvsql /file:<sqlfile> [ /csv | /text ]';
Parameter.Named.addAction('csv',function(){CsvSql.output=CsvSql.toCsv;});
Parameter.Named.addAction('text',function(){CsvSql.output=CsvSql.toText;});
Parameter.Named.addAction('file',function(filename){
  var fso = WScript.CreateObject('Scripting.FileSystemObject');
  if(!fso.FileExists(filename)) Parameter.abort('Error: ファイルが見つかりません');
  var stream = fso.OpenTextFile(filename);
  CsvSql.query = stream.ReadAll();
  stream.Close();
});

Parameter.UnNamed.addAction(0,function(args){
  if(!WScript.Arguments.Named.Exists('file')) Parameter.abort();
});
Parameter.UnNamed.addAction(1,function(args){
  if(WScript.Arguments.Named.Exists('file')) Parameter.abort();
  CsvSql.query=args[0];
});

Parameter.Named.read();
Parameter.UnNamed.read();
try{
  CsvSql.execute();
}catch(e){
  WScript.StdOut.Write('Exception: ');
  WScript.StdOut.WriteLine((e instanceof Error)? e.message: e);
}
WScript.StdOut.Write(CsvSql.output());

[javascript][WSH]

IIF関数を使ってみる

2008/12/22

そもそもAccessをよく知らなかったので、 IIF関数を見つけるまでにCASEとかDECODEとか 試して苦しんだ記憶があります。そんなIIF関数の例。

(a.csv)
社員番号,通勤手段,通勤時間
1,2,20
2,2,15
3,1,50
4,1,30
5,0,10
6,1,70
(資料)
通勤手段 0=徒歩 1=電車 2=車
通勤時間 単位は分
(指示)
この資料から通勤時間が30分を超えている人数を
通勤手段ごとにレポート
※以下、表示の関係上、コマンドが改行しますが、実際は1行です

【まずは見てみる】
C:\TEMP\iif>csvsql "select * from a.csv" /text
+----------+----------+----------+
| 社員番号 | 通勤手段 | 通勤時間 |
+----------+----------+----------+
|        1 |        2 |       20 |
|        2 |        2 |       15 |
|        3 |        1 |       50 |
|        4 |        1 |       30 |
|        5 |        0 |       10 |
|        6 |        1 |       70 |
+----------+----------+----------+

【通勤手段ごとにグループ】
C:\TEMP\iif>csvsql "select [通勤手段] from a.csv group by [通勤手段]" /text
+----------+
| 通勤手段 |
+----------+
|        0 |
|        1 |
|        2 |
+----------+

【通勤手段ごとの人数(個数ってなってるけど)】
C:\TEMP\iif>csvsql "select [通勤手段],count([通勤手段]) as 個数 from a.csv
 group by [通勤手段]" /text
+----------+------+
| 通勤手段 | 個数 |
+----------+------+
|        0 |    1 |
|        1 |    3 |
|        2 |    2 |
+----------+------+

【iif関数で条件に合った人をカウントする】
C:\TEMP\iif>csvsql "select [通勤手段],sum(iif([通勤時間]>=30,1,0)) as
 通勤30分以上の人 from a.csv group by [通勤手段]" /text
+----------+------------------+
| 通勤手段 | 通勤30分以上の人 |
+----------+------------------+
|        0 |                0 |
|        1 |                3 |
|        2 |                0 |
+----------+------------------+

【iif関数の入れ子の例】
C:\TEMP\iif>csvsql "select iif([通勤手段]=0,'徒歩',iif([通勤手段]=1,'電車','車'))
 as 通勤の手段 ,sum(iif([通勤時間]>=30,1,0)) as 通勤30分以上の人
 from a.csv group by [通勤手段]" /text
+------------+------------------+
| 通勤の手段 | 通勤30分以上の人 |
+------------+------------------+
|       徒歩 |                0 |
|       電車 |                3 |
|         車 |                0 |
+------------+------------------+

[javascript][WSH]

各列の型を指定するためには

2008/12/22

例えば、IPアドレスが入っている列があるとします。 普通にcsvsqlでselectすると、数値として読まれてしまいます。

C:\temp>type ip.csv
ip,host
1.2.3.4,AAA
55.66.77.88,BBB
255.0.0.255,CCC

C:\temp>csvsql "select * from ip.csv" /text
+----------+------+
|       ip | host |
+----------+------+
|    1.234 |  AAA |
|  55.6677 |  BBB |
| 255.0025 |  CCC |
+----------+------+

この場合、定義ファイルschema.iniを生成し、列の型を指定できます。 まず、create tableで適当な名前のテーブル名で、目的の表と 同じ構成になるようにファイルを生成します。 すると、同じディレクトリにschema.iniが出来上がります。

C:\temp>csvsql "create table sample.csv (ip varchar(15), host char(10))"

C:\temp>dir /b
csvsql.cmd
ip.csv
sample.csv
schema.ini

C:\temp>type schema.ini
[sample.csv]
ColNameHeader=True
CharacterSet=932
Format=CSVDelimited
Col1=ip Char Width 15
Col2=host Char Width 10

そして、schema.iniの"[*.csv]"を編集し、目的のファイル名にします。 今回の例では、sample.csvとなっていたのを、ip.csvと変えました。 これだけです。結果、カラム[ip]は文字列として認識されました。

C:\temp>type schema.ini
[ip.csv]
ColNameHeader=True
CharacterSet=932
Format=CSVDelimited
Col1=ip Char Width 15
Col2=host Char Width 10

C:\temp>csvsql "select * from ip.csv" /text
+-------------+------+
|          ip | host |
+-------------+------+
|     1.2.3.4 |  AAA |
| 55.66.77.88 |  BBB |
| 255.0.0.255 |  CCC |
+-------------+------+

もちろん、schema.iniを手で書いてもいいですが、こっちの方が正確かつ 簡単です。

[javascript][WSH]

Microsoft Text Driver の制限

2008/12/22

http://support.microsoft.com/kb/178717 の通り。

・DELETE、 UPDATE、 CREATE INDEX、 DROP INDEX 
 ALTER TABLE の各ステートメントをサポートしない
・空の文字列と NULL データの区別する方法がない

[javascript][WSH]

(RFYL - index)