• ベストアンサー

値("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

noname#171981
noname#171981

質問者が選んだベストアンサー

  • ベストアンサー
  • hotosys
  • ベストアンサー率67% (97/143)
回答No.3

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

noname#171981
質問者

お礼

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

その他の回答 (3)

  • mitarashi
  • ベストアンサー率59% (574/965)
回答No.4

>あまりデータがきれいでない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

noname#171981
質問者

お礼

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

  • hotosys
  • ベストアンサー率67% (97/143)
回答No.2

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

noname#171981
質問者

お礼

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

  • CHRONOS_0
  • ベストアンサー率54% (457/838)
回答No.1

>値("001")と数値(1)が混在する列を "001"という値はテキスト型です数値型のフィールドには取り込めません >Accessを使われている方々はどんな方法で >あまりデータがきれいでないExcelシートの場合のインポートをされているのでしょうか? Access(データベース)はデータの整合性を確保するために非常に制限が厳しいです きれいでないExcelデータはそのままでは取り込めません

noname#171981
質問者

お礼

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

関連するQ&A

  • AccessでCSVインポートのゼロサプレス停止

    AccessVBAでCSVインポート時にゼロサプレスをせずに行いたいです。 現状は下記のソースでインポートしていますが数字のみが入っているテキスト項目でゼロサプレスが行われてしまいます。 DoCmd.TransferText acImportDelim, , [テーブル名], [ファイル名] 下記のものについては事情があって対処として厳しいです。 ゼロサプレスのみを止めるよい方法はないでしょうか。 ■インポート定義を使用する ⇒50種類ほど列項目が異なるテーブルがあり、また時々フォーマット変更があるため事前に定義しておくことは難しい。 ■表示フォーマットを指定する ⇒5桁項目に4桁のデータであったり3桁のデータである場合があるがそれらをそのまま保持したい。 ■CSV作成時にダブルクオーテーションで囲む ⇒CSVの作成側に対応依頼はしたができないとの回答があった。 現状としてわかっていることとしては、値をダウブルクオーテーションで囲うことでゼロサプレスを回避できることはわかっているため、 Accessマクロの中でExcelを呼び出しダウブルクオーテーション付で保存できないかと考えていますがうまくいっていない状況です。 Access単独、Excelを使う方法などゼロサプレスを回避できる方法をご教示願います。

  • テーブルデータのインポートについて

    バージョンはAccess2003です。 テーブルAとテーブルBがあります。 CSVからデータをインポートし、全データがテキスト型で保存されています。 テーブルBには最終的にデータを保存したいフィールドプロパティが定義されています。 VBを使ってテーブルAからテーブルBにインポートしたいと思っています。 テーブルAのデータをテーブルBの定義に合うように加工するにはどうすればいいのでしょうか? アドバイスよろしくお願いします。

  • AccessにExcelを追加でインポートできない

    Access97でExcelシートを新規テーブルにインポートしました。 1回目はうまく行くのですが、作成されたテーブルに 再度同じExcelをインポートすると 「ファイル~をインポートするときにエラーが発生しました。ファイルはインポートされませんでした。」 となります。 色々なデータで試しましたがOSは98でもXPでも同じでした。 もちろんテキストデータで保存してからインポートするのは問題ありません。 過去に同様の質問があるはずだと思ったのですが 探すことができませんでした。 みなさんのは出来ますか?

  • ACCESSでの改行コード

    ACCESSでの改行コードの入力方法を確認させてください。 ACCESSのテーブルにテキスト型のフィールドを用意し、一つのフィールドに改行が入ったデータを入力したいと考えています。 以下の方法を試してみましたが、改行コードが入っていないように思えます。 1.エクセルで一つのセルで、ALT+ENTERで文字列を入力し、テーブルにデータをインポート。 2.上記エクセルのデータをCSVで保存したものをテーブルにインポート。 これら2つのデータをテーブルで確認すると、改行はされていないですし、テーブルのデータをエクスポートしたものも改行はされていませんでした。 フィールドの設定で何か必要なんでしょうか? それとも、テキスト型のフィールドには改行コードを持てないんでしょうか?

  • エクセルのデータをアクセスのテーブルにインポート

    エクセルのデータをアクセスのテーブルにインポートしたくて、 アクセスから外部データのインポートでエクセルを指定してるのですが ************************************************************************ ワークシート インポートウィザード インデックス、主キー、またはリレーションシップで重複する値が生成されてるため、 テーブルを変更できませんでした。 重複する値のあるフィールドの値を変更するか、インデックスを削除してください。または重複する値を使用できるように再定義してください。 ************************************************************************ になってしまいます。 エクセルの列名を見ても重複していないし(COUNTIF関数で確認しました) アクセス側のウィザードでインデックスはどこのフィールドにもつけてないし、 「主キーを設定しない」もしくは「主キーを自動的に設定する」にしているのに、何をやっても上記のエラーがでます。 ググっても同じようなエラー内容が見つからないのですが、どうしたらいいでしょう? ヴァージョンはエクセルは2007、アクセスは2010です。バージョンの相違が原因でしょうか?

  • Accessにインポートしたら並び順が変わっちゃった

    Access2000 を使っています。 ExcelデータをAccessテーブルにしようとしましたが、全項目をテキスト型で入れたいのに、自動的に各項目の型が変わってしまうので、一旦CSVに落としてAccessにインポートしました。 ところが、インポートされたテーブルではレコード順が違っちゃっているんです。 総数は変わっていませんから、全て入ってはいるんでしょうが。 いったい何がいけないんでしょうか。多量のデータをインポートするのに不安でいけません。誰か教えて下さい。

  • 3つのテキストボックスの値を1つのフィールドに保存

    ご指南ください フォーム上の3つのテキストボックスの値Q ・S・ DをAというテーブルのBというフィールドに保存したいのです   Bフィールド値 QSD としたいのです よろしくお願いいたします ( アクセス2003 OS XP )

  • ACCESSで、テキストファイルをインポートしたい

    宜しくお願い致します。 ACCESSで、テキストファイルをインポートしてテーブルを作成したいのですが、ファイル→インポートを選択して、ウィザードを開く方法は分かるのですが、マクロを作成してインポートは出来ないでしょうか? excel、CSVファイルでしたら、「ワークシート変換」、「テキスト変換」のマクロでインポートしたりエクスポートしたり出来ると思うのですが・・・、 (1)テキストファイルをインポートしたり、エクスポートしたりする場合、どのようなマクロがあるのでしょうか。 (ためしに、「出力」マクロで、あるテーブルをCSVファイルにエクスポートしてみたのですが、カンマ区切りにできず、excelのような枠付きのテキストファイルになってしまいました。) (2)容量が大きいデータの場合は、テキストファイルでエクスポートしたほうが良い・・・という認識に間違いはありませんでしょうか。 質問が一つ増えてしまいましたが、宜しくお願い致します。

  • csvファイルのインポート

    Accessでcsvファイルをインポートさせようとしています。しかし、「F1フィールドがありません」のようなエラー表示が出て、できません。私の予想では、テーブルの型とcsvファイルの型が違うのではないかと思っています。テーブルには、数値型、テキスト型、日付/データ型があります。 保存したcsvファイルをExcelで開くと日付がApr-01や、2001/06/26となったりしていました。どこかで型変換のようなことをするのでしょうか?csvのことはよく分かりません。どうか、よろしくお願いします。

  • Access(アクセス)でのインポートについて

    テーブルのインポートでエクセルファイルをインポートしたいのですが「ワークシートインポートウィザード」の画面でテーブルを指定して保存すると 「Microsoft Access」の画面で「すべてのデータをテーブルに追加できませんでした。 キー違反のため、0件のレコードのデータが失われ、0件のレコードが削除されました。」と出てきて、保存できません。 もとのテーブルのデータ型を使ってインポートするにはどうしたらいいのでしょうか。よろしくお願いします。 インポートする元のデータ(エクセル)とインポート先(アクセス)の関係は次の通りです。 エクセルファイル    アクセスのテーブル (セルの書式設定)   データ型、フィールドサイズ --------------------------------------------- 数値          数値型、長整数型 文字列         テキスト型、16 インポートする前に元のエクセルデータをアクセスのデータ型に合わせただけではダメなのでしょうか

専門家に質問してみよう