• 締切済み

固定長のデータを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 それとも単純に私の定義や取り込む方法がマズイのでしょうか？(だから取り込めないんだと思っていますが。) 最終的に、どうしたいかというと、以下のような形で、各カラムにデータが入ればＯＫです。 '01101000000','000','9','　　　　　','0','43.052927','141.344693','1' そのままLOAD DATAで取り込みたいですが、この際元データをsedなどのlinux系標準のコマンドで カンマ区切りやタブ区切りに変換して、LOAD DATAで読み込んでも構わないです。 (ただエクセルで操作してというのは無しでお願いします。) ※データ量としては、200Ｍ単位です。 hoge_test.txtを　sedで○○○○○で置き換えて、定義とLOAD DATAで、○○○○○で こういう風に○○○○○書けば取り込めばできるよ みたいな形でコードを教えていただけると助かります。 お手数をおかけいたしますが、よろしくお願いいたします。

  • MySQL
  • 回答数2
  • ありがとう数0

みんなの回答

  • yambejp
  • ベストアンサー率51% (3827/7415)
回答No.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;

  • yambejp
  • ベストアンサー率51% (3827/7415)
回答No.1

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

  1. カテゴリ
  2. [技術者向] コンピューター
  3. データベース
  4. MySQL

関連するQ&A

  • MySQL Date型にNULLが設定できない。

    csvファイルからMySQLにデータを取り込む際に、Date型やint型にNULLを設定するにはどうしたらよいでしょうか。 以下のようなテーブルに | tTest | CREATE TABLE `ttest` ( `ID` bigint(20) NOT NULL, `title` text NOT NULL, `naiyou` varchar(100) DEFAULT NULL, `kaishi` date DEFAULT NULL, `owari` date DEFAULT NULL, `peirod` bigint(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=sjis | 以下のようなcsvファイルを読み込んだとき(*)、 10,テスト,2008-04-05,2009-04-23,43 11,テスト,2008-04-05,NULL,43 12,テスト,2008-04-05,2009-04-23,NULL ２行目のNULLで ERROR 1292 (22007): Incorrect date value: 'NULL' for column .... とエラーが発生します。 ３行目のNULLでもint型ですが同様にエラーとなります。 一方、insertで行の追加以下のようにした場合は成功します。 insert into tTest values (12,テスト,'2008-04-05',NULL,NULL) 外部ファイルから読み込んだ場合にNULL値を設定するにはどのようにしたらよいのでしょうか。 (*)以下のコマンド LOAD DATA INFILE "E:\\work\\MySQL\\sample.csv" INTO TABLE tTest FIELDS TERMINATED BY "," LINES TERMINATED BY "\r\n";

    • ベストアンサー
    • MySQL
  • LOAD DATA INFILE でエラー起きてデータを一括してロードできません。

    はじめまして。 質問があります。 現在WindowsXP Home、MySQL5.0.27を使用しているのですが、 テキストファイルのデータを一括でDBにロードしようとすると、 エラーが起きてしまいます。 ------------------------ Demo01.txt -------------------------- 1,あああ 2,いいい 3,ううう ------------------------ Demo01.sql --------------------------- create table test ( no integer not null, name varchar(30), primary key(no) )type=InnoDB; ---------------------------------------------------------------- mysql> load data low_priority infile 'C:/Demo01.txt' -> replace -> into table test -> fields terminated by ',' -> lines terminated by '/r/n' -> escaped by '\\'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use : 'escaped by '\\'' 付近: 6 行目 ---------------------------------------------------------------- 上記のようなエラーが出てしまいますので、試しにescaped by '\\' を削除して実行すると、 ---------------------------------------------------------------- Query OK, 1 row affected (0.09 sec) レコード数: 1 削除: 0 Skipped: 0 Warnings: 0 ---------------------------------------------------------------- と表示され１行しかロードできません。どうしたら全行をロードするこ とができるのでしょうか？どうかご教授お願いします。

    • ベストアンサー
    • MySQL
  • BitフィールドにLOAD DATAできない

    以下の定義のテーブル(Bitフィールドを持つ)にLOAD DATAでデータを 入れようとしているのですがエラーになり困っています。 マニュアルにあるようにSET句を使用してLOADをしようとしているのですが 「You have an error in your SQL syntax;～」と コマンドの4行目のところで出てしまいます。どこがおかしいのでしょうか。 【テーブルの定義】 mysql> desc data; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | key1 | int(11) | NO | PRI | NULL | | | key2 | int(11) | NO | PRI | NULL | | | date | datetime | NO | PRI | NULL | | | flg1 | bit(1) | NO | | b'1' | | +-------+----------+------+-----+---------+-------+ 【CSVファイルの内容】 1,2,"2016-12-17 10:00:00",1 1,2,"2016-12-17 11:00:00",0 【コマンド】 LOAD DATA INFILE 'C:/csv/data.csv' INTO TABLE test.data (key1, key2, date, @var1) SET flg1 = CAST(@var1 AS SIGNED) FIELDS TERMINATED BY ',' ENCLOSED BY '"';

  • MySQL SELECTの指定について

    まず、御覧頂きありがとうございます。 早速質問なのですが、 CREATE TABLE `hoge` ( `id` int(8) NOT NULL, `name` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `hoge` (`id`, `name`) VALUES (1, 'hogeA'), (2, 'hogeB'), (3, 'hogeC'), (4, 'hogeD'); CREATE TABLE `test` ( `id` int(8) NOT NULL, `name` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `test` (`id`, `name`) VALUES (1, 'testA'), (4, 'testD'); という値が入ってるテーブルがあるとします。(長くなってすみません) で、 hoge.id と test.id が重複した場合 test.name を表示させたいです。 期待値としては id,name 1.testA 2,hogeB 3,hogeC 4,testD になります。 業務でJoinを使うようなテーブル構造にそもそもしないというのもあり、クリティカルなSQLコードに不慣れで御知恵を借りたく質問させて頂きました。 よろしくお願いします。

    • ベストアンサー
    • MySQL
  • mysqlのloadコマンドに引数をセットするバッチの作成

    mysqlのloadコマンドに引数をセットするバッチの作成 データ移行でロードするファイルが数多くあり バッチ処理(param.bat)で実行できるようにしたいのですが、 SQLスクリプト(param.sql)にうまく引数を渡すことができません。 引数１にデータ：aaa.txt 引数２にテーブル:test_tbl どうかアドバイスいただけないでしょうか。 宜しくお願いいたします。 OS:Windows XP 【実行時】 c:temp>param.bat aaa.txt test_tbl 【作成したファイルの記述】 param.bat ------- mysql test1 -u user1 -puser1 < param.sql %1 %2 param.sql ------- LOAD DATA INFILE '&1' INTO TABLE &2 FIELDS TERMINATED BY ',' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'; exit;

  • MySQLのLOAD DATAに関して

    MySQLのLOAD DATAで、固定長行フォーマットのファイルのロードを行っています。 その際、以下のような現象の原因がわからず困っています。 データをロードするテーブル、ロードファイル、実行コマンドは以下になります。 -------------------- テーブル -------------------- フィールド1 char(2) フィールド2 char(2) -------------------- ロードファイル(テキストファイル) -------------------- 1020 3040 -------------------- 実行コマンド -------------------- LOAD DATA INFILE 'ロードファイル' INTO TABLE テーブル FIELDS TERMINATED BY '' ENCLOSED BY '' LINES TERMINATED BY '\r\n'; 結果は以下のように、フィールド2にはデータが登録されていません。 －－－－－－－－－－－－－ フィールド1　 |　フィールド2 －－－－－－－－－－－－－ 10　　　　　　|　 30　　　　　　|　 望んでいる結果は以下のような結果です。 －－－－－－－－－－－－－ フィールド1 | フィールド2 －－－－－－－－－－－－－ 10　　　　　｜20　 30　　　　　｜40　 いろいろ試してみたのですが、 どうもchar(2)の場合は2バイトの倍の4バイト、 char(4)バイトの場合は倍の8バイトというふうにデータが区切られるため、 フィールド1のデータを取得する際に4バイト取得してしまっており、 フィールド2の分のデータが取得できません。 どこに不備があるのかわからないのですが、 何か原因の検討がつく方がいたら、ご回答よろしくお願いします。

    • ベストアンサー
    • MySQL
  • SQL*LOADER　最終列のLENが１

    お世話になります。 前提として 次のようなctlファイルがあります。 OPTIONS(ROWS=500,BINDSIZE=700000) LOAD DATA INFILE '/home/oracle/TEST/00_BIN/YOSAN.csv' TRUNCATE INTO TABLE MT_YOSAN ( 日付 TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', 店舗コード TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', 予算 TERMINATED BY ',' NULLIF 昨年実績=BLANKS, 昨年実績 NULLIF 昨年実績=BLANKS ) ロード対象のCSVですが、次のようなROWデータになります。 "2011/02/22","01010",30000,25000 テーブル仕様は次のようになっています。 日付 　　　　　VARCHAR2(20)　NOT　NULL 店舗コード　　VARCHAR2(20)　NOT NULL 予算　　　　　 NUMBER(10) 昨年実績　　 NUMBER(10) これを実行した際に昨年実績のカラムに25000の先頭の2しかロードされません。 ログファイルを開くと他のLenは*に対して最後の昨年実績列は１が設定されていました。 この事象の原因と対処法がわかる方がいればご教示いただきたく存じます。 よろしくお願いいたします。 またsqlldr実行時は引数controlとlogのみ指定しています。

  • テキストファイルからのテーブル作成

    こんにちわ。今MySQLを使ってテーブルの作成をしているのですが、やり方に自信がないため質問させていただきます。 test.txtに以下の情報が入っているとします。(ゲノムの情報になります） ***** id count start_position end_position 1 2 51091076,51101385, 51091253,51102009, 2 3 112644827,112647824,112648979, 112645084,112647981,112649152, 3 3 30103583,30105062,30106006, 30103918,30105414,30106086, ***** countのコラムにはstart_position, end_positionに何個の値が入るのかを示しています。 ここから、test_tableというのをつくり、以下の形にしたいのです。 id start_position end_position 1 51091076 51091253 1 51101385 51102009 2 112644827 112645084 2 112647824 112647981 2 112648979 112649152 3 30103583 30103918 3 30105062 30105414 3 30106006 30106086 test.txtを使って. LOAD DATA INFILE 'test.txt' INTO TABLE test_table FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'; と全体のテーブルを作りました。この際、start_position, end_positionの値にはカンマが入る形になってしまいます。 上記のテーブルを作成するため、まず、idだけのテーブルを作ろうと思い、 SELECT REPEAT(CONCAT(id, '\r'), EXONCOUNT) FROM test_table INTO OUTFILE 'id_count.txt'; LOAD DATA INFILE 'id_count.txt' INTO TABLE id_count LINES TERMINATED BY '\r'; を実行し、 positionだけのテーブルも以下のように用意しました。 SELECT REPLACE(start_position, ',', '\r') FROM test_table INTO OUTFILE 'start.txt'; LOAD DATA INFILE 'start.txt' INTO TABLE start LINES TERMINATED BY '\r'; そうすると、 id_countテーブルは *** | 1 | | 1 | | 2 | | 2 | | 2 | | 3 | | 3 | | 3 | *** と表記され、 startテーブルは ********** | 51091076 | | 51101385 | | 0 | | 112644827 | | 112647824 | | 112648979 | | 0 | | 30103583 | | 30105062 | | 30106006 | | 0 | ********** と0が入ってしまいます。 start_positionとend_positionの値の最後のコンマを外してからやればできるのかもしれませんが、ほかに方法がありましたら教えていただけると嬉しいです。 わかりにくい点があるかもしれませんが、お願いいたします。

  • ORA-00917エラーが発生する

    SQL*Loaderを使用してCSVファイルをテーブルにロードしたいのですが、 「ORA-00917　カンマがありません」というエラーが発生してしまいます。 制御ファイルやデータファイルのどこに問題があるのでしょうか。 ご教授願います テーブル定義 テーブル名　TB_HOGE ID　varchar XML_DATA　XMLType INS_YMD　DATE USER VARCHAR CTLファイル OPTIONS（ 　ERRORS = 0 ) LOAD DATA CHARACTERSET UTF8 INFILE '/temp/foo/data.csv' INTO TABLE TB_HOGE APPEND XMLType(XML_DATA) FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (ID, XMLFILE FILLER CHAR, XML_DATA lobfile( XMLFILE ) terminated by EOF, INS_YMD "TO_DATA( :INS_YMD, 'YYYYMMDD')" USER) csvファイル data.csv ”No001","/temp/foo/xml/data1.xml","20150320","user1" ”No002","/temp/foo/xml/data2.xml","20150320","user1" よろしくお願いします。

  • AUTO_INCREMENTのあるテーブルにinsertできません（長文）

    mysql5.0.27(RedhatLinux 9)です。 属性のひとつがAUTO_INCREMENTになっているテーブルhogeに、データをinsertしたいのですが、「コラムの数が合致しない」というエラーで、insertできません。 Webをいろいろ漁って、AUTO_INCREMENTの属性は明示的に指定しないようだと理解しました。冗長で申し訳ないのですが、エラーが出た状態のものを、そのまま掲載させていただきます（テーブル名や属性名のみ変更しました）。 状態をまとめると、以下のようになります。 ・下記のテーブル定義で、テーブルは普通にcreateできた。 ・descで確認しても、ちゃんとテーブルはできているよう。 ・下記のinsert文で、その下のエラーメッセージが出て、データを登録できない。 ・どうみても、コラム数は合っている（と思う）。 ・下記のテーブル定義の属性数を適当に減らす（id, item1, item4, item6くらいにする）と、エラーが出ずデータがちゃんとinsertできる。 私はなにか、とんてもない勘違いをしているのでしょうか？かなりの時間試行錯誤しているのですが、一向にわかりません。。。 ===== テーブル定義 ===== CREATE TABLE hoge ( id MEDIUMINT UNSIGNED AUTO_INCREMENT NOT NULL, item1 VARCHAR (64) NOT NULL, item2 VARCHAR (64) NOT NULL, item3 VARCHAR (32) NOT NULL, item4 VARCHAR (64), item5 VARCHAR (64), item6 VARCHAR (16) NOT NULL, CONSTRAINT PK_HOGE PRIMARY KEY (id) ) type=innodb; CREATE INDEX IDX_HOGE_1 ON hoge(item1); CREATE INDEX IDX_HOGE_2 ON hoge(item2); CREATE INDEX IDX_HOGE_3 ON hoge(item3); CREATE INDEX IDX_HOGE_4 ON hoge(item4); CREATE INDEX IDX_HOGE_5 ON hoge(item6); ===== descの出力 ===== +---------------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-----------------------+------+-----+---------+----------------+ | id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment | | item1 | varchar(64) | NO | MUL | | | | item2 | varchar(64) | NO | MUL | | | | item3 | varchar(32) | NO | MUL | | | | item4 | varchar(64) | YES | MUL | NULL | | | item5 | varchar(64) | YES | | NULL | | | item6 | varchar(16) | NO | MUL | | | +---------------------+-----------------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec) ===== insert文 ===== insert into hoge (item1, item2, item3, item4, item5, item6) values ( 'data1','data2', 'data3', 'data4', 'data5', 'data6'); ===== エラーメッセージ ===== ERROR 1136 (21S01) at line 1: Column count doesn't match value count at row 1 長文大変申し訳ありません。よろしくお願いします。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する