残念ながら、最初のテーブルはテーブル構成とCSVデータ構成が異なっていたために、CSVデータのインポートはできたが、テーブルの内容は全くおかしくなっていた。
前回の失敗を踏まえて、再度テーブルを作り直してCSVデータをインポートし成功することができたので紹介する。
インポートするデータ構造をしっかりと見える化して、その通りにテーブルを作成すること。
テーブルの再製作
最初に作ったテーブルのSQL文を改めて見てみよう。
最初のテーブルを作成するSQL文は以下の通りだった。
create table result (id int auto_increment not null, kaisi int not null, hizuke date not null, 3digi int(1) not null, 2digi int(1) not null, 1digi int(1) not null, PRIMARY KEY (id));
このSQL文自体は問題は無かったのだが、テーブル構成要素が実際のCSVデータ構成と合っていなかったことが問題だ。
特に大きな所は、CSVデータ上にはID等ないが、DBのテーブルにはIDがありしかも自動で加算していく構造だった。
これを作った時に、CSVデータのインポートと言うよりも、データを入力すると言う事が頭の中に有った事がこうなった原因なのだと思う。
従って、CSVデータに合わせたテーブルを作成するには、以下のSQL文になると思われる。
create table result (kaisai int not null, hizuke date not null, 3digi int(1) not null, 2digi int(1) not null, 1digi int(1) not null, PRIMARY KEY (kaisai));
このSQL文を実行してみると
MariaDB [numbers3]> create table result (kaisai int not null, hizuke date not null, 3digi int(1) not null, 2digi int(1) not null, 1digi int(1) not null, PRIMARY KEY (kaisai));
Query OK, 0 rows affected (0.026 sec)
となり、無事テーブルができました。当たり前ですよね、前回作ったテーブルから一部変更しただけですから、問題無くできて当たり前ですよね。
実際に作られたテーブルの構造を確認してみる。
MariaDB [numbers3]> show columns from result;
+——–+———+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——–+———+——+—–+———+——-+
| kaisai | int(11) | NO | PRI | NULL | |
| hizuke | date | NO | | NULL | |
| 3digi | int(1) | NO | | NULL | |
| 2digi | int(1) | NO | | NULL | |
| 1digi | int(1) | NO | | NULL | |
+——–+———+——+—–+———+——-+
5 rows in set (0.007 sec)
今度は問題無くできていると思うので、もう一度CSVデータをインポートしてみよう。
再度CSVデータをテーブルにインポートしてみる
それでは再度CSVデータをテーブルにインポートしてみよう。
MariaDB [numbers3]> load data local infile “/home/jkawasaki/numbers3jnb.csv” into table result fields terminated by ‘,’ OPTIONALLY enclosed
by ‘”‘;
Query OK, 521 rows affected, 522 warnings (0.065 sec)
Records: 521 Deleted: 0 Skipped: 0 Warnings: 522
またしてもインポート自体のデータは全てインポートできたのだが、ワーニングがやはり出ている。どこかに問題があるのでしょうね。
ワーニングの内容をチェックするために、次のSQL文を実行してみた。
show warnings;
実際に実行してみると、以下のような結果が表示された。
MariaDB [numbers3]> show warnings;
+———+——+————————————————————————————-+
| Level | Code | Message |
+———+——+————————————————————————————-+
| Warning | 1366 | Incorrect integer value: ‘6184’ for column `numbers3`.`result`.`kaisai` at row 1 |
| Note | 1265 | Data truncated for column ‘1digi’ at row 1 |
| Note | 1265 | Data truncated for column ‘1digi’ at row 2 |
| Note | 1265 | Data truncated for column ‘1digi’ at row 3 |
| Note | 1265 | Data truncated for column ‘1digi’ at row 4 |
| Note | 1265 | Data truncated for column ‘1digi’ at row 5 |
~ ~
| Note | 1265 | Data truncated for column ‘1digi’ at row 57 |
| Note | 1265 | Data truncated for column ‘1digi’ at row 58 |
| Note | 1265 | Data truncated for column ‘1digi’ at row 59 |
| Note | 1265 | Data truncated for column ‘1digi’ at row 60 |
| Note | 1265 | Data truncated for column ‘1digi’ at row 61 |
| Note | 1265 | Data truncated for column ‘1digi’ at row 62 |
| Note | 1265 | Data truncated for column ‘1digi’ at row 63 |
+———+——+————————————————————————————-+
64 rows in set (0.001 sec)
どうやら2つの正しく無い情報がインポートされたみたいですね。
原因はいろいろあるようだが、各COLのデータ形式が違っているのが結局の所の原因のように思える。
よくよく考えてみると、HPからダウンロードしたCSVファイルの最初の開催日などは、実際には第xxxx回となっていたのを、自分で第と回を削除して数字だけにした。
その他の日付以外も3桁の数字もExcelの区切り位置関数を使って分離した。
どうも、この最初の開催回数と当選番号は数字だけどデータ上は文字列扱いのようだ。もちろん、最初から手で同じ数字を打ち込み上書きすれば変わるのだろうが。
従って、列のプロパティで数字に変換してから保存したのですが、それでもインポード時にワーニングが消えません。
最新の1行もエラーで取り込めないのです。
どうしてもワーニングを消すことができないので、COLのデータ構成を変えて挑戦してみました。
今まで
create table result (kaisai int not null, hizuke date not null, 3digi int default 0, 2digi int default 0, 1digi int default 0, PRIMARY KEY (kaisai));
日付以外は全てINT(数値)としていた。
今回は
create table result (kaisai varchar(6) not null, hizuke date not null, 3digi char(1) not null, 2digi char(1) not null, 1digi char(1) not null, PRIMARY KEY (kaisai));
日付以外を文字列として定義しました。
MariaDB [numbers3]> create table result (kaisai varchar(6) not null, hizuke date not null, 3digi char(1) not null, 2digi char(1) not null, 1digi char(1) not null, PRIMARY KEY (kaisai));
Query OK, 0 rows affected (0.029 sec)
文字列に変更しても問題無くテーブルは作成できました。後はCSVファイルをインポートしたときにワーニングが無くなっているかです。
インポートしてみます。
MariaDB [numbers3]> load data local infile “/home/jkawasaki/numbers3jnb.csv” into table result fields terminated by ‘,’;
Query OK, 521 rows affected (0.050 sec)
Records: 521 Deleted: 0 Skipped: 0 Warnings: 0
やりました、ワーニングは0です。問題無くインポートできた事になります。
テーブルにインポートされたデータを読み出すと以下の通り。
MariaDB [numbers3]> select * from result;
+———-+————+——-+——-+——-+
| kaisai | hizuke | 3digi | 2digi | 1digi |
+———-+————+——-+——-+——-+
| 5664 | 2021-04-05 | 4 | 1 | 5 |
| 5665 | 2021-04-06 | 0 | 5 | 3 |
| 5666 | 2021-04-07 | 7 | 4 | 2 |
| 5667 | 2021-04-08 | 6 | 1 | 1 |
| 5668 | 2021-04-09 | 6 | 8 | 4 |
| 5669 | 2021-04-12 | 1 | 7 | 8 |
~ ~
| 6178 | 2023-03-30 | 8 | 3 | 9 |
| 6179 | 2023-03-31 | 6 | 9 | 4 |
| 6180 | 2023-04-03 | 0 | 0 | 9 |
| 6181 | 2023-04-04 | 9 | 1 | 1 |
| 6182 | 2023-04-05 | 6 | 4 | 4 |
| 6183 | 2023-04-06 | 2 | 5 | 8 |
| 6184 | 2023-04-07 | 2 | 7 | 8 |
+———-+————+——-+——-+——-+
521 rows in set (0.006 sec)
問題無く全部データがインポートされております。
これでやっとデータのインポートが完了しました。
直ぐに終わると思っていたCSVデータのインポートですが、ワーニングに行く手を阻まれて結構苦労しました。
ナゼかは分かりませんが、構造を数字から文字列に変える事でインポートしたときにワーニングは全て解除されたんです。
ナゼなんだろう?
テーブルを再製作してCSVデータインポート まとめ
テーブルのカラム構造とCSVデータの構造が違うためにCSVデータインポートが上手くいかなかった事がスタートだった。
しかし、実際にいろいろと構造を変えながら実施していても、データは取り込めるのですがワーニングが消えませんでした。
データが取り込めているのだから、特に問題無く使えるだろう。
そう思ってそのまま使おうとしたのですが、ワーニングが出たまま使用していると、後々よ着せぬところで問題が発生するのではないかと考えました。
従って、素人が慣れるDBのワーニングと格闘しながらなんとか全てのワーニングを抑え込むことができました。
正しい理由はよく分からないのですが、とりあえずは問題無くなりました。
これでいろいろとやっていこうとするベースができた形になります。
単なるデータのインポートでここまで手こずるとは思ってもみませんでした、
しかし、いろんな意味で良い勉強になりましたね。
いよいよ次はWebとの連携かな。