Access (一般機能)

Accessの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Access 2021)
短いテキストのデータ型フィールドに文字列として保存された数字がインポートできない
投稿日時: 22/07/05 15:10:37
投稿者: 2365

商品データをAccessでいろいろ加工したいのですが、大元となる商品リストをインポートする際に、インポート先のテーブルの全てのフィールドを短いテキストにしているのに、データ型の変換エラーが出ました。
中身を確認すると、商品リストの価格が数値になっているもの、文字列になっているものが混在しており、文字列のものがエラーになっているようでした。(とはいえ、文字列のもの全てがエラーになっている訳ではないようです)
大元の商品リストは複数の人間が介在して作成しているものなので、エラーがでないように全てのフィールドを短いテキストのデータ型にしたのですが、やり方がマズイでしょうか?
大元のリストをきれいにしてからインポートする方が良いのは分かっているのですが、縦にも横にも大きなデータであり、週次で更新されるため、工数少なくしたいです。
インポートエラーが出ないようにするにはどうしたらよいでしょうか?

回答
投稿日時: 22/07/05 15:54:34
投稿者: sk

引用:
商品データをAccessでいろいろ加工したいのですが、
大元となる商品リストをインポートする際に、

・その「商品リスト」のデータ形式/ファイル形式は何なのか。
 
・実際にどのような方法によってインポートを実行しているのか。
 (マクロアクション、VBA のコード、保存済みのインポート操作など)
 
引用:
インポート先のテーブルの全てのフィールドを
短いテキストにしているのに、データ型の変換エラーが出ました。

仮にテキストファイル、または Excel ワークシートのいずれかを
既存のテーブルにインポートしようとしてるのだとして、
インポート元のそれぞれの列をどのデータ型として扱うか
(解釈するか)は、インポート先のフィールドのデータ型によって
判断されているわけではありません。
 
テキスト ファイルのデータをインポートまたはリンクする:
https://support.microsoft.com/ja-jp/office/%E3%83%86%E3%82%AD%E3%82%B9%E3%83%88-%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E3%82%A4%E3%83%B3%E3%83%9D%E3%83%BC%E3%83%88%E3%81%BE%E3%81%9F%E3%81%AF%E3%83%AA%E3%83%B3%E3%82%AF%E3%81%99%E3%82%8B-d6973101-9547-4315-a8f8-02911b549306
 
引用:
Access は、ファイルの最初の 25 行をスキャンして、テーブルのフィールドのデータ型を決定します。

引用:
ソース ファイルの 25 行目より後に異なるデータ型の値が混在する場合、インポート操作でスキップされたり不適切に変換されたりする可能性があります。

 
Excel ブックのデータのインポートとリンクの設定:
https://support.microsoft.com/ja-jp/office/excel-%E3%83%96%E3%83%83%E3%82%AF%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E3%81%AE%E3%82%A4%E3%83%B3%E3%83%9D%E3%83%BC%E3%83%88%E3%81%A8%E3%83%AA%E3%83%B3%E3%82%AF%E3%81%AE%E8%A8%AD%E5%AE%9A-a1952878-7c58-47b1-893d-e084913cc958
 
引用:
Access は、ソースの最初の 8 行をスキャンして、テーブルのフィールドのデータ型を決定します。ソースの最初の 8 行のどの列にも、異なるデータ型が混在しないようにすることをお勧めします。混在すると、適切なデータ型が列に割り当てられなくなる可能性があります。

 

回答
投稿日時: 22/07/05 15:55:30
投稿者: taitani

元データを一度別のExcel file の PowerQuery で取り込み、データをきれいにしてから、Access に取り込んではいかがでしょうか。
 
http://officetanaka.net/excel/function/GetAndTransform/

投稿日時: 22/07/05 16:25:28
投稿者: 2365

sk様、回答ありがとうございます。
 
・その「商品リスト」のデータ形式/ファイル形式は何なのか。
→ファイル形式はエクセルになります。
 
・実際にどのような方法によってインポートを実行しているのか。
 (マクロアクション、VBA のコード、保存済みのインポート操作など)
→手動のインポートです。
 
 
インポート元のそれぞれの列をどのデータ型として扱うか
(解釈するか)は、インポート先のフィールドのデータ型によって
判断されているわけではありません。
→このことは初めて知りました。しかも最初の25行(も!)をスキャンして判断するだなんて。。。
 しかも、25行目より後に25行目までのデータ型と異なるものが混在する場合はスキップされる可能性
があるだなんて。。。。
 ということは、一つのフィールド(列)内のデータは予めデータ型をそろえておかないといけないということですね。
 これまでずっとインポート先のフィールドのデータ型に合わせてインポートされているのだと思っていました。それができれば簡単だったのですが。。。
 
どうもありがとうございました!

投稿日時: 22/07/05 16:38:21
投稿者: 2365

taitani様、ご回答ありがとうございます。
Excel file の PowerQuery というものを初めて知りました。
教えていただいたリンク先を確認して勉強してみます。
ありがとうございました。

回答
投稿日時: 22/07/05 18:12:58
投稿者: sk

引用:
ファイル形式はエクセルになります。

ならばスキャンされているのは「ソースの最初の 8 行」のはず。
 
引用:
これまでずっとインポート先のフィールドのデータ型に合わせて
インポートされているのだと思っていました。
それができれば簡単だったのですが。。。

一定のフォーマットで保存されているテキストファイルを
既存のテーブルにインポートするケースについては、
テキストインポートウィザードを実行する過程で
あらかじめインポート定義を作成しておき、
次回以降のインポート時にその定義を呼び出す、
という方式を採るのが一般的です。
 
できるネット より:
https://dekiru.net/article/15656/
https://dekiru.net/article/15657/
 
Excel ワークシートを既存のテーブルにインポートするケースに関しては、
上記のインポート定義と同等の機能がありません。
 
また、スプレッドシートインポートウィザードにおいて
フィールドごとにデータ型の指定が出来るのは、
そのワークシートを新規テーブルとしてインポートする時だけです。
(その際のインポート操作を保存しておいて後から呼び出すことは
出来ますが、テーブル定義をまるごと置き換える形になるため、
格納済みのレコードは全て削除されます)
 
引用:
ということは、一つのフィールド(列)内のデータは
予めデータ型をそろえておかないといけないということですね。

あらかじめそのように対処しておくのが最善ではあります。
 
特に「ユーザーが直接手を加えたワークシート」の場合、
どこにどんなノイズが混入しているかは、実際のワークシートを
検証しない限り、誰にも分かりません。
 
また、インポート先のフィールドのデータ型がテキスト型ならともかく、
「数値データに変換できない文字列」をそのまま数値型のフィールドに
格納することはできませんし、日付/時刻型のフィールドについても同様です。
 
そういったケースまで回避したければ、Excel ワークシート側の
各セルの表示形式やデータの入力規則などを適切に設定しておき
(そういうテンプレートを用意して使用してもらうようにし)、
エラーを招くデータがユーザーによって入力される余地を
可能な限り減らすようにしておくことが推奨されます。
(それでもエラーが発生する可能性をゼロにすることは出来ませんが)

投稿日時: 22/07/07 10:02:59
投稿者: 2365

sk様、引き続きの回答ありがとうございます。
エクセルの場合も最初の8行で判断されているということでしたら、今回のインポートエラーもさもありなんという感じです。
別件でテキストデータを毎日インポートしているのですが、インポート定義をしてある為その情報が呼び出されて、そのデータ形式で保存されているんですね。エクセルも同様と思っていたことが、今回のご説明でそうではないことがよくわかりました。
新規テーブルでインポートする場合はデータ型が指定できるというコメントを拝見して、そういえば以前先輩がどうしても思うとおりにインポートできない場合は、新規テーブルでインポートしてオブジェクトの名前を置き換えちゃえばいいよ、と仰っていたのを思い出しました。
 
sk様おっしゃる通り、私が今回やろうと思っているデータは本来は数値型のフィールドや日付型のフィールドもあるのですが、エラーが頻発する為とりあえず全てのフィールドを短いテキストでインポートし、そのあとにフィールド毎にデータ型を変更した、あるべき姿の空のテーブルに追加クエリでデータを追加する、という手法をとっていましたが(私は初心者に毛が生えた程度の為、前任の受け売りです)、そもそものインポートで躓いて困っておりました。
 
なかなか道は険しいですが、勉強します。
色々お教えいただきどうもありがとうございました。

トピックに返信