ACE エンジン 経由で CSV ファイルを SQL で読み込む

以前、SQL Server でテキストファイルをSQLで読み込む方法として OPENROWSET をご紹介しました。詳しくは「Apache アクセスログをSQLで読み込む【SQL Server編】」を参照してください。今回は、同じく SQL Server ですが、ACE エンジンを経由して読み込みを行ってみようと思います。

CSV ファイルの配置

せっかくなので、サンプルとして少し実用的な CSV ファイルを使ってみます。厚生労働省から公開されている新型コロナウィルス感染症に関するオープンデータです。こちらにあります。とりあえず、 新規陽性者数の推移（日別）: newly_confirmed_cases_daily.csv をダウンロードし、“c:\csvfiles" というフォルダに配置します。

※記事中で利用しているファイル（9/21時点）は以下から取得してください。

newly_confirmed_cases_dailyダウンロード

リンクサーバーの作成

CSV ファイルを配置したフォルダに対して ACE エンジンをプロバイダーにしたリンクサーバーを作成します。プロバイダーは「Microsoft Office 16.0 Access Database Engine OLE DB Provider」を選択します。

リンクサーバーの作成（１）

ACE エンジンですが、JET エンジンの後継で「Access Connectivity Engine」の略語です。要は Access に外部からアクセスするためのエンジンです。「Microsoft Access データベース エンジン 2016 再頒布可能コンポーネント」をダウンロードして個別にインストールすることもできます。今回はテキストファイルなので、あまりバージョンを気にしなくても良いですが、Access MDB や Excel Book に接続したりする場合はバージョンが重要です。「Wikipedia – Microsoft Jet Database Engine 」に素晴らしくまとまってますね。
さて、リンクサーバーが登録できたら、sp_tables_ex (Transact-SQL) を利用してテーブルの一覧（＝ファイルの一覧）を取得してみます。

EXEC sp_tables_ex CSVFILES;
テーブル一覧取得

ファイル名がテーブル名のようなイメージで一覧が取得できました。勿論、複数ファイルが存在すればすべて表示されます。

schema.ini の作成

この状態でも、あたかもテーブルのようにSQLでアクセスできるのですが、項目名や属性は暗黙変換によって行われるため意図しない結果になる可能性があります。そこで項目名や属性を定義するファイル(schema.ini)を作成します。作成する場所は、リンクサーバーを作成したフォルダと同じ場所です。「Schema.ini ファイル (テキスト ファイル ドライバー)」に詳しい説明があります。
“c:\csvfiles\schema.ini"として以下のように作成しました。フォーマット見ればわかりますが、もちろん、複数ファイル定義が可能です。

[newly_confirmed_cases_daily.csv]
ColNameHeader=True
Format=CSVDelimited
CharacterSet=ANSI
Col1=日付                  DateTime
Col2=都道府県              Text
Col3=新規感染者数          Long

CSV ファイルの読み込み

リンクサーバーを経由して該当テーブル（＝つまり CSV ファイル）を読み込んでみます。

SELECT * FROM CSVFILES...newly_confirmed_cases_daily#csv;
CSV ファイルの読み込み

きれいに読み込めました。

データベースSQL,SQLServer

Posted by takmaru