More than 1 year has passed since last update.

[SQL]Access 256を超える列数のデータベースを分割インポートする方法 How to 255 over colomns xlsx File To Split and To Import Table

Last updated at 2022-07-26Posted at 2022-07-24

前回の続き

Access SQL Access>>Excel Select INTO でテーブルクエリをxlsbに出力する Access Query Can Export xlsb file from Table or Query With Acrion Query Select Into
[VBA SQL]Access のテーブルとExcelのxlsx形式ファイルでImport Exportを行う方法　How to Import And Export between Accss Table To xlsx Worksheet
前回でついにExcel(.xlsx)からAccessへクエリによりテーブルにインポートできるようになった。
それでは今回はExcelからテーブルにインポートする方法をからさらに進める。
公式の解説では
Select intoは].[Sheet1$]をさらに].[Sheet1$A1:J5]のようにかけます。

これを応用すれば AとB列 CからJ列と分けられるのでしょうか。さらにそれを別のテーブルに出力できるのか。

結論からいうとできる

やればわかりますが、これは可能。
したがってCSVファイルなどもxlsxファイルにして分割して取り込めばいい
まずCsvからxlsx
Workbooks.Open メソッド (Excel)
Workbook.SaveAs メソッド (Excel)
コンマ区切りを前提とすると
Workbooks.Open "C:\hoge\ImportTabe.csv", 0, False, 2
そしてファイルがあれば削除して
Workbook.SaveAs "C:\hoge\ImportTabe.xlsx", 51
保存。xlsxは51を指定してください。

ただし1テーブルは127列で切ること

このとき、基本的に255を使い切ってはいけない。
内部に記録されてそれ以上テーブルの変更ができなくなるためである。

クエリー実行でエラーが発生する(Access 97) - MOOG

これはAccess97が一度に更新できるフィールド数はJETデータベースエンジンの仕様から約127までです。クエリー等が動作する時には内部的な作業フィールドを必要としますがその数は更新を指定したフィールド数の約2倍となります。Access97で1つのテーブルに作成できるフィールド数の上限が255ですのでほぼ半分の127個までがおよそ1度に実行できるフィールド数であることになります
ACC2000: "Too Many Fields Defined" Error Message Saving Table -pkisolutions
[ACC2003] テーブル保存時にエラーメッセージ "定義されているフィールドが多すぎます。" が表示される

詳細
Access では、テーブルに最大 255 個のフィールドを定義できます。しかし 255 個のフィールドを作成後、10 個のフィールドを削除した場合、Access 内部の列のカウントから削除したフィールド数が差し引かれないため、内部カウントは255のままとなります。
また、フィールドのプロパティを変更した場合、新たにフィールドが作成され内部のカウントもまた追加されます。しかし、同様に元のフィールドのカウントは差し引かれないため、内部のカウントは 1 増加することになります。

この仕様は変わっていない。
Access の仕様制限
つまり、255は限界値でこれ以上変更がない場合だけが使える。
常識では97時代のクエリの記事と、あとの記事から127が限界だと考えたほうが良い。
内部構造自体はそこまで変わっていない。メモリが増強されたので127より一度にクエリは更新がかかるかもしれないが。
本質的には半分が前提だろう。修正の余裕のため倍持っていると考えたほうが良い。
もともとExcelはIV列、つまり256列が限界だった。なのでこれで良かった。
しかし現在のxlsxは256を超えるテーブルが作れてしまう。
Excelではフィールドのデータ型を完全に強制はできないが、ある程度はできる。
しかしデータ型を完全に強制できないし、あとからクエリで組み合わせるためにはIDを振る必要があることから、必ず1列必要となる。
また、あとの解説のように、テキスト型の字数制限は行サイズに影響しない。
このため、どうしても列数がぎりぎりにならぬよう、半分にしておくことが妥当である。

フィールドサイズの設定

テーブルの数値フィールドのフィールドサイズプロパティを変更することで、Access テーブル内の各レコードで使用される領域サイズを調整できます。テキストデータを保存するフィールドのフィールドサイズも変更できますが、この操作を行っても、使用される領域サイズには大きな効果はありません。

注: .accdb ファイル形式を使用した場合、レプリケーションはサポートされません。

ここはこのように記載されているが、一応テーブルデザインで定義可能

　[フィールドプロパティ] ウィンドウで、[全般] タブの [フィールドサイズ] プロパティに新しいフィールドサイズを入力します。 1 から 255 の範囲の値を入力できます。この数値は、各値に含めることができる文字数の最大値を指定します。これよりも大きなテキストフィールドにはメモ型 (Access 2016 を使用する場合は長いテキスト) を使用します。

注: テキスト型フィールドのデータ (Access 2016 を使用する場合は短いテキスト) では、実際の値を保存するのに必要な領域だけが確保されます。 "フィールドサイズ" プロパティはフィールド値の最大サイズになります。

つまりテキストの文字数を制限しても最初から255文字分が確保されている。唯一の圧縮手段はUNICODE圧縮である。しかしこれも修正すれば列数が加算される。最初個のエラーが出たとき、行サイズだと勘違いして懸命に字数を減らしていたが、短いテキスト型は字数を制限しても使う容量は同じ。 [Access開発の落とし穴？→フィールドサイズ](https://www.out48.com/archives/1230/) ユニコード圧縮に触れていないが、それを考慮外とすればテキスト型は領域設定だけメモリを食う。圧縮できるとすれば倍精度をバイト型、整数型にするテキストになっている数字を適切な型にする。ということになる。

解決方法は列数が上限だと有効ではではない

データベースを最適化します。それには [ツール] メニューの [データベースユーティリティ] をポイントし、[最適化/修復] をクリックします。
または

次の手順でテーブルの新しいコピーを作成します。

テーブルのリレーションシップをすべて書き留めておきます。

テーブルを選択します。

[ファイル] メニューの [名前を付けて保存] をクリックします。

['テーブル名' テーブルの保存先] ボックスに新しい名前を入力し、[OK] をクリックします。

手順 2 で選択したテーブルを再度選択し、Delete キーを押します。

手順 3 で保存したテーブルの名前を、元のテーブル名に戻します。

新しいテーブルに、先ほど書き留めておいたリレーションシップを設定します。

しかしこれも255列を使い切っていれば解決できない。列数も増やせば255を超える。
このため127で作成し、上記の手順を行うことで完成する。

選択クエリでIDを参照して結合し、このうちから255列選んで表示させるとよい。
CSVファイルでもExcel化すればこの方法が使える。
255列を超えると普通の方法ではインポートができなくなるが、この方法ならExcel側で分割することなくできる。
複数値を持つフィールドなどは無理だが、
また、シートは別になるが、エクスポートで違うシートに出力する。もしくは合成したクエリでエクスポートする。
これは前回の手順のとおりとなる。

設定テーブルの作成（任意）

さらに、設定をテーブルにしておく。もちろんExcelでも良いが、資料が散らかるので同じaccdb内に作成している。
（複数のファイルからインポートする場合はシート名）
（複数のシートからインポートする場合はシート名）
（複数のテーブルに分割する場合はテーブル名）
0から始まるAccessのフィールド番号（空白許容）
1から始まるExcelの列番号（空白許容）
Aから始まるExcelの列番号（空白許容）
フィールド名（重複許可、空白なし）
（別名があれば別名）
データタイプ（重複許可、空白なし）
備考整数型、ハイパーリンク型への変更などがあれば記載。文字列上限があれば記載。

と言ったテーブルである。カッコのフィールド名は状況に応じて加える。
テーブルをまとめて、もしくはテーブル毎に作成する。
ここでフィールド名が予約後になっていないか、IDを除いてフィールド名が重複しないかがを確認する。
IDも本当はテーブル名IDのようにしたテーブル名ほうが良い。
また、フィールド名もID、F001C金額, F003数量, F004Bバイト数, F005D日付、F006T時刻、F007YYesNO , F008U氏名,F009M詳細
Uはユニコード圧縮型ほか B 大きな数 E OLE型 H ハイパーリンク型　I 整数型　 J オブジェクト型 Mメモ型（長文字型）N 十進型(Numeric）L、長整数型 , R レプケーションID型 S 単精度浮動小数型 V（Multiple value)複数値
Numeric Decimal) 数量（倍精度は名前でわかるので特に記述なし）ショートテキストも名前でわかるので区別しない。ほかも名前でわかれば記述しない。と決め手列番号、を書く。最初が数字だとエラーになるのでフィールドのFがつく。そしてID列は0なので、IDでよい。

というように列番号、データタイプがわかるようになっていると理想。しかし、見づらいのは確か。
そこでテーブルに記録することでデータ型の再設定のときに参照することが可能となる。
普段は隠しテーブルにしておいても良い。

データ型の変更

テーブルの複数フィールドのデータ型をまとめて変更する - ヘルプの森
ただし、十進型には変更できない。
また整数型 Integerは SmallInt
大きな数は BigInt
ALTER TABLE ステートメント (Microsoft Access SQL)
Database.Execute メソッド (DAO)
CuurentDB.Execute "ALTER TABLE tablename ALTER COLUMN fieldnam Date;" , dbFailOnError
公式では更新や削除のときはロールバックができるように dbFailOnError を付与するように勧めている。この命令はこのオプションがないと失敗してもそのまま処理が進む。
CuurentDB.Execute "ALTER TABLE tablename ALTER COLUMN fieldnam DECIMAL(18,3);" , dbFailOnError
例えばこのようなSQLはAccessでは実行ができない。もしオプションがないと何事もなくスルーしてしまう。
しかし、いちいちとまるのもまた大変なので、このオプションがあるのは妥当だろう。
なお、ADOXを使用しても十進型は列を追加した時しかできない。
なので、次のデータ型の変更が難しい場合に移行する。

データ型の変更が難しい場合

ACCDBとmdBで対応が異なる

Access クエリで CDec() 関数を使用する場合、"入力した式に間違った数の引数を含む関数があります"
MDBの場合は関数を自作する
accdbファイルの場合
データ型変換関数

テキストで読み込むのもありえる

ということは演算誤差が気になる場合には、一旦テキストでテーブルを作り、クエリ等でCDECに変換すれば良い。
また、ミスがあるかもしれない場合もテキストで読み込み、エラーを排除すればよい。
例えば日付ならISDATEがある、
これは数千行以上のテーブルで、簡単に目視で確認できない場合に有効。

これはどちらがいいかだが、基本はテーブルのデータ型を最適化するほうだろう。
ALTERをたくさん書いたりすれば煩雑であるが、SQLでは変換しないため、スピードがあがる。

他の方法はないのか

Domcd

Domcdや標準の方法はエラーになってしまう。
この場合、別シートにあらかじめ分割するしかない。
しかし、分割してDoCmdがメジャーだと思う。

RecordSet

これも255に制限される。
メモ型はうまくいかない。

結局255列に分割するしかない。
また、テーブルの定義を自分で作らなければならない。
リンクされた Excel スプレッドシート列のデータは、Access データベースの 255 文字に切り捨てられます。
ACCESS2013もACCESS2007同様に列数が255を超えた場合も、255個迄取り込むようようには出来ないのでしょうか？

１．ACCESS2013
１）ACCESS2013は列数が255を超えると
　｢インポートするテキストファイルには256個以上の列が含まれているため、この処理は実行できません。
　　　　まずソースファイルのバックアップコピー作成し、
　　　　列数を255個以下にしてから、インポートを実行
　　　　して下さい｣というエラーが表示され取り込めませんでした。
　　
２．ACCESS2007
　　１）ACCESS2007は列数が255を超えた場合は、
　　　　255個迄取り込めました。
答え：Access 2013 でも 255 列を超えた場合に 255 個までインポートされるようにする方法がないかを探してみたのですが、有効な方法は見つかりませんでした。。

配列で読み込む方法

CSVとなる。
"12.00","1,000.23","あ","¥2000"
ShiftJisはPrint＃が良い。FSOがいいという意見もある。
ADODOB.Streamも有効だ。
このとき、
1行ずつ読み込む
buf =.readText(-2)
次にこれをタブ区切りに変更する
Buf = replace(Buf,""",""","""" & vbtab & """",1,-1,vbTextCompare)
さらに日付に見える部分は日付になるよう正規表現で処理する。

ar = splirt( buf , vbtab)
こうしないとコンマ区切りを切ってしまうため。
ただし、この場合、最初に型指定したテーブルを用意しなければならない。
Createで作成するのはとんでもないことになる。
こうした場合には空のテーブルを隠しておいて、コピーして使うことが多い。
VBAでそれもなくなったときに再作成するようにする。
空のテーブルが見えていると誤操作される可能性があるためである。
この場合には、再作成が不要なので、255列でも良い。
その一方で、255列を超える場合、このようなタブ区切りへの変換がうまくいかない場合がある。

テーブルのフィールド数が255を超える場合の対処法
全然技術ではないが、このように列数ではなくID番号で縦にするのも有効。

ポイント

メモ型は危ない。
長いテキスト型になるHyperLinkは危ない
255文字というのは危ない。200字程度にする。
文字制限は容量に関係ない。

Sample

前回の例を考えてIV列がある。
そこで、3つに分割する。
実際はコードの意味などを考えて区切る必要があるが、ここでは列数だけでやってみる。迷ったときはこの方法が覚えやすい

AZ104分割法

AZは26 127に収まるにはA-Zを4回繰り返すと

$26*4=104$

これを利用して104ごとに分割する。
実際は意味があるのでこのように行かないが、多少のズレは許容できる。
なお、行数はすべて同じ行数をインポートすること。
列が重複しないこと。
行数も巨大な場合は100行だけ取り込んで見る。
また、テーブルを構造だけコピーして
Insert　Intoを使うこのとき、途中からなのでHDR=NOとなる。

SELECT *
INTO T_PartA
FROM (SELECT * FROM [Excel 12.0 Xml;HDR=YES;IMEX=1;DATABASE=C:\hoge\IMportTable.xlsx].[Sheet1$A1:CZ1000]);

CZが104なのとわかりやすい。

SELECT *
INTO T_PartB
FROM (SELECT * FROM [Excel 12.0 Xml;HDR=YES;IMEX=1;DATABASE=C:\hoge\IMportTable.xlsx].[Sheet1$DA1:GZ1000]);

GZが208

SELECT *
INTO T_PartC
FROM (SELECT * FROM [Excel 12.0 Xml;HDR=YES;IMEX=1;DATABASE=C:\hoge\IMportTable.xlsx].[Sheet1$HA1:IV1000]);

IVまで48となる。

IDは形を変換してIndex

PartAはIDがDoubleなので

ALTER TABLE  [T_PartA] ALTER cOLUMN [ID] LONG;

Create Unique Index ID ON [T_PartA] [ID] With Primary;

追加クエリ Insert Intoとポイント

列名が必要

列の途中から追加する場合、列名がわからないので、エラーになる。
すでに取り込んだ行や、不要な行を削除し、取り込む列を2行目からにする。もしくは、読み込ませたい行の上の行に列名を挿入する。つまりB1のように指定した最初の行に列名が入らなければならない。逆に3行目から入るのであれば4行目は読める。
これはHDR=NOでも同じ。1行目は必ず列名として読む。1行ずつにしても同じ。
また、idを付与した状態でも有効

INSERT INTO T_PartA
SELECT *
FROM (SELECT * FROM [Excel 12.0 Xml;HDR=YES;IMEX=1;DATABASE=C:\Hoge\IMportTable.xlsx].[Sheet1$B1:C5]);

またWhere ID =はできない。読み込む範囲にID列がないため。

PartB、PartCにIDを付与する。まとめて主キーとする。

CREATE INDEX ステートメント (Microsoft Access SQL)
CONSTRAINT 句 (Microsoft Access SQL)](https://docs.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/constraint-clause-microsoft-access-sql)

ALTER TABLE  [T_PartB] ADD COLUMN [ID] Counter PRIMARY KEY;

ALTER TABLE  [T_PartC] ADD COLUMN [ID] Counter PRIMARY KEY;

VBA

ID列を付与して、順番を入れ替えるのをまとめて。
これは1回しか実行できないので、このように一時クエリでいいと思われる。
Access VBA to Set Field Location
一番最後(右）に作って、0とすると最初（左）に来る。
もちろんこうした例は検討していた。
ACCESS VBA MSが教えてくれないテーブルに主キー(Primary Key)index Fieldを追加する方法
こちらにもこの項目のVBAを追加したが、このその2の項目は当時不明だった。
ふと今回、StackOverFlowをみてなんとなくCurrentDbから記述したら成功した。
ただし、この移動量はいくつくらいか。列数が大きいときは定義が多すぎますエラーが出るかもしれない。単純に変更するだけなら1だが、移動するとなると、ほかを全部入れ替えていないか。そうするとはね上がる。

Sub ADDPrimaryKeyFieldAndChangePosition()
Dim cDB As DAO.Database:: Set cDB = CurrentDb
Dim fld0 As DAO.Field, fld1 As DAO.Field
Dim fldName As String
Dim tName As String
tName = "T_PartB"
fldName = "ID"
DoCmd.RunSQL "ALTER TABLE  [" & tName & "] ADD COLUMN [" & fldName & "] Counter PRIMARY KEY;"
cDB.TableDefs(tName).Fields(fldName).OrdinalPosition = 0 ' なぜかこのように指定したら成功
CurrentDb.TableDefs(tName).Fields.Refresh
CurrentDb.TableDefs.Refresh
End Sub

参考文献

インデックスを作成する(CREATE INDEX文, ALTER TABLE文) - javadrive
フィールドのデータ型の設定を修正または変更する

取りあえすいろいろな欠点はあるが、きちんとした形式でエクセルに読み込まれているのであれば、必ずこれで分割してインポートができることがわかった。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up