ホチキス先生の「プログラマーと呼ばれたい」

InfoPath & SQL Server !

Posts Tagged ‘CSV

校務支援システムの更新 – 02 – 既存システムからエクスポートしたデータを加工してはいけない – そのままインポートできるように SQL Server のテーブル設計をしよう

leave a comment »

sql_server_management_studio_001_mid_640_480

学校の校務支援システムの運用には様々な課題があるが、とりわけ大きい課題がシステム移行時に発生する。既存システムから生徒の氏名住所などの基本情報や、履修情報、成績情報を新システムに移行しなければならないが、その作業と責任は、そのときの学校の担当教員に負わせられることとなる。既存システムの業者も、新システムの業者も、一定の技術情報は提供しても作業に関してはどちらも助けてはくれない。

学校として学籍管理のデータを扱う場合、管理の手法はシステムによって異なる。生徒の氏名や住所、生年月日などの基本データに大きな違いはないだろうが、教科科目の情報、成績管理の手法はシステムによって違いが大きいと考えられる。既存のシステムからエクスポートしたデータを新システムでそのまま使えるとは考えにくい。

そこでデータ処理をする必要がおこるが、業者がデータ移行ツールなどを用意することはない。そこでデータ移行においてデータ処理をする必要が生じるが、これをエクセルなどの表計算ソフトで行うと重大な間違いがおこる危険がある。また一度で完全に移行できることも考えにくいので、ある程度の試行錯誤が必要となる。そのたびに表計算ソフトでデータ加工をしていては手間がかかりすぎ、また事故がおこる危険が大きい。

そこで SQL Server にデータを読み込んで処理することが最も正しい解決策だ。できれば既存システムから SQL クエリによってデータを読み出したいところだが、それができなければ何らかの形でデータをエクスポートすることになるだろう。多くのシステムでエクセルや CSV の形式でデータを取り出すことができるようになっているはずだ。

sql_server_management_studio_006_mid_640

このとき、既存システムで使われるフィールドと新システムで使われるフィールドに違いがあったとき、これを表計算あるいは CSV のデータ上で加工してはいけない。もし新システムで使わないデータフィールドがあったとき、心情的にはデータを切り捨ててインポートしたい欲求は理解できる。余計なフィールド設定をせずに済ませたいだろうからだ。

またフィールド名を修正したい欲求にもかられる。しかしエクスポートしたデータを手作業で加工してはいけない。既存システムからエクスポートしたデータは、そのまま SQL Server にインポートできるようにテーブルを作っておくべきだ。

sql_server_management_studio_003_mid_640_480

データの移行は一回で終わることは、まず、ない。既存システムのデータを新システムにインポートするには、何度かの試行錯誤が必要となるはずだ。またすべてのデータを一度に移行しようとして間違いがあれば修正も困難になる。小さなデータを試験的に移行しながらチェックを繰り返し、うまく移行できることを確かめてから全部のデータを移行するだろう。

このとき、エクスポートしたデータを手作業で加工する工程を挟むと、そのたびに手作業が必要となる。手作業には誤りがつきまとい、とりわけ同じ作業を何度も繰り返していると誤る危険性が高くなる。

新システムで使わないフィールドの設定はせずに済ませたい、おかしなフィールド名は修正したい気持ちになることは理解できるが、ここで手間を惜しまず、エクスポートしたデータは無修正でそのままインポートできるように SQL Server のフィールド設計をしよう。

2019年1月2日

松本 吉生(まつもとよしお)

Microsoft MVP Data Platform
1961年京都に生まれ、神戸で幼少期を過ごす。大学で応用化学を学んだのち、理科教諭として高等学校に勤務する。教育の情報化が進む中で校内ネットワークの構築運用に従事し、兵庫県立明石高等学校で文部科学省の「光ファイバー網による学校ネットワーク活用方法研究開発事業」に携わる。兵庫県立西宮香風高等学校では多部制単位制の複雑な教育システムを管理する学籍管理データベースシステムをSQL ServerとInfoPath、AccessなどのOfficeソフトウエアによるOBA開発で構築・運用する。2015年から2017年まで兵庫県立神戸工業高等学校でC#プログラミング、IoTなどのコンピュータ教育を行い、現在は兵庫県立神戸甲北高等学校に勤務する。2004年からマイクロソフトMVP(Microsoft Most Valuable Professional)を受賞し、現在15回目の連続受賞。2016年にマイクロソフト認定教育者(Microsoft Innovative Educator Experts : MIEE)を受賞し、現在4回目の連続受賞。

校務支援システムの更新 – 01 – データ移行は現場教員の責任となる – データ移行作業は SQL Server を使おう

leave a comment »

高等学校の成績処理などにデータベースを使うようになったのは、単位制や総合学科など講座の選択肢が多い学校ができてからだ。基本的にクラス単位で授業を行う普通科高校では、出欠や成績の管理にデータベースを使う必要はなかった。クラス単位で紙ベースの出席簿や成績伝票での処理で十分だったからだ。しかし生徒が多くの講座に分かれて受講する単位制や総合学科の高校では、紙ベースの処理が不可能だ。

筆者も他部制単位制の兵庫県立西宮香風高校に勤務したとき、SQL Server を使った校務支援システムを作り運用した。多くの単位制や総合学科の学校で業者と契約して作られたシステムを運用していたが、新しいタイプの他部制単位制高校に合うシステムはなかったからだ。

近年、単位制や総合学科ではない普通科の高校でも校務支援システムを導入する動きがある。それは普通科の学校でも多様な選択肢を用意した選択講座のカリキュラムを拡大することも背景にある。契約や運用の面からクラウドベースの統一システムを都道府県単位で一括契約するケースも増えてきた。

このような校務支援システムの運用にはいろいろな課題があるが、システム更新のときに大きな課題がある。別システムへの更新時に、過去のデータを移行する作業が必要となるのだ。このデータ移行の作業は現場の教員にまかされる。既存システムの業者も、新しいシステムの業者も、どちらも何もしない。責任もとらない。

データの移行は、そのときの担当教員にとって大きな責任を負わせられるものとなる。在校生の成績データを一つ残らず完全に新システムに移行しなければならない。この作業をエクセルなどでやっていては大きな間違いがおきる危険性がある。データ移行の作業は SQL Server を使うことが最も良い。

sql_server_management_studio_001_mid_640_480

既存システムのテーブルにあわせて SQL Serever にテーブルを作る。既存システムからエクセル形式や CSV 形式でエクスポートしたデータを一括で SQL Server に読み込む。例えば高等学校の校務支援システムでは中学校情報を管理するが、中学校コードは既存システムと新システムで異なることがあるだろう。このとき、まず既存システムの中学校コードと新システムで利用するコードを一括で SQL Server に読み込んでおく。

sql_server_management_studio_002_mid_640_480

sql_server_management_studio_003_mid_640_480

sql_server_management_studio_004_mid_640_480

そして既存システムのコードと新システムのコードを関連付けたビューを作り、これを他の情報と関連させる。

sql_server_management_studio_005_mid_640_480

SQL Server で一元的にデータ処理を行うことで、間違いのないコード変換処理をすることができる。SQL Server で正しい変換テーブルとビューを作っておけば、既存システムからのデータ移行作業を手間なく、そして誤りなく何度もテストし確かめることができる。

2019年1月1日

松本 吉生(まつもとよしお)

Microsoft MVP Data Platform
1961年京都に生まれ、神戸で幼少期を過ごす。大学で応用化学を学んだのち、理科教諭として高等学校に勤務する。教育の情報化が進む中で校内ネットワークの構築運用に従事し、兵庫県立明石高等学校で文部科学省の「光ファイバー網による学校ネットワーク活用方法研究開発事業」に携わる。兵庫県立西宮香風高等学校では多部制単位制の複雑な教育システムを管理する学籍管理データベースシステムをSQL ServerとInfoPath、AccessなどのOfficeソフトウエアによるOBA開発で構築・運用する。2015年から2017年まで兵庫県立神戸工業高等学校でC#プログラミング、IoTなどのコンピュータ教育を行い、現在は兵庫県立神戸甲北高等学校に勤務する。2004年からマイクロソフトMVP(Microsoft Most Valuable Professional)を受賞し、現在15回目の連続受賞。2016年にマイクロソフト認定教育者(Microsoft Innovative Educator Experts : MIEE)を受賞し、現在4回目の連続受賞。

郵便番号データを SQL Server で利用するには(1) – SQL Server 2012 Management Studio でデータ変換サービス DTS を使って CSV データを SQL Server に取り込む

leave a comment »

データ変換サービス DTS はSQL Server 2000 の頃からよく使っていた。Excel や Access 、CSV 形式のテキストファイルなどを SQL Server にウィザードで取り込むことができる便利なツールだ。だが DTS は無償版の SQL Server 2012 Management Studio のメニューからは利用できない。

SQLSV_MS_2012_DTS_003_mid_640

<Fig.1 : SQL Server 2012 Management Studio の「ツール」メニューからはデータ変換サービス DTS が使えない>

データ変換サービス DTS を利用するには、Windowsのスタートメニューで Microsoft SQL Server 2012 から「データのインポートおよびエクスポート」を実行するか、以下のフォルダにある DTSWizard.exe を直接実行する。フォルダ名はインストールしている SQL Server や Management Studio のバージョンによって異なる場合もある。

C:\Program Files\Microsoft SQL Server\110\DTS\Binn

SQLSV_MS_2012_DTS_004_mid_640

<Fig.2 : Windowsのスタートメニューから「データのインポートおよびエクスポート」を実行する>

SQLSV_MS_2012_DTS_005

<Fig.3 : C ドライブの Program Files フォルダから直接 DTS ウィザード DTSWizard.exe を起動する>

データ変換サービス DTS を起動する前に、日本郵便の Web ページから最新の郵便番号データをダウンロードしておく。データは CSV 形式で、ZIP 形式の圧縮になっている。ファイルをダウンロードし、適当なフォルダに保存して展開しておく。展開すると次のように KEN_ALL.CSV というファイルができる。これは全国の郵便番号を一括でダウンロードしたファイルだ。

ちなみにこのファイルは、Excel をインストールしているコンピュータなら下のように Excel との関連付けがされており、ダブルクリックすると Excel で開くことができる。しかし Excel のバージョンや保存形式によって最大行数の制約を受けることがあるので、くれぐれも上書き保存しないように。うっかり上書き保存するとデータが切り捨てられてしまう場合がある。

SQLSV_MS_2012_DTS_006_mid_640

<Fig.4 : 日本郵便の Web サイトから郵便番号データをダウンロードする>

SQLSV_MS_2012_DTS_007

<Fig.5 : 日本郵便からダウンロードした ZIP 圧縮形式の郵便番号データを解凍した>

郵便番号データの準備ができたら、データ変換サービス DTS を起動する。次の起動画面が表示されるだろう。

SQLSV_MS_2012_DTS_008

<Fig.6 : データ変換サービス DTS を起動する>

「次へ」をクリックしてウィザードをすすめると「データソースの選択」画面になる。ここでは CSV 形式のテキストファイルを読み込みたいので、「データソース」を「フラットファイルソース」にし、先に保存した郵便番号の CSV ファイルを「ファイル名」のところで「参照」ボタンをクリックして選択する。

「ロケール」と「コードページ」には文字コード体系がデフォルトで設定されているはずだ。「形式」では「テキスト修飾子」がデフォルトで<なし>になっているが、この郵便番号ファイルはテキストデータをダブルクォーテーション「”」で囲んでいるので、「テキスト修飾子」に「”」を入力する。またデータにフィールド名はないので「先頭データ行を列名として使用する」のチェックを外す。

SQLSV_MS_2012_DTS_009

<Fig.7 : データ変換サービス DTS でデータソースの選択をする>

左側の「データソース」で「全般」から「列」のビューに切り替えると、データをプレビューで確認できる。

SQLSV_MS_2012_DTS_010

<Fig.8 : データ変換サービス DTS のデータソースの選択で「列」のビューに切り替える>

次に左側の「データソース」で「全般」から「詳細設定」のビューに切り替える。ここで重要なのは、OutputColumnWidth の設定で、この値を読み込むデータの最大サイズ以上に指定しておかなければならない。

そこであらかじめ Excel で郵便番号データの各フィールドの要素と最大データ長を調べておく。フィールドの要素は次のようになっている。

列0 : 何かの管理番号(最大5桁の数字)
列1 : 郵便番号の前から3~5桁を切り取った数値
列2 : 郵便番号(7桁の数字)
列3 : 都道府県の半角カナ(最大文字長7)
列4 : 市区町村の半角カナ(最大文字長22)
列5 : 市区町村以降の半角カナ(最大文字長73)
列6 : 都道府県(漢字、最大文字長4)
列7 : 市区町村(漢字、最大文字長10)
列8 : 市区町村以降(漢字、最大文字長37)
列9~14 : 住所データを管理する何らかの記号(1桁の数値)

デフォルトで OutputColumnWidth の値は50に設定されている。ここで問題になるのは、列5の半角カナ73文字と列8の漢字37文字だ。漢字は2バイト文字なので、文字数の2倍、74以上のサイズを必要とする。そこでこの2つのフィールドは、それぞれサイズを増やしておかなければならない。

SQLSV_MS_2012_DTS_011

<Fig.9 : データ変換サービス DTS のデータソースの選択で「詳細設定」のビューに切り替える>

あらかじめデータの最大サイズがわかっていれば手作業でOutputColumnWidth の値を変更するが、わからないときは「型の推測」ボタンをクリックし、実際のデータをスキャンして自動的に調べることもできる。

SQLSV_MS_2012_DTS_012

<Fig.10 : データ変換サービス DTS で列の型の推測をする>

SQLSV_MS_2012_DTS_013

<Fig.11 : データ変換サービス DTS で型の推測をした>

ここまでで入力データの設定ができた。次は変換先の設定をする。変換先はデフォルトの SQL Server Native Client とし、サーバー名をドロップダウンで選択する。そしてデータベースをデータをインポートしたいデータベースに変更する。

SQLSV_MS_2012_DTS_014

<Fig.9 : データ変換サービス DTS で変換先を選択する>

次の「コピー元のテーブルおよびビューを選択」では、元データとインポートするテーブルとのデータマッピングを編集することができる。「マッピングの編集」ボタンをクリックする。

SQLSV_MS_2012_DTS_015

<Fig.10 : データ変換サービス DTS でコピー元のテーブルおよびビューを選択する>

「列マッピング」のビューでは、変換元と変換先のフィールド名、フィールドの型、サイズが自動的に設定されている。設定値は「型の推測」で元データから得られたものだ。

SQLSV_MS_2012_DTS_016

<Fig.11 : データ変換サービス DTS で列マッピングを編集する>

「型の推測」で列0から列2までは数字と判断されている。SQL Server でどのような処理をしたいかによるが、数字であってもコードとして処理したい場合は、文字列にしたほうが扱いやすい場合がある。また住所のデータは varchar ではなく nvarchar にしたい。そこで型やサイズを手作業で変更する。

SQLSV_MS_2012_DTS_017

<Fig.12 : データ変換サービス DTS で変換先の型とサイズを編集する>

またデフォルトで「変換先テーブルを作成する」のラジオボタンが選択されており、SQL Server に新しくテーブルを作成することができるが、「SQL の編集」ボタンをクリックすると「テーブル作成 SQL ステートメント」のウィンドウが表示され、SQL Server に生成するテーブルのテーブル名やフィールド名を編集することができる。テーブル名を「郵便番号住所」とし、各フィールド名を設定する。

SQLSV_MS_2012_DTS_018

<Fig.13 : テーブル作成 SQL ステートメントを編集する>

テーブル作成 SQL ステートメントの編集を終了すると「列マッピング」のビューに戻る。このビューに戻っても「変換先」のフィールド名は変わらず「列 0」や「列 1」といったデフォルトのままであるので、手作業で「変換先」のフィールド名を書き換える。ドロップダウンリストには表示されないので、手作業で書き換える必要がある。

SQLSV_MS_2012_DTS_019

<Fig.14 : データ変換サービス DTS の列マッピングで変換先のフィールド名を編集する>

列マッピングの編集が終わると「コピー元のテーブルおよびビューを選択」画面に戻る。このときも戻ったときは「変換先」のテーブル名がデフォルトで与えられたままなので、「テーブル作成 SQL ステートメント」で変更したテーブル名に手作業で書き換える必要がある。

SQLSV_MS_2012_DTS_020

<Fig.15 : データ変換サービス DTS のコピー元のテーブルおよびビューを選択で変換先のテーブル名を書き換える>

次に「データ型マッピングの確認」になる。変換先のテーブル名やフィールド名、データの型やサイズを変更したので、データの切り捨てなどがおこる可能性があるとの注意がされるが、間違いがなければこのまま次にすすめてよい。

SQLSV_MS_2012_DTS_021

<Fig.16 : データ変換サービス DTS でデータ型マッピングの確認をする>

ここまでで設定は終了し、最後の「パッケージの実行」では、いますぐ実行したい場合は「すぐに実行する」のチェックボックスを入れたままにし、次へすすむ。

SQLSV_MS_2012_DTS_022

<Fig.17 : データ変換サービス DTS でパッケージの実行をする>

SQLSV_MS_2012_DTS_023

<Fig.18 : データ変換サービス DTS でウィザードを完了する>

ウィザードを完了すると、データ変換サービス DTS が変換元のデータを読みとり、SQL Server に送信する。変換のプロセスがわかり、レポートが表示される。

SQLSV_MS_2012_DTS_024

<Fig.19 : データ変換サービス DTS で変換プロセスとレポートを表示する>

SQL Server Management Studio でテーブルとデータを確認する。すべてのデータが変換されたかどうかは、ビューを作って確かめる。

——————————————————————-

SELECT COUNT(*) AS Expr1 FROM dbo.郵便番号住所

——————————————————————-

SQLSV_MS_2012_DTS_025_mid_640

<Fig.20 : SQL Server Management Studio でテーブルとデータを確認する>

SQLSV_MS_2012_DTS_026_mid_640

<Fig.21 : SQL Server Management Studio でデータの件数を確認する>

データ変換サービス DTS では、変換元のフィールドサイズの最大サイズがわかっていれば手作業で OutputColumnWidth の値を変更し、わからなければ「列の型の推測」でデータサイズを調べ、自動的に適用させればよい。その場合は、変換先のフィールドが設定したい型やサイズにならない場合があるので、そのときは手作業で編集する。

データ変換サービス DTS でエラーがおこったときは、まずフィールドサイズが OutputColumnWidth の設定値を超えていないかを確認しよう。