• 締切済み
  • すぐに回答を!

AccessでExcelデータをインポート

お世話になります。  Access2003/2010  Excel2003/2010 10年程前より、Access2003で作成したツール(Excel2003形式のファイルを インポート)を利用しております。 Excelファイルは毎日追加/更新しているのですが、先日、とうとうExcel2003 の行の上限である65,536行を超えてしまいました。 今後はExcel2010形式のファイル(行の上限は1,048,546行)とし、かつ、 2010形式のファイルをインポートできるようツールの修正をしなければならなく なりました。 ツールの修正というよりは、今までAccess2003上で使用していたので、そのまま Access2010上で使用したいだけなので、修正が必要がどうかも分からない 状況です。 そこで2点質問ですが、  1.現状インポートする際は、   DoCmd.TransferSpreadsheet acImport, 8, "temp", strFile, True   としておりますが、「8」の箇所は2010形式のExcelをインポートする場合   どのように修正すればよいでしょうか。   ヘルプを見ても8(Excel2000形式)までの記載しかありません。   とりあえず8のままExce2010形式で、かつ2003の行の上限を超えている   ファイルを(66000行)インポートしてみましたが、行は削れることなく   インポートできました。  2.インポート時の文字数制限について   インポートするExcelファイルの「備考」フィールドは文字数が多いのですが、   Access2003上で使用すると全ての文字列(750文字程度)をインポート   できておりました。   しかしAccess2010上で使用すると、文字列が途中で切れる(255文字)   不具合が発生しました   インポートする際は、あらかじめテーブルを作成しておき、そこに対して   Excelのデータをインポートしております。なお、「備考」のデータ型は   メモ型です。   (255文字で切れることから)恐らくAccessがインポートする時点で「備考は   テキスト型」と判断しているから255で切れてしまうのではないか、で、切れた   文字列をメモ型に入れてるだけのように見受けられるのですが。。   2003から仕様が変わってしまったのでしょうか。   上記のことから、インポートするExcelの先頭行の「備考」に、適当な文字列   (750文字)を入力し、それをインポートすると途切れることなくインポート   できました。   ※Accessがメモ型と認識したから? ※いずれもツールをAccess2010上で使用した状況です。 ご教示の程、宜しくお願い致します。

共感・応援の気持ちを伝えよう!

  • 回答数1
  • 閲覧数1592
  • ありがとう数10

みんなの回答

  • 回答No.1

8→10    だと。 以前も似たような質問がありましたが、 http://okwave.jp/qa/q8413669.html 上記は、 varStr = Split(buf, ",", , vbTextCompare) のところを、 varStr = Split(buf, ",", , vbBinaryCompare) と、引数を変更する必要がありましたが。 たとえば、以下のように、メモ型のデータがある場合、配列に 入れ込んでAccessでテーブルに入れるという方法 ぐらいだと思いますが。 例えば、Excelで 番号  名前    住所     質問 1    徳川    東京    秋刀魚は、・・・・・ 2    織田    名古屋   外郎の・・・・・・・ 3    豊臣    大阪    タコヤキの・・・・・ のような、データがあり、Accessに同じ構造の テーブルがあるとして、 Dim db As DAO.Database Dim rs As DAO.Recordset Dim i As Long Dim j As Long Dim k As Long Dim m As Long Dim App As Object Dim obj As Object Dim sheet As Object Dim buf As Variant Dim varData As Variant Set db = CurrentDb Set rs = db.OpenRecordset("T質問", dbOpenDynaset) Set App = CreateObject("Excel.Application") App.Visible = True App.Workbooks.Open (CurrentProject.Path & "\mydata1.xls") 'obj.Visible = True i = App.Worksheets("Sheet1").Range("A1").End(xlDown).Row For j = 2 To i   For k = 1 To 4     If k = 1 Then       buf = buf & App.Sheets("sheet1").Cells(j, k)     Else       buf = buf & "," & App.Sheets("sheet1").Cells(j, k)     End If   Next k   varData = Split(buf, ",", , vbBinaryCompare)   rs.AddNew     For m = 0 To UBound(varData)       rs.Fields(m) = varData(m)     Next m   rs.Update   buf = "" Next j App.Quit Set App = Nothing rs.Close: Set rs = Nothing db.Close: Set db = Nothing のような、感じですかね。 あるいは、大量のデータを処理するならば、 一旦、そのまま DoCmd.TransferSpreadsheet acImport で、入れてその後、メモ型のフィールド のみを配列に入れて上記の一部を以下のようにして、 メモ型のフィールドを書き変えるかでしょうか。 i = App.Worksheets("Sheet1").Range("A1").End(xlDown).Row For j = 2 To i   If j = 2 Then     buf = buf & App.Sheets("sheet1").Cells(j, 4)   Else     buf = buf & "," & App.Sheets("sheet1").Cells(j, 4)   End If Next j   varData = Split(buf, ",", , vbBinaryCompare)   m = 0   rs.MoveFirst   Do Until rs.EOF   rs.Edit   rs.Fields(3) = varData(m)       'あるいは、       'rs!質問 = varData(m)   rs.Update   m = m + 1 rs.MoveNext   Loop この場合は一度にデータを配列に入れますから、 メモリの余裕がなければフリーズ、ということに なるかもしれません。 余裕がなければ、ExcelとAccessを行ったり来たり といことになります。 上記の二つを一つのプロシージャの中に、どちらかの変数(i、j、m など) を違うものして一度にやってしまうか、あるいは、別個にして実行 するかのどちらかだろうと思いますが。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

いつもご回答ありがとうございます。 >8→10    だと。 これってどこかに情報載ってますでしょうか。色々探してはみたのですが。。 とりあえず、10に変更して問題なくインポート(エクスポートも)できることを 確認しました。 元々インポートしているExcelファイルに限界があり、運用自体も変えたい ところではあるのですが、なかなか手を付けられずにいます。 Excelファイルは質問にも書きましたように、既に65000行を超えており かつ、100列ほどあります。 なので教えて頂いた上記処理に耐えられるかどうか。。 なお、レジストリの修正については先日行ってみたのですが、 状況かわらずでした。 TypeGuessRowsを「0」にして、ImportMixedTypesを 「MajorityType」に変えてみたのですが、何か間違ってますでしょうか。 なお、「MajorityType」にするとTransferSpreadsheetの箇所で エラーになったのでTextに戻しました。

関連するQ&A

  • Access Excelインポート時の制限について

    お世話になります。 Access2010 Win7Pro Excelファイルのデータをインポートし、それをSQLサーバーへ エクスポートするツール(Access)があります。 例えば、Excelに「ID」と「メモ」というフィールドがあり、1500行 あったとします。 また、メモには100文字程度の文字列が入力されています。 それをインポートすると、問題なくメモの100文字はインポートできます。 但し、1000行目のメモに500文字入力されている場合、それを インポートすると255文字に削られてしまいます。 これはインポートするときに、先頭から数行がチェックされて データ型が自動的に決められる為、上記のケースだとテキスト型に なってしまい1000行目のメモは255文字に削られている・・・ ということだと思います(仕様)。 なので、現状はExcelをインポートする前に先頭行のメモを255文字 以上入力した状態でインポートし、SQLサーバーへエクスポート後 テーブルの先頭レコードのメモより不要な文字(追加して入力した分)を 削除しております。 当該ツールを使って、インポート⇒エクスポートするのは日々の作業なので 毎回メモに文字を追加して、あとで削除するというのが手間でなりません。 なにかよい方法はございますでしょうか。

  • 【再】AccessでExcelインポート時の制限

    お世話になります。 ※再投稿となります。 Access2010のExcelインポート時の制限につきまして、ご教授頂けたらと思います。 現在、VBAにてExcelをインポートし(※)、その後、SQLサーバーへエクスポートする処理を行っておりますが、Excelの「備考」の文字数が多くなってきた為、途中で文字が切れないよう修正することとなりました。 上記(※)の処理では予めローカルに空のテーブル(temp)を作成し、そこにExcelデータをインポートしております。  DoCmd.CopyObject "", "temp", acTable, "tempcopy"  DoCmd.TransferSpreadsheet acImport, 8, "temp", strFile, True tempはtempcopyをコピーして作成しており、今回tempcopyの「備考」をテキスト型からメモ型に変更しました。 これにより「備考」の文字は途中で切れなくなりました。 ※当該mdbはAccess2003にて作成。 しかし、このmdbをAccess2010で実行すると、文字が途中で切れてしまいます。 Access2010は2003とインポート時の仕様(制限)が異なるのでしょうか?その場合、どのようにすれば文字列が途中で切れずにインポートすることが出来るようになるのでしょうか。 以上、宜しくお願い致します。

  • Access Excelのインポートについて

    お世話になります。 Access2010 Excelインポート時の仕様についてご教示下さい。 フォーム上に「インポート」ボタンを設置し、ボタンを押すと ファイル選択ダイアログが表示され、そこで選択されたファイルを インポートしています。 DoCmd.TransferSpreadsheet acImport, 10, "temp", strFile, True ※strFileはダイアログにて選択されたファイルのパス インポートする前に事前にテーブルを作成しておき、そこに Excelのデータがインポートされます。 事前に作成しておくテーブル(temp)のデータ型は以下の通りです。 ID オートナンバー 契約開始日 日付/時刻型 契約終了日 日付/時刻型 案件名   テキスト型 価格    テキスト型 ※Excelの「価格」は通貨型となっておりますが、いくつかのセルに  文字列が入力されている為、テキスト型としてます。 <質問> インポートすると、「価格」フィールドにてデータ型の変換エラーが 発生します。エラーテーブルを確認したところ、Excelの「価格」に 文字列が入っている行でした。 予めテキスト型を用意しておいても、文字列の行はエラーとなって しまいます。なお、価格(数値)が入力されているものは、文字列として インポートされています(左詰めになっている)。  100000  50000  10000  80000   ・  50000  空白  ← 本来は文字列が入力されている。 予めテキスト型を用意しておいたとしても、インポート時の仕様?で 先頭から数行が数値なら数値フィールドと認識されてしまって、 文字列が削れ落ちてしまうのかなと。 ちなみに、インポートするExcelの先頭行の価格を「'100000」にして インポートしたところ、ちゃんとインポートはできましたが、 2行目以降は¥マークも含めた文字列でインポートされてしまいました。  100000  ¥50000  ¥10000  ¥80000   ・  ¥50000  文字列 どうにか対処する方法はございますでしょうか。 以上、宜しくお願い致します。

  • Access2007へのデータインポート

    Excel2007で作った、2列だけの40,000行にわたるデータがあります。この1列目には数値が入っていて、複数の行に同じ数値が入っています。2列目は文字列です。 これをAccess2007で作ったデータベースに取り込みたいのです。 データベースにはIDフィールド(固有数値)とメモ型に規定されたフィールドがあり、Excelの1列目の数値がAccessのIDフィールドと一致したら、Excelの2列目の文字列を、メモ型に規定されたAccessのフィールドにインポートする作業を目指しています。 結果的に、メモ型に規定されたAccessのフィールドにはたくさんの文字列が連なるようにしたいのですが、方法がわかりません。 教えていただける方がおられましたらうれしいのですが・・・。どうぞよろしくお願いいたします。

  • Accessへのインポートについて

    ExcelからAccessへデータをインポートするとどうしても文字列で09と表示しているところがインポートエラーになり空欄になってしまいます。前に0(ゼロ)がついているからだと思うのですが、インポート後テーブルでデータ形式を変更し入力すればいいんですが余りにも量が多い為、正常にインポートできるようにする方法はないでしょうか?

  • access2010でメモ型がインポートできない

    以前のACCESS2000では出来ていたのですが、ACCESS2010(windows7)にバージョンアップしたところ、csvファイルのインポートで、メモ型のデータが255文字までしかインポートできなくなりました。 調べてみたところ、そのフィールドを、クエリなどで使用しているとキャスト(型変換)されるのでメモ型ではなくなり255文字の制限がかかる、との記述がありましたが、新しいテーブルを作成してインポートしてもできませんでしたので、別の原因かと思われます。 また、ACCESS2000のデータを、ACCESS2010の[ファイル]-[保存して発行]・・・・も行い、拡張子を.accdbにしてみましたが、やはり255文字を超えるインポートができませんでした。 インポート時の[設定]のところのフィールドの情報で、メモ型にしてあるフィールド名が表示されていないのが気になりますが。 文字列の引用符は ” にしています。 ACCESSはデータの25行目までを読み込んでデータ型を自動選択している、という記述もあり、1行目のデータに256以上の文字を入れてインポートもして見ましたが255文字で切られてしまいます。 よろしくお願いいたします。

  • Access97 Excelファイルのインポート

    はじめまして、こんにちわ。 早速ですが、質問させてください。 Access97で既存のテーブルにExcelファイルをインポートした際にエラーがでてインポートできないファイルがありました。その原因を調査している次第です。 Excelファイルは様々な担当者に作成いただく予定ですので、エラーの再現性を確認して、原因となる作業を予め禁止しておきたいと考えております。 (1)ファイル→外部データ取込→インポートからインポートするExcelファイルを選択し、インポートボタンを押したところ、『ファイル○○にアクセスできません。このファイルが存在するか、またファイルの形式が正しいか確認してください』と言うエラーが出ます。 実際にそのファイルは開けますし、同じように作成したファイルはインポートできるものもあります。 中身を全てコピーし、新規作成したExcelファイルに貼り付けて名前をつけて保存したものをインポートすると、問題なくインポートできました。 どういった作業をすると、上記エラーが出るようなファイルになってしまうのでしょうか。 (2)ファイル→外部データ取込→インポートからインポートするExcelを選択し、インポートボタンを押した後、ワークシートインポートウィザードで『先頭行をフィールド名として使う』をチェックすると、『先頭行にAccessフィールド名に使用できない文字が含まれています。』というエラーが出ます。 調べてみると、空白の列がフィールドとしてインポートされるようになっています。Excel上では何も表示されていない列で、その列を削除すればエラーがなくなります。 どういった作業をすると、空白の列や行がインポートされるようになってしまうのでしょうか。 お手数ですがよろしくお願い致します。

  • 【Access】Excelインポート時にエラー

    お世話になります。 <環境>  Windows7 Pro  Excel2010  Access2003 Excelに番号、コード、数量の3列(A~C列)の表があります。その表をAccessにインポートしようとすると実行時エラー2391となります。 インポートはフォーム上のボタンよりダイアログを表示し、Excelを選択させてます。 で、手動でファイル⇒外部データの取り込み⇒インポートで当該Excelを選択し、「先頭行をフィールド名として使う」にチェックを入れタイミングで「先頭行に、Accessのフィールド名として使用できないデータが含まれています。フィールド名として有効な名前が自動的に割り当てられます。」と表示されることから、表外の箇所まで取り込もうとしてエラーになっているかと思われます。 しかし、Excel上で見ても表外の箇所は空白セルで何も入力されてません。 単純に表外の認識されていた5列(D~H)を削除すると、問題なくインポートできました。 ※いったん罫線を消して再度引きなおしてもダメでした。 このインポートしようとしているExcelは、元々別のExcelシートより必要な箇所をコピペして作成したものです。なので、コピペ作業の際に目に見えないゴミのようなものが入力?されてしまったのかもしれません。 また、Excel2010で作成したものを2003形式で保存しています。なので、その辺でのバグなのかもしれません。。 ※インポートする為のExcelは客先にて作成している為、コピペ作業はどのように行われているかは不明です。こちらで検証すると問題なくインポートできます。 上記と同じような現象になった方はいらっしゃいますでしょうか。また、そのときの回避策などございましたらご教授頂けたらと思います。

  • EXCEL→ACCESSインポートでセル内改行が機能しません・・。

    EXCELデータをACCESSにインポートする局面で、 ・EXCELデータのセルは、CHAR(10)によって、セル内で改行されています。 ・これをACCESSへインポートすると、改行が機能せず連続した文字列となってしまいます。 ・ACCESSの該当項目は、メモ型(文字数が多いので)です。 EXCELでの表示と同一の表示がACCESSで得たいのです。 よろしくご指導下さい。

  • ACCESSデータベースのインポート 

    ACCESSデータベースでインポートしたいデータ(拡張子がlog)があるのですが、そのままだと文字列が区切られていなくテーブルに入らないので、毎回 しょうがなく事前に置換ツール等を使ってカンマ区切りにしてからインポートしています。 モジュールなどを使って、インポートする前のデータをテーブルに入れやすい 形(カンマ区切りのtxtファイルぐらいしか思いつかないのですが。。。)に置換・変換っていうのはできるんでしょうか??教えて下さい。。

専門家に質問してみよう