Access VBAで

ExcelファイルをAccessのデータベースに取り込みたいときは

 

DoCmd.TransferSpreadsheet acImport,(インポート元のExcelワークシートの種類・省略OK),[取り込み先となるテーブル名],[インポートするファイルの名前],[インポートするファイルの最初の行に項目名の列があるか],[インポートしたいワークシートの範囲]

 

を使う。

 

csvなどのテキストファイルをAccessのデータベースに取り込みたいときは

 

DoCmd.TransferTextacImport, (インポート元のExcelワークシートの種類・省略OK),[取り込み先となるテーブル名],[インポートするファイルの名前],[インポートするファイルの最初の行に項目名の列があるか],[HTMLファイルをインポートするときに指定する、インポートしたい一覧名],[文字コード]

 

を使う。

 

互換性はないので、扱うファイルによって使い分ける必要がある。

データベースの情報をxls形式のExcelファイルに出力するAccessファイル「Ex」がある。
そのExで出力したファイルを取り込んで、別のDBに登録するAccessファイル「Im」がある。

「Im」は、当案件の開発・修正の対象外なので手を加えないことになっている。

 

Imにデータを取り込んだときに、本来登録されるべきデータは20万件は優に超えているはずなのに
65535件しか登録されていないことが判明した。

 

Microsoftの仕様上、xls形式のExcelファイルは最大行数が65536行しかなく、ヘッダーで1行使うと残り65535行となるので65536番目以降のデータが切られてしまっている
というのが大元の原因だった。

 

「Im」は手を加えられないし、「Ex」はこの案件のために新たに製造したものだから、xlsx形式で出力するように「Ex」を修正すればいいのではないか
と考えられるが
既に業務で使用されている「Im」はxls形式でしか取り込めない(ファイル選択時に選べないようになっていた)仕様なので、その方針は外された。

ひとまず、50000件ごとに分割してxlsファイルに出力し、事なきを得ていたのだが
これまで、「Im」を使っているお客様から、65535件超のデータが取り込めないというクレームもなかったので
お客様はcsv形式で分割しないで出力し、それを「Im」に取り込んで一発で取込を終えていた(はずだ)。だから、「Ex」がxls形式で出力しているのをcsv形式に直すべきだ。
と現場の偉い人が推理し、その方針で直しなはれということに(私が休んでいる間に)決まっていた。
(個人的には、どこからcsvファイルが出てきたのかが謎ではあるが)

違和感を覚えながらも、csvファイルで出力するように「Ex」を改修した。

「Im」でcsvファイルは選べるようになっていたので、そのファイルを選択し、取り込もうとすると

「外部テーブルのフォーマットが正しくありません」

というシステムエラーが発生した。

・・・？？？
え、誰か実際に「Im」を動かして、csvファイルを取り込んでみて、エラーも問題もないってこと、確認してなかったんですか？？？()

自分のデータに不備があるのかと思い、「Im」をデバッグし、何を満たせば正常に取り込めるのか探ってみると

DoCmd.TransferSpreadsheet acImport, , tableName, filePath, True, range(変数名変えてます)

を実行したときにエラー処理が動いた。

根拠を集める
ここでエラーが出るので無理です、直せません(泣)とだけ言っても説得力に欠けるかと思い、
DoCmd.TransferSpreadsheet メソッドについて
調べることにした。

Microsoftのリファレンス
https://docs.microsoft.com/ja-jp/office/vba/api/access.docmd.transferspreadsheet
を見ると、どうもDoCmd.TransferSpreadsheet メソッドはExcelのワークシートを取り込むのにしか対応していないらしい。
上から下まで読んでも、csvの文字はなかった。

また、「Im」にはこのメソッドしか書かれていないので、お客様は(csvファイルは選べたにも関わらず)xlsファイルでしか取込ができなかったのではないかと考えられる。

 

さらに、「では、csv形式のファイルを取り込むには何が必要なのか？」と思って調べてみると
Accessでcsvファイルを取り込むには
DoCmd.TransferTextメソッドを使うという記事を発見した。
https://docs.microsoft.com/ja-jp/office/vba/api/access.docmd.transfertext
https://www.feedsoft.net/access/tips/tips93.html
csvファイルはテキストファイルの一種なので、言われたら納得できる。

自分の仲間内で偉い人に

以下の旨を相談した。
・「Im」は元からcsv形式のファイルを取り込めるようにできていない(選択はできるものの)
・csvファイルにこだわるのであれば、「Im」に対し、csvファイルを正常に取り込むためのメソッドを実装、どの形式のファイルが選択されたのかを判定するIf文や分岐処理が必要
・しかし、「Im」はこの案件の対象範囲外なので、手を加えていいのか否か判断がつかない
・加えて、お客様は本当にcsv形式で運用しているのか(推測ではなく(重要))確認してほしい(お客様が「Im」に対し、何か手を加えていないのかも含め)

 

後日、お客様から「csvファイルを取り込むことはしておらず、分割してxlsファイルを出力し、数回に分けて取り込んでいた」という回答があった。

また、新たに「Im」を改修することはせず、「Ex」に対し50000件を超えるデータを出力するときは分割して複数のxlsファイルを出力することになった。

 

csvファイルをダブルクリックするとExcelが立ち上がることもあるが

厳密にはテキストファイルなので、区別して扱う必要がある
ということと
ミステリーでもない限り、推測・実践なし・確認なしで決めつけて話したり、方針を決めて人を動かしたりするのはよくないなと思った。