【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は開くと以下のような作りになってます。コンテンツの有効化等は適宜行ってください
![Run SQLシート](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2187642/111f1d8c-87df-f167-f7a1-d920240602a8.png)
### CSVを置いているディレクトリを指定
最初に、CSVを置いているディレクトリを指定をします。CSVに対してSQLを実行したい場合は、Dirだけ設定します。
![CSVを置いているディレクトリを指定
](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2187642/4f0afb66-917c-947e-8341-1b00ef4bdfd5.png)
|パラメーター|概要|
|---|---|
|Dir|ここにCSVファイルが保管されているディレクトリを記載。このディレクトリがDB扱いで、直下にあるファイルがテーブル扱いになるようにしています。<br>例） `C:\CSV_Directory`|
|File|ここにExcelファイルのファイル名を記載するとExcelに対してSQLを発行できできるようになります。この場合、ExcelファイルをDB、シートをテーブルとして扱います。<br>例） `data.xlsx`|
### SQLを記載（MS Access構文）
SQLを記載します。MS Access構文になるので、少し癖があります。[参考： MS Access構文](#ms-access-sql)
![SQLを記載（MS Access構文）
](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2187642/95d217fe-ad5d-04ad-fc7a-6257f0d4a9cd.png)
注意点としては、先頭データ使って各カラムを、暗黙的に型設定をしちゃいます(特にCSVにSQL投げる場合)。なので、文字列として扱いたいのに、先頭データが数字だけだと数値データとして扱うことがあります。その場合はもとのCSVファイルを直すか、SQLを工夫してあげる必要があります。
### オプションを設定
ファイルを読み込む際の設定や実行結果をどこに出力するかの設定です。
![オプションを設定](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2187642/0dc4a0df-1839-af3f-dbf2-ec9dedccd6a0.png)
|オプション|概要|デフォルト値|
|---|---|---|
|Charset|文字コード（`S-JIS`, `UTF-8`から選択）|S-JIS|
|Contains Header Line|先頭にヘッダー行があるかどうか。`Yes`にすると、ヘッダー行をカラム名として使います。`No`の場合カラム名が無いので、SQL中ではF1, F2, F3...の様に指定します|Yes|
|Export Result as CSV|実行結果をCSVとして出力するかどうかの設定です。<br>`No`の場合は、実行結果エリアに出力します。Excelなので100万行以上の場合はエラーに落としてます。<br>`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ファイルで出力します）。
![SQLを実行](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2187642/38b64792-976b-9a67-1a3d-9796ffbbbc64.png)
![実行結果](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2187642/dd2b121f-8098-30f5-b862-be988ee0913b.png)
`select`だけでなく、`insert` `delete` `update`もできます。
## ExcelにSQLを実行
ExcelファイルにSQLを実行するには、まず`Target File`に対象となるExcelのファイル名を記載してください。
そのExcelファイルのシートやRangeに対してSQLを実行します。

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`シートに追記することで、他のコマンドも使えるようになります。
![automation](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2187642/dc1f4c15-a2c1-708c-9682-510a9c8b5df8.png)
### 自動化の基本的な考え方
一直線なジョブネットフローを組むようなイメージです。Step1から順番に処理を実行して行くだけになります。
考え方としては、全部をこのExcel SQLで実現するのではなく、バッチ処理や他のプロセスのほうが効率的にできるなら、そっちに任せる、というものです。
```mermaid
graph TB
  Start([Start])-->IFS[/While Loop Count\]
  IFS-->STEP01[STEP 1 - Bashコマンド]
  STEP01-->STEP02[STEP 2 - SQLを実行]
  STEP02-->STEP03[STEP 3 - batch.shを実行]
  STEP03-->STEP04[STEP 4 - SQL]
  STEP04-->STEP05[STEP 5 - export.batを実行]
  STEP05-->IFB[\End While Loop/]
  IFB-->|Loop| IFS
  IFB-->End([End])
```
### 自動化する処理を設定
![自動化する処理を設定](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2187642/17614a48-4d9f-1eda-081e-18b4d5411161.png)
#### 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で実行します。
### 実行範囲の設定
どのステップからどのステップまで何回繰り返し実行するかを設定します。
![実行範囲の設定](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2187642/489d511b-be7a-6481-9670-52100ec9ed7d.png)
|パラメータ|意味|
|---|---|
|Start Step|どのステップから実行するか|
|End Step|どのステップまで実行するか|
|Loop Count|何回繰り返し実行するか|

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

### 実行
実行ボタンクリックすれば、設定したステップを実行していきます。この間、Excelがフリーズするので、別プロセスでExcel実行した方がいいかもです（[既定で新しいインスタンスでExcelを強制的に開く方法](https://docs.microsoft.com/ja-jp/office/troubleshoot/excel/force-excel-to-open-new-instance)）。
![実行](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2187642/da2f3b6d-ffc9-830e-742a-3314897c46ef.png)
### 設定
`automation master`シートにコマンドの種類とVBAから実行するコマンドフォーマットを定義しています。
![automation setting](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2187642/3337a623-4a6b-070b-39fb-ac5732393547.png)
デフォルトで設定しているのは以下です。
|Process|Command Format|
|---|---|
|Bash (Git)|`cmd.exe /S /C call "C:\Program Files\Git\bin\bash.exe" --login -i -c "cd \"{work_dir}\"; {command};"`<br>※cmdで、Git Bachをコールしてるだけ|
|Bash (Msys)|`cmd.exe /S /C call C:\msys64\msys2_shell.cmd -no-start -defterm -mingw32 -where "{work_dir}" -c "{command}"`<br>※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と実行結果（レコード数やエラー）を出力します。
![ExcelSQL_02_LOG_01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2187642/dc0f24ff-6ab9-f91d-7031-f93cf4d7e424.png)

# 参考
以下、参考情報です。
## MS Access SQL
MS Accessはちょっと特殊な構文なので、普段ほかのDB使われてる方にはかなり嫌がられます。参考までに基本的な構文を。
**簡単な例**
```sql:
select * from [data.csv];
```
ファイルは[]で囲む

**カラムの指定**
```sql
select
  item_id,
  item_name,
  amount,
from [data.csv];
```
**カラムのリネーム**
```sql
select
  item_id as itemid,
  item_name as [名前],
  amount as [金額],
from [data.csv];
```
日本語名にするときはカラムを[]で囲う

**JOIN**
```sql
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が使えます。

**集約**
```sql
select
  item_name,
  sum(revenue),
  count(item_name)
from
  [data.csv]
group by
  item_name;
```
[一般的な集約関数](https://support.microsoft.com/ja-jp/office/sql-%E9%9B%86%E8%A8%88%E9%96%A2%E6%95%B0-a810ee71-c1cd-43d7-9c55-8005f1893be5)(sum,count,max,min等)は使えます。Window関数は無いです。

**IF文**
```sql
select
  iif(categ = "1", true, false) as [結果]
from
  [data.csv];
```
IF文は、iif関数です。
`iif(条件,TRUE時の値, FALSE時の値)`

**日付のフォーマット**
```sql
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関数](https://support.microsoft.com/ja-jp/office/%E6%97%A5%E4%BB%98%E3%81%A8%E6%99%82%E5%88%BB%E3%81%AE%E3%83%95%E3%82%A3%E3%83%BC%E3%83%AB%E3%83%89%E3%82%92%E6%9B%B8%E5%BC%8F%E8%A8%AD%E5%AE%9A%E3%81%99%E3%82%8B-47fbbdc1-52fa-416a-b8d5-ba24d881b698)で日付を文字列に変換できます。日付フォーマットのカラムを比較するときは、#で囲ってあげる必要があります。
他にも[様々な関数](https://support.microsoft.com/ja-jp/office/%E9%96%A2%E6%95%B0-%E3%82%AB%E3%83%86%E3%82%B4%E3%83%AA%E9%A0%86-b8b136c3-2716-4d39-94a2-658ce330ed83)が使えます。

**クロス集計(MS Accessの強み)**
個人的に、MS Access構文が使えて一番いいこと（個人的には唯一のそして強力な利点）はクロス集計が簡単にできることだと思ってます。
```sql
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億レコードの分析したことあります。