[-d database_file]
[-i input_file[::sheet_name[::top_left_cell_address[::row_count['x'column_count]]]][/table_name[/A][/X][/R][/C][/N]]]
[-e encoding] [-h] [-t] [-l delimiter_char] [-f fields] [-1] [-a]
[-x file_format] [-s skip_count] [-m max_row_count]
[-o output_file[::sheet_name[/P][::top_left_cell_address[::row_count['x'column_count]]]][/table_name]]
[-E encoding] [-H] [-L delimiter_char] [-N] [-X file_format]
[-Z error_message]
[-J setting]
[-q query_string] [-Q query_file] [-c query_file_encoding]
[-p param_name=param_value]
[-r "query_replace_from::query_replace_to"]
[-A]
[-I interrupt_file][/check_count]
([]のオプションは省略可能)
-i input_file[::sheet_name[::top_left_cell_address[::row_count['x'column_count]]]][/table_name[/A][/X][/R][/C][/N]]
入力ファイルを指定する(ワイルドカード指定可能)
対応ファイルは以下
対象のシートを指定する(Excel形式のみ有効)
ワイルドカード指定可能。
空または「*」とすると、全てのシートが対象となる。
※ sheet_name は存在しなくてもエラーにならない。存在しない時にエラーとしたい場合には、table_name を指定した上、/R 指定すること。
データを取り出す範囲を限定する(Excel形式のみ有効)
-i input.xls::sheet1::B5::10x5
この指定は、input.xls の sheet1 の B5 セルから 10行x5列の範囲を取り出す。
後ろから順に省略が可能で、省略した場合は全部の範囲が対象となる。
また、-i input.xls
のみ指定した場合は、input.xls の全てのシートのA1から UsedRange で認識される全てのデータが対象となる。
指定した名前のテーブルにデータを投入する。
input_file がワイルドカード指定の場合は必須。
ワイルドカード指定の場合、該当するファイルの全てのデータを table_name に投入する。
これらのデータは項目の数や構成が異なっている場合、全てのデータの項目を包含したテーブルが作成される。
単一ファイルで、table_name の指定がない場合のテーブル名は以下
-i
を複数回指定し、同じ table_name を指定した場合は、指定した input_file のデータ項目を全て包含したテーブルが作成され、全てのデータを投入する。
テーブルの項目名は入力フィあるから得た名前を全て小文字にして作成される。
※大文字小文字を保つ場合は/C
オプションを指定する。
1行の入力データに同一名称の項目が複数ある場合は、最後に読み込んだ値が採用される。
※input_file は存在しなくてもエラーにならない。存在しない時にエラーとしたい場合には、table_name を指定した上、/R 指定すること。
データを投入する前にテーブルを削除せず、レコードを追加する。
データベースファイルを指定する場合で、一度の実行の後、同じテーブルにデータを追加したい時に指定する。このとき、既存のテーブルに無い項目を持つデータを指定するとエラーとなる。
同一テーブル名を複数回指定した場合、うち一つでもこの指定があれば、全てに指定したのと同一の効果となる。
入力元の制御情報(ロード元のファイル名や行番号)を記録しないでテーブルを作成する。
制御情報は、通常のクエリでは取り出されないが、項目に別名をつけることで取り出せる。
ただ、この場合、SELECT DISTINCT * を行っても、入力元行番号の影響で、期待の結果は得られない。これを抑制するための指定。
同一テーブル名を複数回指定した場合、うち一つでもこの指定があれば、全てに指定したのと同一の効果となる。
テーブルが作られない時にはエラーとする。
テーブルが作られない原因は、入力のファイルがないか、レコードがない場合となる。
同一テーブル名を複数回指定した場合、うち一つでもこの指定があれば、全てに指定したのと同一の効果となる。
入力データから項目名を作成するとき、大文字小文字を保持する。
ひとつの -i
オプションで指定した入力データ(複数あり)内に、同一名称(大文字小文字区別せず)の項目が複数ある場合は、最後に読んだ項目名が列名となる。(1ファイルであれば、1行内の後ろの項目。入力が抽出データ形式(xml,xbd)ならば、より後ろの行でかつ1行内で後ろの項目。複数のファイルであれば、より後に読んだファイルでの最も後ろの項目。複数のファイルは、ファイル名の昇順に処理する。)
同一テーブル名を複数回指定した場合、うち一つでもこの指定があれば、全てに指定したのと同一の効果となる。
読み込んだ行の列がテーブルの列に対して足りないときに、その列にはNULL値が挿入される。
これにより、「入力データ中に項目があるが値が空」と、「入力データ中に項目自体が無い」を区別することが可能となる。
既定では、これは区別なく、テーブルには空文字列が入る。
入力ファイルの文字コード(SJIS, EUC, JIS, UTF8, UTF16のどれか) を指定する。
入力が CSV ファイルの時のみ有効。
省略時は SJIS とみなす。
データの1行目の値を項目名として使用することを指定する。指定がない場合、項目名は item1, item2,…のようになる。
入力が CSV、Excelファイルの時のみ有効。
指定があっても空の場合は、item1 … の名前をつける。
項目の値の前後の空白は取り除かれる。また、大文字小文字の区別はつけず、半角英字は小文字となる。
データ項目の前後の空白を取り除く。
項目を区切っている文字を指定する。
入力が CSV ファイルの時のみ有効。
-f 1 |
1番目の項目 |
-f 1,2 |
1 と 2 |
-f 1-3 |
1 から 3 |
-f 名前,住所,電話番号
項目を区切らずに改行毎に1項目として扱う。入力が CSV ファイルの時のみ有効。
-l
の指定と重複した場合、こちらが優先される。引用符はデータとして扱う。
例えば、“aaa”,“bbb”,“ccc” の行は、このままの形で登録される。
file_format には、XLS|XBD|XBD1|CSV|EXCEL のいずれかが指定できる。
入力ファイルの拡張子に関わらず、指定の形式として扱う。このため、読み込めない場合がある。
ファイルをワイルドカードで指定した時にこの指定をすると、全てのファイルを指定の形式として扱う。
また、この他の入力に関するオプションは、ここで指定を行った場合適宜有効となる。(たとえば、CSVファイルにのみ有効なオプションなど)
通常、XBD形式のファイルは、すべての行(ROWSタグ)を走査して項目を列挙するため、その他の形式に比べて低速となる。対象のデータの各行が同じ項目である事が明らかな場合は、この指定をすることによって、最初のROWSタグのみを項目列挙に使用する。
skip_count が指定されている場合は、項目列挙に使用するのは、指定した数の読み飛ばしを行ってから、最初のROWSタグ内の項目となる。
通常、Excel形式のファイルは、MS Excelを使用せずに読み込みを行う(XLS指定のときも)が、EXCEL 指定をした時は、MS Excel を使って読み込む。
これは、非常に低速になるが、通常の処理ではうまく読めないデータがある場合に使用する。
読み飛ばす行数を指定する。入力ファイルの種類により次のように動作する。
最大で読む行数を指定する。入力ファイルの種類により次のように動作する。
テーブル列名に使用できない文字が含まれているために、入力データから列名が生成できない場合、エラーとせずに、エラーの原因となる文字を除去して名前をつける場合に指定する。この結果、元々エラーでなかった他の列と同一名となる場合は連番を付加する。
なお、入力データの列名に相当するものが空の場合は、このオプションが指定されていなくても、自動生成する。
-o output_file [::sheet_name[::top_left_cell_address[::row_count['x'column_count]]]][/table_name]
クエリの結果を出力する出力ファイル。
クエリ、table_name 共に指定が無い時は無視される。
table_name 指定があるとき、クエリ指定は無視される。ファイル名に “?” を含む場合には、その文字をクエリの出力列名のうち、"file_label"という名前の列の値で置換して出力する(結果、出力ファイルが複数となる場合がある)。
この時、file_label の値がファイル名として不正な文字を含むとエラーになる。file_label の値はデータ中に出力されない。
次の形式で出力可能(ファイルの拡張子で判断)
抽出データ形式(拡張子 xbd, xml)
Excel形式(拡張子が xls、xlsxなど)
CSV 形式(上記にあてはまらない拡張子のとき)
sheet_name に “?” を含む場合には、? 文字をクエリの出力列名のうち、"sheet_label"という名前の列の値で置換して出力する(結果、出力シートが複数となる場合がある)。
Excel形式の場合は ::sheet_name の指定が必須。Excel形式以外の場合、::sheet_name 指定は無視する。
結果的にシート名が不正となる場合(使用できない文字が含まれる場合など)は、エラーになる。
指定がある場合、指定したシートが存在しないときは無視する。
指定のないときは、シートが存在しなければ作成する。
Excel形式の場合は データを出力する範囲を限定できる。
指定の無い場合は、A1セルからデータのある分だけ出力する。
例:
-o output.xls::sheet1::B5::10x5
この指定は、output.xls の sheet1 の B5 セルから 10行x5列の範囲にデータを出力する。
出力のデータがこの範囲に収まらない場合は、それらのデータは切り捨てられる。
出力データがこの範囲に満たない場合は、そのまま出力する。
※ クエリの列名による、セルのアドレス指定での出力列名が “cell_label_” で始まる列は、以降をセルのアドレスとして扱い、常にその場所へ出力します。
例えば、次のようなクエリの場合
SELECT
社名 cell_label_C10,
住所 cell_label_E15,
明細ID,
品名,
金額
FROM TABLE_A
社名は常に C10セル、住所は常に E15 に上書き出力され、その他
の列(明細ID、品名、金額)は、一覧出力されます。
指定した名前のテーブルの全件を出力する。この指定は、クエリ指定よりも優先される。
出力ファイルの文字コード(SJIS, EUC,JIS,UTF8, UTF16のどれか) を指定する。省略時は SJIS とみなす。
出力が CSV ファイルの時のみ有効。
1行目に項目名を出力する。
出力が CSV、Excelファイルの時のみ有効。
項目を区切る文字を指定する。
出力が CSV ファイルの時のみ有効。
項目をダブルクオーテーションで囲まない時には指定する。
出力が CSV ファイルの時のみ有効。
file_format には、XLS|XBD|CSV|EXCEL のいずれかが指定できる。
出力ファイルの拡張子に関わらず、指定の形式として扱う。
また、この他の出力に関するオプションは、ここで指定を行った場合適宜有効と
なる(たとえば、CSVファイルにのみ有効なオプションなど)。
通常、Excel形式のファイルは、MS Excelを使用せずに出力を行う(XLS指定のときも)が、EXCEL 指定をした時は、MS Excel を使って出力する。
これは、非常に低速になるが、通常の処理ではうまく出力できないデータがある場合に使用する
クエリ結果が0件のとき、error_message を出力して警告終了するには指定する。
クエリ、および出力ファイルの指定が無い時にはこの指定は無視される。
<内部使用のためのオプションで、通常使用できません>
レポート作成設定の JSON 文字列、または設定ファイル名を指定する。
指定の値のファイルがあれば、設定ファイル名と解釈し、そうでなければ JSON 文字列として解釈する。いずれも正しい設定として読み込めなければエラー。
この指定があると、次の指定は無視する。
-o
オプションのファイル名以外のすべての指定
-E
-H
-L
-N
の指定
-X
の EXCEL 以外の指定
データベースからデータを出力するときのクエリ文字列。
-Q
といずれも指定がなければ、データの取り出しは行わない。
クエリの指定があり、-o
(出力ファイル)の指定がなければ、データを取得しないクエリ(UPDATE句など)とみなし実行する。
※クエリにエラーがある場合、警告終了となる。異常終了としないのは、投入データの内容によって、対象テーブルがない場合などと、本当の文法エラーの区別が判断できないため。
これは、エラーメッセージから人によって判断が必要となる。
※出力をXMLファイル(抽出データ形式)にする場合には、取得した項目名はXMLのタグとして利用できるルールに準じている必要がある。
※SELECTクエリの時は、複数のクエリを指定しても、最初のクエリの結果のみ取得される。SELECTでない場合は、「;(セミコロン)」で区切ることより、複数のクエリを一度に実行できる。
※クエリに使えるSQLの文法は、SQLite で解釈できるものに準じており、後述する拡張関数を使用できる。
クエリ文字列が記載されたファイル。-q
と同時に指定した場合、こちらが優先される。
クエリファイルの文字コード(SJIS, EUC, JIS, UTF8, UTF16のどれか)を指定する。
省略時は SJIS とみなす。
データベースに対して、トランザクションを開始せずにクエリを実行する。
トランザクション内で実行出来ない、管理コマンドなどの実行用。
クエリ内に、@param_name の形式で記述したパラメータに対して、param_value で与えた値を割り当てる。
例:SELECT * FROM table where name = @myname
に値「sanpei」を割り当てるには、
-p myname=sanpei
などとする。
これはいわゆる「パラメータクエリ」なので、通常 WHERE句の条件の値を変更した
り、INSERT 文の値を変更するのに使うが、SELECT の対象テーブル名を変更したり
はでできない。このような用途には、-r
を使うこと。
クエリ文字列内の query_replace_from を query_replace_to に置換する。
パラメータとして使用できないような置換を行うために使う。クエリ文字列の指定が無い時は、無視される。
パラメータクエリの指定と同時に指定される場合、パラメータの設定よりも前に、置換処理が行われる点に注意のこと。
複数指定することができるが、query_replace_from が同一の場合、より後ろで設定した内容が優先される。
また、置換処理は指定した順番にクエリ全体に対して行われるので、前の置換の結果に対して後続の置換が行われる点も注意のこと。
値の小数点以下を四捨五入する。値が数値でないときは0。
文字列値が指定した正規表現にマッチすれば true。
文字列値のうち、パターン文字列に合致する部分を、指定した文字列で置換する。
値を全角に変換する。
値を半角に変換する。
指定した書式で値を変換する。この書式は、.NETの string.Format の書式指定文字列と同等。
パス文字列を連結する。
パス文字列値から、ディレクトリ部分を抜き出す。
パス文字列値から、ファイル名の部分を抜き出す。拡張子の有無:0-拡張子を含む、1-拡張子を除く。
パス文字列値から、拡張子の部分を抜き出す。
例:‘abc.txt’ → ‘.txt’ を得る。
Excel準互換の数式の結果を得る。値を指定したときは、これを EX_FORMAT と同等の書式文字列と扱って、変換した結果の数式文字列を評価する。
Excel の日付シリアル値を、日付の文字列に変換する。EX_FORMAT と同じ書式指定が可能。
値を、指定した書式の日付として扱い、年、月、日の加算を行って、指定の書
式で出力する。
例:
SELECT
EX_DATECALC(日付の列名, 'yyyy/M/d', 1, 1, 1, 'yyyy-MM-dd')
FROM TBL
‘yyyy/M/d’ 形式で格納されている日付の列の値の、1年と1ヶ月と1日後を求め、‘yyyy-MM-dd’ の形式で出力します。
加算値は、マイナス値も指定可能。
年を加算 → 月を加算 → 日を加算の順に個別に処理されます。
書式には .NET のカスタム日付書式文字列が指定できます。和暦には対応
していません。
複数行の値を1項目に連結する。
例:
GRP | MNO | NAME
-----+-----+------------------------
001 | 1 | 名前A
001 | 2 | 名前B
001 | 3 | 名前C
002 | 1 | 名前D
002 | 2 | 名前E
003 | 1 | 名前F
このようなデータ(tbl)を
SELECT GRP, EX_GROUP_CONCAT(NAME, ',', 0, 0, 0, 0) FROM tbl GROUP BY GRP
とすると、
GRP | EX_GROUP_CONCAT(NAME, ',', 0, 0, 0, 0)
-----+------------------------------
001 | 名前A,名前B,名前C
002 | 名前D,名前E
003 | 名前F
が得られる。
最大連結数:1 以上の整数を指定すると、連結する要素の数を制限する。
指定があれば、実行時にこのファイルを作成する。
処理中にこのファイルが削除されたら、処理を中断して終了する。
ファイルがすでに存在する場合もエラーとならない。
check_count は、中断のチェックを行うレコードの間隔を示す。既定値は10000。
この値が大きいほど、中断チェックを行う処理が、本来の処理に与える影響は小さい。
-i aaa.xml -o bbb.xml -q "select * from aaa where xxx is not null"
-i aaa.xml -o bbb.xml -Q query.sql
-i aaa.xml/TABLE_A -o bbb.xml -Q query.sql
(この例は無意味ですが、XML→CSVなど形式を変換したいときに使います)
-i aaa.xml/TABLE_A -o bbb.xml/TABLE_A
-i aaa.xml/TABLE_A -i ddd.xml/TABLE_A -o bbb.xml -Q query.sql
このとき、TABLE_A は全ての入力 xml の項目をもったものになります。
-i *.xml -o bbb.xml -Q query.sql
各 xml は項目が異なっていても構いません。
DBに作成されるテーブルは、全ての入力 xml の項目をもったものになります。
テーブル名は、最初に *.xml にマッチしたファイル名から拡張子を抜いたものになります。
上と同様ですが、DBのテーブル名を明示的に指定しています。全てのデータが TABLE_A に入ります。
-i *.xml/TABLE_A -o bbb.xml -Q query.sql
-i aaa.xml/TABLE_A -d work.db (一回目の実行)
-i ccc.xml/TABLE_A/A -d work.db (二回目の実行)
-d
でデータベースファイル名を指定します。ファイルがなくても作成されます。
この場合は、aaa.xml, ccc.xml は同じ項目を持っていなくてはなりません。
-i aaa.xml/TABLE_A -d work.db
-d work.db -o bbb.xml -Q query.sql
-d work.db -q "DELETE FROM TABLEXXX"
-i aaa.xml -o xxx.xml -Q query.sql -p param1=AAAAA -p param2=BBBBB
クエリファイルには次のように記述していて、
SELECT * FROM mytable
WHERE nendo = @param1
and month = @param2
@に続く変数に、値を与えて実行します。
このような変数は、全ての文言に使えるわけではありません。例えば、
@param1 * FROM mytable
として、param1 に “SELECT” を与えるようなことはできません。
-Q
が優先されます。
-d work.db -o bbb.xml -Q query.sql -c UTF8
-i aaa.xml -o bbb.xml -q "select name 名前 from aaa"
-i 売上.xml -i 商品.xml -o bbb.xml -Q query.sql
query.sql は例えば、
SELECT
s.商品名 商品名 ← (別名を付けないと「s.商品名」と出力されます)
,u.金額 金額
FROM 売上 u INNER JOIN 商品 s
ON u.商品コード = s.商品コード
など・・
-i 売上.xml -i 商品.xml -o bbb.xml -Q query.sql
query.sql は例えば、
SELECT
u.商品コード マスタにない商品コード
,s.商品コード
,u.金額 金額
FROM 売上 u LEFT OUTER JOIN 商品 s
ON u.商品コード = s.商品コード
WHERE s.商品コード is null
など・・
-i aaa.xml -o bbb.xml -q "SELECT * FROM aaa ORDER BY 金額"
-i aaa.xml -o bbb.csv/aaa
または
-i aaa.xml -o bbb.csv -q "SELECT * FROM aaa"
-i aaa.xml -o 売上_?.xml -Q query.sql
query.sql は
SELECT
店名 file_label ← この名前のカラムの値は、出力ファイルの "?" の場所に埋め込まれる
,売上金額
FROM aaa
とすると、
売上_東京.xml
売上_大阪.xml
....
のようにファイルができる。
-i bbb.csv -o aaa.xml/bbb
または
-i bbb.csv -o aaa.xml -q "select * from bbb"
※ クエリでは、テーブル、カラム、SELECT等のキーワードは大文字小文字を区別しません。
-i *.csv/allcsv -o aaa.xml/allcsv
-i bbb.csv -h -o aaa.xml/bbb
-i *.csv/allcsv -h -o aaa.xml/allcsv
※ 例えば、
csv-A
ID,名前,性別
1,太郎,男
2,花子,女
csv-B
ID,生年月日,社員番号
2,19900101,0808
3,19951220,0900
が、allcsv テーブルとして
ID,名前,性別,生年月日,社員番号
1,太郎,男,,
2,花子,女,,
2,,,19900101,0808
3,,,19951220,0900
と入る。
-i bbb.csv -f 1,3,8-10,20- -o aaa.xml/bbb
-i bbb.csv -e UTF8 -l ! -o aaa.xml/bbb
-i aaa.xls::sheet1
このとき、テーブル名は、ファイル名と同じ aaa になります。
-i aaa.xls
全てのシートが同一テーブルに入ります。このとき項目はすべてのシートの項目を含んだものになります。
ワイルドカードでシートを指定
-i aaa.xls::sheet_*
-i aaa.xls::sheet1 -h
-i aaa.xls::sheet1::C3 -h
-i aaa.xls::sheet1::C3::10x5 -h
-i aaa.xls::sheet1::C3 -f 1-3,7
↓このようにした場合、-f の指定は無視されます。(列数の指定をされているため)
-i aaa.xls::sheet1::C3::10x5 -h -f 1-3
-i aaa.xls::sheet1::C3::10 -f 1-3,7
-f 1-3,7 -h(続く)
-i aaa.xls::sheet1::C3::10x5/TABLE_A (続く) ← -f指定は効きません
-i aaa.xls::sheet2::B5::20x8/TABLE_A (続く) ← -f指定は効きません
-i bbb.xls/TABLE_A (続く) ← -f指定が効きます
(全てのシートを読みます)
-i ccc.xls::sheet_*::E1::5x5/TABLE_A (続く) ← -f指定は効きません
-i ddd.csv/TABLE_A (続く) ← -f指定が効きます
-i eee.xml/TABLE_A ← -f指定は効きません
(そもそもXMLには効かない)
このとき TABLE_A には、CSV, Excelの対象の領域の一行目の項目と、xml の項目の全てを含んだ列ができます。
aaa.csv
あ,い,う
aaa,bbb,ccc
bbb.csv
い,う,え
vvv,xxx,zzz
というファイルを、
-i aaa.csv/TABLE_A -b bbb.csv/TABLE_A -f 2,3
で読み込むと、TABLE_A は
い | う | え |
---|---|---|
bbb | ccc | |
xxx | zzz |
というデータになります。
-o out.xls::sheet1/TABLE_A
-o out.xls::sheet1 -q "SELECT * FROM TABLE_A"
-o out_?.xls::sheet1 -q "SELECT *, name file_label FROM TABLE_A"
↑ ↑
file_label の値で置換する ファイル名の一部にしたい値に
位置に "?" を記述する file_label という列名をつける
-o out.xls::sheet_? -q "SELECT *, name sheet_label FROM TABLE_A"
↑ ↑
sheet_label の値で置換する ファイル名の一部にしたい値に
位置に "?" を記述する sheet_label という列名をつける
-o out.xls::sheet1::C5 -q "SELECT name cell_label_B3, meisai FROM TABLE_A"
↑
出力するセルの位置を指定する
-o out.xls::sheet1::C5::10x5 -q "SELECT * FROM TABLE_A"
select
店
,売上
from 売上データ
order by cast(売上 as int) desc
limit 10
※ 売上は文字列なので、cast しないと正しく並べ替えられません。
select
店
,売上
from 売上データ
order by cast(売上 as int) desc
limit 10
offset 10
select
x.path
,x.filename
,x.ext
,x.sheetname
,t.__xob_row 行番号
,t.*
from t inner join __xob_finfo x
on t.__xob_finfo_rowid = x.rowid
__xob_finfo テーブルはシステムテーブルで、処理を行うと必ず作成されます。
また、各テーブルには、以下のシステム列があります。
__xob_row | 元のファイルの行番号 |
__xob_finfo_rowid | ファイル情報テーブルの rowid |
クエリで select * をした場合、列が __xob で始まる場合は出力されません。
出力したい場合は、__xob 列に別名をつけます。