値（"001"）と数値（1）が混在する列を整数型のフィールドに保存する

質問

こんにちは。お世話になります。

VBAで、ExcelシートをAccess側でデータ型を指定してテーブルにインポートする方法
をご教授いただきたく書き込みをさせていただきました。
値（"001"）と数値（1）が混在する列を
整数型のフィールドに保存するというようなことがしたいです。

今までは、
　　1.ExcelをCSV形式で保存する。
　　2.インポート定義ですべてテキストを指定
　　3.テーブルの項目定義でデータ型を指定したところにインポート
ということをおこなっておりましたが、

1.のCSV保存の際にデータの丸めが発生してしまう問題が発生してしまいました。
（※本来の値が123456789.12であるような場合に、
　　123456789.1とシートに表示され、そのままCSV化してしまう。）

直接Excelからですと正しくデータがインポートされるようですので、
いろんなサイトを参考に、
　　・すべてテキスト型の仮テーブルを動的に作成し、
　　　Excelシートをインポート
というところまで作ってみたのですが、
これを本来のデータ型を指定したテーブルに追加しようとすると
”型が違います”エラーがでてしまって、
CSVのように無理やりデータを受け取ってはくれず
どうしたらいいのか壁にぶつかって困っています。

Accessを使われている方々はどんな方法で
あまりデータがきれいでないExcelシートの場合のインポートをされているのでしょうか？

検索しても同じことで困ってらっしゃる方の質問があまりないことからも
自分の知識不足で、回りくどい変な方法になってしまっている気がしてなりません。

お時間のある時に是非アドバイスよろしくお願いいたします。

Access2002：WindowsXP

回答No.3

hotosys · ベストアンサー

補足
ただし、当然のことながら"AAA"などの文字列を整数型にすればエラー値になる。
ちなみにそんなデータは0にするなら、
iif(iserror(CInt([book1].[フィールド1])),0,CInt([book1].[フィールド1]))
で回避できる。
かもしれない、やった事無いが。

お礼

そうですか、エラーになったら、ただＮｕｌｌになってしまうのかなぁっと思っていましたので、そのあたりもちゃんと仕様をつめる必要があるんですね。参考になる補足まで書いていただきありがとうございます。

回答No.4

mitarashi

>あまりデータがきれいでないExcelシートの場合のインポート
エクセル形式だと、「大きなお世話」をしてくれる事が多いので、質問者さんと同様に、テキストファイルで、定義ファイルを用いて処理しています。
Excelでは、Range("A1").Valueと、Range("A1").Textで、セルの内容が取得できますが、後者の場合は、表示されている状態での取得になります。ExcelのCSV形式出力は、.Textの方で行っている様ですね。
自分で、Tab区切りテキストファイルへの出力ルーチンを作成されては如何でしょうか。Tab区切りにする時は、1行分の文字列を生成しないといけないので、.Valueでいきなり結合はできないでしょうが、trim(str(.value))でも入っている通りの値が得られる様です。
Sub test()
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
With FSO.CreateTextFile(ThisWorkbook.Path & "\" & "Sample.txt")
.WriteLine ActiveSheet.Range("a1").Value
.WriteLine ActiveSheet.Range("a1").Text
.Close
End With
Set FSO = Nothing
End Sub

お礼

なるほどーー。そういうことなのですね。
以前、valueとtextの違いで思った値が取れなくて困った経験がありましたが、CSV出力時にはtextということなんですか！激しく納得です！
やはりデータのほうをキレイに出力してインポート、というのが正しい姿なのですね。
コマンドボタン押下時に、
ユーザーが指定したｘｌｓファイルを、いただいたコードでtextで保存しなおして、Accessにインポート。
そのあとに使用したtextファイルを削除。
とすればユーザーは意識することないってことですね！
先ほどhotosysさんに教えていただいた方法とどちらがいいのか悩みます～。
後々後任者が不自然に思わない方法をとりたいなぁっと思っているのですが、悩みますー。
項目数がおよそ200項目のファイルもありますので、あとは実行時間で考えたいと思います。
色々な方法をご提示いただいて、みなさまにとても感謝です。

回答No.2

hotosys

読み込んだテーブルを基にクエリを作る。
例えば読み込んだテーブル名がBook1で、フィールド1,フィールド2,フィールド3,フィールド4があるとする。
新規でクエリを作り、
フィールド1: CInt([book1].[フィールド1])
フィールド2: CDbl([book1].[フィールド2])
フィールド3: CCur([book1].[フィールド3])
フィールド4
と指定すれば各々、整数、倍精度浮動小数点型、通貨型、そのままのデータ型、のデータになる。
ただし、当然のことながら"AAA"などの文字列を整数型にすればエラー値になる。

お礼

本来のテーブルの項目定義のデータ型をfield.typeで判定させ、
そのデータ型にあったCint（）やCdbl（）を挿入しながらSQLを組み立てて実行する。。。
(お伝えしていませんでしたが、functionとして共通利用したいので、動的に都度SQLを実行しようかと思っています)
なるほど！出来そうな気がします～！
hotosysさんご回答ありがとうございました！

回答No.1

CHRONOS_0

>値（"001"）と数値（1）が混在する列を
"001"という値はテキスト型です数値型のフィールドには取り込めません

>Accessを使われている方々はどんな方法で
>あまりデータがきれいでないExcelシートの場合のインポートをされているのでしょうか？
Access(データベース)はデータの整合性を確保するために非常に制限が厳しいです
きれいでないExcelデータはそのままでは取り込めません

お礼

”００１”と１をともに１として取り込みたいと伝えたかったのですが、説明が足らず申し訳ありません。
そうですか。。。
CSVって何？Excel！っという職場環境も多いように感じておりましたので、
Excel取り込みもよくあることで、きっと何か技があるのだろう。。。と夢みていたのですが、悲しい結末です。
ご回答ありがとうございました。