TL;DR
中間ファイルの作成
> sqlcmd -f 65001 -i './query.sql' -b -s "`t" -W -o './results.tsv'
中間ファイルの洗浄
> $filtered = Get-Content -Path './results.tsv' |
Where-Object { $_ -notmatch '^-+\s*-+' } | # Remove the separator line (e.g., "----- -------")
Where-Object { $_ -notmatch '\(\d+ rows affected\)' } | # Remove the rows affected message
Where-Object { $_ -notmatch '^\s*$' } # Remove empty lines
> $filtered | Set-Content -Path './results.tsv' -Encoding UTF8
Excel出力
> Import-Csv -Path './results.tsv' -Delimiter "`t" |
Export-Excel -Path './results.xlsx' -AutoSize -AutoFilter -Show
はじめに
SQL Serverから特定のSQLを実行し、データを出力するには、SSMSで手動出力の他に、PowerShellの sqlcmdコマンドでCSVファイルなどを出力する方法もあります。
しかしsqlcmdコマンドで出力する際に、下記のように、----- ----や(3 rows affected)など、後続処理に影響する行も出力されます。
また、行数の多いデータを出力した際、CSVのままだとファイルサイズが大きく、扱いにくい面もあって、せっかくだしExcelファイルとして出力したいです。
方法
CSV等の出力
まずは下記のようなSQL文を作ってDemoを行います。
WITH TestData AS (
SELECT
'ITEM-001A' AS ProductCode, -- 英数字の文字列
N'りんご' AS ProductNameJP, -- 日本語の文字列
100 AS Quantity, -- 整数
123.45 AS UnitPrice, -- 小数
CAST('2025-01-15' AS DATE) AS OrderDate, -- 日付
CAST('2025-01-15 09:30:00' AS DATETIME2) AS LastUpdatedDateTime, -- 日付と時刻
NULL AS Status, -- NULL値を含む文字列
1 AS IsAvailable -- 真偽値 (1=True, 0=False)
UNION ALL
SELECT
'ITEM-002B',
N'バナナ, お得', -- コンマ付き文字列
250,
67.89,
CAST('2025-03-20' AS DATE),
CAST('2025-03-20 14:00:00' AS DATETIME2),
'ACTIVE',
0
UNION ALL
SELECT
'ITEM-003C',
N'みかん',
50,
99.00,
CAST('2025-05-10' AS DATE),
CAST('2025-05-10 18:45:00' AS DATETIME2),
'DELETED',
1
)
SELECT * FROM TestData
ORDER BY ProductCode;
上記SQLの結果の中に、,が入っているので、SSMSで出力する際はちゃんとダブルクオーテーションで囲んでくれますが、残念ながらsqlcmdコマンドはできませんので、タブ区切りのTSVファイルとして出力します。
> sqlcmd -f 65001 -i './query.sql' -b -s "`t" -W -o './results.tsv'
上記コマンドのオプションを簡単に解説します。
-
-f 65001: UTF-8として出力すること -
-i './query.sql': 実行したいSQLファイル -
-b: SQLエラーが発生した際、バッチそのものを停止 -
-s "``t": 列の区切り文字を\t(Tab)にする -
-W: 各セルの余分なスペースを削除する -
-o './results.tsv': 出力ファイルの指定
もしSQLの結果にタブも存在していれば、;, :, |など別の区切り文字を試してください。
セパレーター行、rows affected行の削除
> $filtered = Get-Content -Path './results.tsv' |
Where-Object { $_ -notmatch '^-+\s*-+' } | # セパレータ行 "----- -------" の削除
Where-Object { $_ -notmatch '\(\d+ rows affected\)' } | # "X rows affected" 行の削除
Where-Object { $_ -notmatch '^\s*$' } # 空の行の削除
> $filtered | Set-Content -Path './results.tsv' -Encoding UTF8 # 処理結果の上書き保存
SQL文が変更できる場合
SQL文の一番最初に、下記の記述を入れると、(X rows affected)の行も出力しなくなります。
set nocount on;
Excelに変換
> Import-Csv -Path './results.tsv' -Delimiter "`t" | Export-Excel -Path './results.xlsx' -AutoSize -AutoFilter -Show
上記コマンドのオプションを簡単に解説します。
-
-Delimiter "``t": 区切り文字を\tにする -
-AutoSize: 列幅をAutoSizeに -
-AutoFilter: ヘッダーにフィルターを付与 -
-Show: 変換終えたら、Excelファイルを開く
余談: DateTimeの一括変換
出力データに日付や時間がある時、最初はこんな感じの文字列になり、一回クリックすると、やがてDateTime型と認識されます。
しかし数万行の大きいファイルでは、一括DateTime変換じゃないと仕事にならないので、以下の方法を使いました。
まず、SQL文のDateTimeの列にあえて.000のようなゴミ文字列を入れて出力します。
SELECT
ProductCode,
ProductNameJP,
Quantity,
UnitPrice,
FORMAT(OrderDate, 'yyyy-MM-dd.000') OrderDate,
FORMAT(LastUpdatedDateTime, 'yyyy-MM-dd HH:mm:ss.000') LastUpdatedDateTime,
Status,
IsAvailable
FROM TestData
ORDER BY ProductCode;
とすると出力されたExcelの該当列がこんな感じになります。
そしてExcelの検索置換機能を使って、 SQLに仕込んだ.000などを一括削除します。
Excelはこの検索置換の作業中、自動的にDateTimeを認識し、変換してくれます。