Step Up OFFICE ‐ 効率的なデータ管理の手法 ‐

第 2 回 「インポート方法の選択とエラー対処」 ‐ Access へ既存データをインポートする ‐

神代 巳代子
MOT (Microsoft Official Trainer)

前回、既存の Excel で作成されたデータをクリーニングすることについて解説いたしました。ここで、「Excel のままデータを管理することが困難である」と、判断されたら早めにデータベース システムへ移行をしましょう。今回はデータを有効に利用するために、Access への移行手順についてご紹介します。

Access へ既存データをインポートする

作成するシステムは、「見積請求管理1.mdb」とします。Accessでは、既存のExcelファイル、テキストファイルなどからデータをインポートして使用することができます。また、Excelでもデータを利用する可能性がある場合には、Excelファイルとのリンクを取っておくこともできます。

ご承知のように、Accessでは「フィールド名」「データ型」「データプロパティ」を細かく設定する必要があり、インポートする際にデータ型が統一されていないと思わぬ結果が生じます。仮に、前回のデータシート[first]と同様のデータをこの後紹介する「手順2」でインポートすると、数値・文字が混在していたレコードは正しくインポートされないことになります。（前回の[first]ではデータ件数の関係で、テキストデータとしてインポートされます。）インポートエラーが発生した場合、Accessのテーブルタブに[インポートエラー]テーブルが作成されます。どのようなエラーが発生しているか・・・。を確認しておきましょう。今回は、Accessに移行を行って効率的なデータ管理を行うことを目標に進めていきたいと思います。

インポート方法を検証する

データをインポートする際の手順として、方法が二つ考えられます。

方法 1: フィールド名、データ型を指定してテーブルを作成し、インポートウィザードの最後で既存のテーブルにデータをインポートする。

• 元になるデータと完全にフィールド名を一致させておく必要があります。また、データ型があっていない場合はインポートの際にデータが抜け落ちてしまいます。たとえば、数値型のフィールドを作成し、バイト型と指定した場合、0～255までの数値しか扱うことができないため、256などの数値が入力されているときは、インポートできなくなります。

方法 2: テーブルを作成せずに、インポートし、インポートウィザードの最後で新規テーブルを作成する。

• データ型などはAccessが自動的に判断し、設定するため元データに数値・文字が混在している場合、インポートエラーが発生します。また、フィールドサイズが判断されたデータ型の最大値（テキスト型では255、数値型では倍精度浮動小数点）に設定されるため、ハードディスク容量やフリーメモリの領域を圧迫します。メモリ、フィールド数によりますが、経験値からすると2万件を越すと苦しいようです。

データ件数25,000件 店舗コードフィールドは、ほとんどが数値であったが、文字が入力されていたセルが196件存在した。

テーブル [インポートエラー] サンプル画面

テーブル [sheet] デザイン ビュー サンプル画面

効率的な移行

さて、どちらの方法をとるにせよ、あらかじめデータをきちんと整理しておくことが大切です。データ件数がそれほど多くなく、一度インポートすればよいというのであれば方法２を使用する方が簡便です。

Excelデータのインポート

方法 1: 手順書

1. Excelデータとテーブルリンクを取っておく。[リンク顧客]
2. デザインビューでデータ型、フィールド名を指定しテーブル作成をしておく。[T顧客]
   その際、データ型は[リンク顧客]のデータの最大値となるようにしておく。（関数を使用して、数値の最大、文字列の最長を検索しておくとよいでしょう。Sheet[最大値検索]参照）
   主キーは設定しない。
3. 追加クエリーを作成し、[T顧客]に、[リンク顧客]のすべてのデータを追加する。
4. 作成した[T顧客]に主キーを設定する。

３ で追加エラーが発生した場合、[T顧客]のフィールド名、データ型が間違っていたことになります。Excelで数値・文字が混在するフィールドがあった場合は、あらかじめExcelでテキストに変換しておくか、テーブル作成時にデータ型を「テキスト」としておきましょう。また、フィールドプロパティ：値要求、空文字列の許可プロパティを確認しましょう。

サンプルデータ[T顧客1]に追加クエリーでデータを追加した場合は、「郵便番号」フィールドプロパティで空文字列の許可：いいえ　と設定されているため、郵便番号の入力されていない2件のデータが型変換エラーによって抜け落ちてしまいます。この場合、テーブル「インポートエラー」は作成されないため、どこでエラーが発生しているのか判断するのが難しくなります。（[T顧客1インポート後]では、プロパティを変更しているため、データが正しく追加されます。）

インポート後にデータ件数を確認し、一致していなければ「不一致クエリー」で抜け押したデータを検索し、エラーの原因を特定します。

確認手順

1. 「QT顧客1データ削除」クエリーの実行

2. 「QT顧客1へのデータ追加」クエリーの実行 データ件数：596

3. 「Qリンク顧客とT顧客1との差分」クエリーの確認 データ件数：2

   • このクエリーを作成することで、インポートできなかったデータを特定することができます。データが特定できたら、なぜインポートできなかったのか・・・。を考えましょう。

4. 「QT顧客1インポート後データ削除」クエリーの実行

5. 「QT顧客1インポート後へのデータ追加」クエリーの実行 データ件数：598

   • 「郵便番号」フィールドプロパティ　空文字列の許可：はい　により正しくデータがインポートされます。

6. 「T顧客1インポート後」テーブルのデザインビューを表示し、主キーを設定
   現在主キーが設定できないことを確認。

   • 「得意先コード」＋「店舗コード」で主キーを設定することができそうでしたが、重複するデータが存在しているようです。

7. 「Q主キー設定比較1」クエリーを実行し、主キー項目の重複検索テーブルを作成。データ件数：6

   • このクエリーを作成することで重複データの検索ができます。

    

8. 「Q主キー設定比較2」クエリーを開き、重複データの確認。 データ件数：12

   

9. データを整理し、主キーを設定する。

   • まったく同じデータは入力ミスと判断し、削除します。それ以外は店舗コードを条件に「T顧客インポート後」から該当データを表示するクエリーを作成し、店舗コードを修正するなどして整理します。 サンプル[T顧客]

10. Excelファイルとのリンクを解除し、不要なテーブル・クエリーを削除する。

方法 2: 手順書

1. 前回のコラムを参考に、元データの変更を行う。
2. [リンク顧客]を元にデータをインポートし、「T顧客2」を作成する。
3. 「T顧客2」のデザインを表示し、フィールドプロパティを変更する。
4. 主キーを設定する。

３ の操作には、やはりテキストの最大数、数値の最大値などを正確に理解しておく必要があります。また、主キーの設定には方法1：確認手順と同様の操作が必要になります。

テキストデータのインポート

テキストデータをインポートする場合も、基本的にはExcel同様になります。テキストデータには固定長（1フィールドの長さが一定のもの）、タブ、カンマなどの区切り文字がフィールドごとに入力されているもの・・・など様々なパターンがあります。既存のシステム（オフコン）からデータを取り出す際に、「タブ区切りを入れる」「カンマ区切りを入れる」と指定することできると思われますので、レコード中に絶対使用されていない文字をフィールドの区切りとして指定しておくと良いでしょう。数値がカンマ付きで入力されているにも関わらず、「カンマ区切り」を指定すると、当然ながら余分なフィールドが作成され、正しくデータをインポートできません。テキストデータを一度Excelで開くことも可能ですが、うっかりすると開くときにデータ型を指定し忘れて、テキストデータを数値として読み込んでしまうことがあります。また、読み込んだデータをcsvファイルとして保存するとせっかくテキストに変換し「0001」としておいたデータが、数値「1」となってしまいます。

サンプルデータ「商品.txt」

テキストデータのインポートはウィザードの指示に従って行います。インポート中に「設定」ボタンを押して、インポート定義を設定、保存することができます。テキストファイルを元に日々書き換わるデータがある。といった場合には、インポート定義を設定しておくとよいでしょう。

テキストデータインポート画面サンプル

第二回目の解説はこちらまでです。次回は、整合されたデータに対してよりわかりやすいアプリケーションとしての作成方法について解説いたします。