こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

固定長のデータをmysqlで取り込む方法

実は、かなり悩んでいるため、助けて(教えて)いただけないでしょうか?
なおMysqlのバージョンは5.5.31です。

・hoge_test.txtの中身
011010000000009     043.052927141.3446931
011000000000009     043.059576141.3581841
011010010000009     043.036466141.3242842
011010010010009     043.038999141.3229683
0110100A0020009     043.037663141.3252133
0110100E0030009     043.037048141.3238143

※真ん中の     の部分は2バイトの空白5文字。。。
先頭の11桁分は、数字+A~Z、
test6とtest7にあたる部分には、43.059576や141.358184などといった
小数点(少数6桁)のデータがあります。

といったhoge_test.txtの固定長のデータ(改行コードCR/LF)があるとき、

CREATE TABLE `hoge_test`.`hogehoge` (
`test1` varchar(11) DEFAULT NULL,
`test2` varchar(3) DEFAULT NULL,
`test3` varchar(1) DEFAULT NULL,
`test4` varchar(5) DEFAULT NULL,
`test5` varchar(1) DEFAULT NULL,
`test6` varchar(9) DEFAULT NULL,
`test7` varchar(10) DEFAULT NULL,
`test8` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

上記のように定義して、以下のような形で取り込んでみました。
※小数点部分はDOUBLEを使うのもありですが・・・うまくいかなかったので文字列として取り込むことに

set character_set_database=sjis;
LOAD DATA LOCAL INFILE "hoge_test.txt"
INTO TABLE hoge_test.hogehoge
FIELDS
TERMINATED BY ''
OPTIONALLY ENCLOSED BY ''
LINES
TERMINATED BY '\r\n'
;

上記を実行すると、こんな感じのような形となります。
'01101000000', '271', '6', '1', '', '', '', ''

これを以下のようにしたいのですが、

そももそLOAD DATA で、このような固定長データを読み込むは難しいのでしょうか?

オラクルでいうところのPOSITION句で各項目のレコード上の位置を指定はできないのでしょうか?
http://oracle.se-free.com/utl/C2_kotei_load.html

それとも単純に私の定義や取り込む方法がマズイのでしょうか?(だから取り込めないんだと思っていますが。)

最終的に、どうしたいかというと、以下のような形で、各カラムにデータが入ればOKです。
'01101000000','000','9','     ','0','43.052927','141.344693','1'

そのままLOAD DATAで取り込みたいですが、この際元データをsedなどのlinux系標準のコマンドで
カンマ区切りやタブ区切りに変換して、LOAD DATAで読み込んでも構わないです。
(ただエクセルで操作してというのは無しでお願いします。)
※データ量としては、200M単位です。


hoge_test.txtを sedで○○○○○で置き換えて、定義とLOAD DATAで、○○○○○で
こういう風に○○○○○書けば取り込めばできるよ
みたいな形でコードを教えていただけると助かります。


お手数をおかけいたしますが、よろしくお願いいたします。

投稿日時 - 2014-04-10 23:47:25

QNo.8549755

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

ちなみにこんな感じ

create table hogehoge (
test1 char(11) DEFAULT NULL,
test2 char(3) DEFAULT NULL,
test3 int DEFAULT NULL,
test4 char(5) DEFAULT NULL,
test5 int DEFAULT NULL,
test6 double DEFAULT NULL,
test7 double DEFAULT NULL,
test8 int DEFAULT NULL
);

create temporary table tmp (data char(41));

insert into tmp values
('011010000000009     043.052927141.3446931')
,('011000000000009     043.059576141.3581841')
,('011010010000009     043.036466141.3242842')
,('011010010010009     043.038999141.3229683')
,('0110100A0020009     043.037663141.3252133')
,('0110100E0030009     043.037048141.3238143');
//ここをload処理にする?

insert into hogehoge
select
mid(data,1,11)
,mid(data,12,3)
,mid(data,15,1)
,mid(data,16,5)
,mid(data,21,1)
,mid(data,22,9)
,mid(data,31,10)
,mid(data,41,1)
from tmp;

//表示
select * from hogehoge;

投稿日時 - 2014-04-11 13:17:02

ANo.1

一行を1データとしてテンポラリテーブルに読み込んで、
分割しながら流し込めばいいのでは?

投稿日時 - 2014-04-11 09:22:57

あなたにオススメの質問