何十万、何百万レコードといった大容量の固定長ファイルを調査する場合に、エクセルのデータで区切り変換するのは時間もメモリもくいますね。
今時固定長のファイルでやりとりはそうそうないのですが、以前入手ファイルが固定長だったということがありましたので、
その時にどう対応したか記事にいたします。
大したことはやっていないのですが、以下の対応を取りました。
- 1つのtext型で構成される列のみを所持するテーブルを作成
- copyコマンドでローディング
- 固定長の構成通り、substringで区切りごとに列を用意して、create table as select …
では、早速見ていきます。
データの準備
以下のような構成で定義されている固定長ファイルがあったとします。
| 項目名 | 開始 | バイト数 |
|---|---|---|
| orderid | 1 | 8 |
| orderdate | 9 | 8 |
| price | 17 | 8 |
| quantity | 25 | 8 |
| amount | 33 | 8 |
上記の構成になっている以下のような「orderdata.txt」というファイルを作成しました。
A100000120200325000005500000000500002750
A100000120200325000000860000000100000086
A100000120200325000001500000000100000150
A100000120200325000000650000000100000065
A100000120200325000001700000000100000170
A100000120200325000001650000000100000165
A100000120200325000001700000000100000170
A100000220200514000005500000000500002750
A100000220200514000000860000000100000086
A100000220200514000001500000000100000150
A100000220200514000000650000000100000065
A100000220200514000001700000000100000170
A100000220200514000001650000000200000330
A100000220200514000001700000000100000170
A100000320200211000005500000000400002200
A100000320200211000000860000000100000086
A100000320200211000003000000000100000300
A100000320200211000001500000000100000150
A100000320200211000000650000000100000065
A100000320200211000001700000000100000170テーブルの準備
上記の「orderdata.txt」を格納するテーブルを作成します。
create table fixedlength(
fixedlength text
);COPYコマンド
psqlの\copyで「orderdata.txt」を作成したテーブル「fixedlength」にローディングします。
psql -h <ホスト名> -p 5432 -U <ユーザー名> -d <データベース名>
\copy fixedlength from '<フルパス>/orderdata.txt' with encoding 'utf-8' csv ;
セットされたorderdata.txtの確認
fixedlengthにセットされた内容を確認します。
create table as select … で新規テーブルを作成
まずsubstringの構文を確認します。
PostgreSQLのマニュアルでは以下の通りになっていますね。
PostgreSQL 12.4文書
第9章 関数と演算子
9.4. 文字列関数と演算子
https://www.postgresql.jp/document/12/html/functions-string.html
substring(string [from int] [for int])
--戻り値型 text
--部分文字列の取り出し
--substring('Thomas' from 2 for 3) 結果 homしかし、以下の記述もできます。これはお好みなのでしょうか？
--Boxcodeを1文字目から3文字分取り出す
select substring('boxcode',1,3) as name01
,substring('boxcode' from 1 for 3) as name02
結果は、同じです。
それでは本題。
substringで固定長の定義どおりに列を区切って、create table as selectで新たなテーブルを作成し、データセットします。
create table orderdata
as
select cast(substring(fixedlength,1,8) as varchar(8))as orderid
,cast(substring(fixedlength,9,8) as date) as orderdate
,cast(substring(fixedlength,17,8) as numeric) as price
,cast(substring(fixedlength,25,8) as numeric) as quantity
,cast(substring(fixedlength,33,8) as numeric) as amount
from fixedlength;ここはcreate viewでもOKですね
結果は以下の通りです。
以上、固定長ファイルを整理する方法でした。
CSVファイルでも同様の対応をとったことがございます。
その際はstring_to_arrayを使いました。
次回はstring_to_arrayを使ってのCSVファイルでの同様対応を記事にしたく思います。
では。