Skip to main content

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

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

神代 巳代子
MOT (Microsoft Official Trainer)


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

トピック

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

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

>効率的な移行 効率的な移行

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

サンプル データ

exe   サンプル データのダウンロード
圧縮した Excel/.mdb/.txt ファイル、382KB

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

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

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

ページのトップへ ページのトップへ

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

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

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

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

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

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

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

 

image2-1

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

image2-2

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

ページのトップへ ページのトップへ

効率的な移行

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

Excelデータのインポート

方法 1: 手順書

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

3 で追加エラーが発生した場合、[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

    image2-4

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

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

方法 2: 手順書

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

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

ページのトップへ ページのトップへ

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

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

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

1001 商品1 "21,957"
1002 商品2 "48,599"
1003 商品3 "5,805"
1004 商品4 "18,875"
1005 商品5 "30,912"
1006 商品6 "49,753"
1007 商品7 "37,247"
1008 商品8 "15,064"
1009 商品9 "3,953"
1010 商品10 "36,624"
1011 商品11 "43,316"
1012 商品12 "16,781"
1013 商品13 "19,976"
1014 商品14 "41,107"
1015 商品15 "21,390"
1016 商品16 "29,051"
1017 商品17 "18,842"
1018 商品18 "9,187"
1019 商品19 "38,258"
1020 商品20 "47,262"
1021 商品21 "18,181"
1022 商品22 "13,293"


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

image2-5

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

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

ページのトップへ ページのトップへ