【Excel VBA】CSVやExcelファイルにSQLを実行するツール

CSVに対して、SQLを実行したい。けどわざわざSQLiteとかqとかいれるのは面倒、できるだけコードを書きたくない、という私みたいな人向けのツールです。

かれこれ10年以上前、私が新人の頃に作ったものなので、稚拙な構成で最適化もなにもないですが、私と同じような境遇の方がいらっしゃればと思い、今さらですが公開しようと思います。

Excel SQL

Github:
https://github.com/taukuma/excel-vba-run-sql-to-csv/

ダウンロード:
https://github.com/taukuma/excel-vba-run-sql-to-csv/raw/main/ExcelSQL.xlsm

使い方

Excelなので、結構皆さん余り抵抗なく使っていただけました。
※特にベテランの方々は、Excelを見ると安心するようで、むしろ好まれてたりしました。ただSQLはMS Access構文のため、エンジニアの方から嫌われてますが・・・。

基本操作方法

ExcelSQL.xlsmは開くと以下のような作りになってます。コンテンツの有効化等は適宜行ってください

CSVを置いているディレクトリを指定

最初に、CSVを置いているディレクトリを指定をします。CSVに対してSQLを実行したい場合は、Dirだけ設定します。

パラメーター	概要
Dir	ここにCSVファイルが保管されているディレクトリを記載。このディレクトリがDB扱いで、直下にあるファイルがテーブル扱いになるようにしています。 例） C:\CSV_Directory
File	ここにExcelファイルのファイル名を記載するとExcelに対してSQLを発行できできるようになります。この場合、ExcelファイルをDB、シートをテーブルとして扱います。 例） data.xlsx

SQLを記載（MS Access構文）

SQLを記載します。MS Access構文になるので、少し癖があります。参考： MS Access構文

注意点としては、先頭データ使って各カラムを、暗黙的に型設定をしちゃいます(特にCSVにSQL投げる場合)。なので、文字列として扱いたいのに、先頭データが数字だけだと数値データとして扱うことがあります。その場合はもとのCSVファイルを直すか、SQLを工夫してあげる必要があります。

オプションを設定

ファイルを読み込む際の設定や実行結果をどこに出力するかの設定です。

オプション	概要	デフォルト値
Charset	文字コード（S-JIS, UTF-8から選択）	S-JIS
Contains Header Line	先頭にヘッダー行があるかどうか。Yesにすると、ヘッダー行をカラム名として使います。Noの場合カラム名が無いので、SQL中ではF1, F2, F3...の様に指定します	Yes
Export Result as CSV	実行結果をCSVとして出力するかどうかの設定です。 Noの場合は、実行結果エリアに出力します。Excelなので100万行以上の場合はエラーに落としてます。 Yesを選択した場合は、Dirに設定したディレクトリにCSVで出力します。CSV出力の場合は、100万行以上のレコードも出力できます	No
Export File Name	上記のCSV出力がYesの場合にどういうファイル名で出力するかを設定できます。未入力の場合はsql_result_YYYYMMDDhhmmss.csvです

SQLを実行

Runボタンをクリックすると、SQLを実行します。実行結果はExport Result as CSVがNoの場合は実行結果エリアに表示します（Yesの場合は、CSVファイルで出力します）。


selectだけでなく、insert delete updateもできます。

ExcelにSQLを実行

ExcelファイルにSQLを実行するには、まずTarget Fileに対象となるExcelのファイル名を記載してください。
そのExcelファイルのシートやRangeに対してSQLを実行します。

SQLの記載方法は基本的に同じですが、テーブルの部分だけ特殊になります。

select categ, sum(amount) as amount from [シート名$]
where categ  = 'AAA' and categ2 <> 'BBB'
group by categ;

※シート名やRangeの後ろに必ず$をつけること

自動化(automationシートの使い方)

automationシートでは、登録した一連の処理をボタンクリック一つで実行できます。処理には、SQL Bash Command Prompt PowerShellをデフォルトで設定できます。
※automation masterシートに追記することで、他のコマンドも使えるようになります。

自動化の基本的な考え方

一直線なジョブネットフローを組むようなイメージです。Step1から順番に処理を実行して行くだけになります。
考え方としては、全部をこのExcel SQLで実現するのではなく、バッチ処理や他のプロセスのほうが効率的にできるなら、そっちに任せる、というものです。

自動化する処理を設定

SQL

SQLを実行するための設定です。実行結果をCSVで出力すれば、後続の処理に噛ませることもできます。
記入内容

パラメータ	意味	記入例
Prosess	SQLを選択します	SQL
Command	実行するSQL	select item from [data.csv]
dir	作業ディレクトリ(CSVが置いてあるディレクトリ)	C:\work
tareget file	SQLを実行するのがExcelの場合はここにExcelファイル名を記載。CSVならブランクでOK
charset	文字コード	UTF-8
header	先頭行をヘッダーとして扱うかどうか	Yes
export	実行結果をCSVとして出力するか	Yes
export as	実行結果をCSVとして出力する場合の出力ファイル名	result.csv

Bash

Bashはデフォルトで2種類設定してあります（Git BashとMsys2）。インストールディレクトリが異なっているかもしれないので、automation masterシートを確認してください（設定方法はこちら）

記入例

Process	Command	dir
Bash (Git)	grep -e "error" data.log > target.log	C:\work

BashコマンドはそのままCommand欄に記載できます。wgetでデータとってきたり、grepしたり、sedやawkでCSVに整形したり。dirはワーキングディレクトリです。ここに設定したディレクトリ上でコマンドを実行します。

コマンドプロンプト

コマンドプロンプトのコマンドもそのまま記載できます。
ProcessにCommand Promptを選択してください。

PowerShell

PowerShellも同様です。ProcessにPowerShellを選択すると、設定したコマンドをPowerShellで実行します。

実行範囲の設定

どのステップからどのステップまで何回繰り返し実行するかを設定します。

パラメータ	意味
Start Step	どのステップから実行するか
End Step	どのステップまで実行するか
Loop Count	何回繰り返し実行するか

例えばStep2～Step5までを10回実行するとしたい場合は、Start Step=2 End Step=5 Loop Count=10とすればOKです。

実行

実行ボタンクリックすれば、設定したステップを実行していきます。この間、Excelがフリーズするので、別プロセスでExcel実行した方がいいかもです（既定で新しいインスタンスでExcelを強制的に開く方法）。

設定

automation masterシートにコマンドの種類とVBAから実行するコマンドフォーマットを定義しています。

デフォルトで設定しているのは以下です。

Process	Command Format
Bash (Git)	cmd.exe /S /C call "C:\Program Files\Git\bin\bash.exe" --login -i -c "cd \"{work_dir}\"; {command};" ※cmdで、Git Bachをコールしてるだけ
Bash (Msys)	cmd.exe /S /C call C:\msys64\msys2_shell.cmd -no-start -defterm -mingw32 -where "{work_dir}" -c "{command}" ※cmdでMsys2をコールしてるだけ
Command Prompt	cmd.exe /S /C cd "{work_dir}" & {command}
PowerShell	powershell -Command "cd ""{work_dir}""; {command};"
SQL	{command}

Git BashやMsysのインストールディレクトリが違う場合は、適宜変更してください。また、ここに追記すれば、どのコマンドでも実行できるようになります

実行ログ（SQL）

Run SQLシートでSQLを実行すると、logシートにSQLと実行結果（レコード数やエラー）を出力します。

参考

以下、参考情報です。

MS Access SQL

MS Accessはちょっと特殊な構文なので、普段ほかのDB使われてる方にはかなり嫌がられます。参考までに基本的な構文を。
簡単な例

select * from [data.csv];

ファイルは[]で囲む

カラムの指定

select
  item_id,
  item_name,
  amount,
from [data.csv];

カラムのリネーム

select
  item_id as itemid,
  item_name as [名前],
  amount as [金額],
from [data.csv];

日本語名にするときはカラムを[]で囲う

JOIN

select
  list.title,
  list.unit_price,
  sales.revenue
from
  [item_list.csv] as list
left join
  [sales_data.csv] as sales
on
  list.id = sales.item_id
order by
  title asc;

JOIN区はinner/left/right/outerが使えます。

集約

select
  item_name,
  sum(revenue),
  count(item_name)
from
  [data.csv]
group by
  item_name;

一般的な集約関数(sum,count,max,min等)は使えます。Window関数は無いです。

IF文

select
  iif(categ = "1", true, false) as [結果]
from
  [data.csv];

IF文は、iif関数です。
iif(条件,TRUE時の値, FALSE時の値)

日付のフォーマット

select
  format(log_date, "yyyy/mm/dd hh:mm:ss") as [日付]
from
  [data.csv]
where
  log_date BETWEEN #2022/5/1# AND #2022/5/31#

format関数で日付を文字列に変換できます。日付フォーマットのカラムを比較するときは、#で囲ってあげる必要があります。
他にも様々な関数が使えます。

クロス集計(MS Accessの強み)
個人的に、MS Access構文が使えて一番いいこと（個人的には唯一のそして強力な利点）はクロス集計が簡単にできることだと思ってます。

transform
  sum(revenue)
  select
    shop,
    item
  from
    [data.csv]
  group by
    shop,
    item
pivot
  month;

最後に

私がこのを作った動機で、また使い続けている理由です。同じ境遇の方がもしいらっしゃって、このツール少しでも助けになるのであれば幸いです。

このツールはこういう人向けです

CSVファイルがいくつかあって、集計や結合にSQL使いたいな、でもMS Access立てたり、DBにデータ取り込んだりしてまではやりたくないな、と思ってる人

このExcelツールでできます。DB立てなくて良いです！

そしてなにかインストールしてまやりたくない(学習コストが面倒)し、いまあるものでなんとかできないかな、と思ってる人

このツールはただのExcelです。SQL書ければ使えます！

100万行以上あるCSVファイルの分析しないといけないんだけど、Excelでは限界だし、かといってコマンドラインやプログラムを書いてまでやりたくないな、SQL使えないかな、と思ってる人

このツールは100万行以上の処理できます！

ログから特定のデータを抽出して集計してレポート作成するフローを、定期的に何度もしなくちゃいけないから、効率化できないかな、と思ってる人

それautomationシートでできます！

エンジニアだけじゃなく、営業や企画でもさくっと分析できるようにな無料のツールないかな、と思ってる人

Excelなので大抵の人、使えます！SQLを事前に埋め込んであげれば、ボタンひとつで結果がでて来ます！

尋常じゃない量のデータ解析しろって言うけど、それできる分析基盤うちにないし、予算もないから新設できないし、そんな時間もないのにあたらしいソフトウェアをインストールしちゃダメとか、うちの上司なに考えてるの！？心のなかで発狂してる人

このツールで4億レコードの分析したことあります。