しのもりのエンジニアっぽいやつのほう: PowerShellでSelect結果をCSV出力

シリーズ目次

その3：PowerShellでSelect結果をCSV出力
その4：PowerShellでテーブルCreateスクリプトを出力

前置き

もう何番煎じか分からないテーマですが、なるべくすっきり書ける方法がないかやってみました

やりたいことは、
・文字コードはUTF8
・ダブルクォーテーションで括る
・1行目はヘッダー情報

まずはこのストアドを見てほしい

	ALTER PROCEDURE [dbo].[EXPORT_SOURCES]
	@RetCode INT OUTPUT,
	@RetMsg VARCHAR(MAX) OUTPUT,
	@RetTable VARCHAR(MAX) OUTPUT
	AS
	BEGIN
	/* export_for_sourceに登録されているテーブルやストアドを出力 */
	/* テーブル出力_データ生成時に手動で登録しているテーブルを出力 */
	DECLARE @name nvarchar(100)
	DECLARE @Filepath nvarchar(1000)
	DECLARE @columnHeader VARCHAR(8000)
	DECLARE @raw_sql nvarchar(4000)
	DECLARE @ColumnList VARCHAR(8000)
	DECLARE @sql VARCHAR(8000);

	DECLARE CSR_table_names CURSOR FOR
	select a.name from sys.objects a where a.type = 'U' and a.name like 'wk_%'

	OPEN CSR_table_names;

	FETCH NEXT FROM CSR_table_names
	INTO @name
	WHILE @@FETCH_STATUS = 0
	BEGIN

	Print @name;

	SET @columnHeader = '';
	SET @ColumnList = '';
	SET @raw_sql = '';
	SET @sql = '';

	SET @Filepath = 'C:\data_converter\from\table\data\' + @name + '.csv';
	SELECT @columnHeader = COALESCE(@columnHeader+',' ,'')+ '''""'+column_name +'""''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @name
	SET @columnHeader = SUBSTRING(@columnHeader,2,LEN(@columnHeader) - 1);
	SELECT @ColumnList = COALESCE(@ColumnList+',' ,'')+ '''""'' + ' +'REPLACE(CAST(['+column_name +'] AS VARCHAR(max)),''""'',''""""'')' + ' + ''""''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =@name
	SET @ColumnList = SUBSTRING(@ColumnList,2,LEN(@ColumnList) - 1);
	SELECT @raw_sql = 'SELECT '+ @columnHeader +' UNION ALL SELECT ' + @ColumnList + ' FROM [' + @name +']'
	SELECT @sql = 'bcp "'+@raw_sql+ '" queryout '+@FilePath +' -t, -r\n -k -c -C65001 -T -S' + ' WNT136\SQLEXPRESS' + ' -d '+DB_NAME()

	SET NOCOUNT ON;
	BEGIN TRY
	EXEC master..xp_cmdshell @sql
	FETCH NEXT FROM CSR_table_names
	INTO @name
	END TRY
	BEGIN CATCH
	SET @RetCode = ERROR_NUMBER();
	SET @RetMsg = ERROR_MESSAGE();
	SET @RetTable = @name;
	END CATCH;
	END

	DEALLOCATE CSR_table_names

	END

view raw export_CSV_by_stored.sql hosted with ❤ by GitHub

うむ、なにやってるのかさっぱり

bcpとsqlcmdでCSVを出そうとした場合、ヘッダー情報が出ないため、まずヘッダー情報をSQLで生成している。
またダブルクォーテーションで括ってくれないので、カラムの前後に付加している。

これはこれで利用価値はあるけど、やはり冗長。
ヘッダー情報をunionしたり、ダブルクォーテーションのエスケープ処理したりで、マジで使いづらい。

なるべくストアド内で済ませようと設計したのですが、一度作って考えてた結果、やはりpowershellでやることに。
powershellならパイプでexport-csvできるので、かなりすっきりするはず！

PowerShellでSelect結果をCSV出力

んで結果こうなりました。めっちゃスッキリ！！

	$strServer = $env:COMPUTERNAME + "\SQLEXPRESS"
	$strDBTo = 'data_convert_to'

	#出力テーブル一覧を取得
	$tables = Invoke-Sqlcmd -ServerInstance $strServer -Database $strDBTo -verbose -Query "select export_table_name,export_order_number from wk_export_table_names where export = 'True' order by 2"

	#ループ処理
	for($i = 0; $i -le $tables.Count - 1; $i++){
	#CSV出力処理
	$SQLStrings = "select * from " + $tables[$i].export_table_name
	$CSVFilePath = "C:\data_converter\" + $tables[$i].export_table_name + ".csv"
	Invoke-Sqlcmd -ServerInstance $strServer -Database $strDBTo -verbose -Query $SQLStrings \| Export-Csv -Path $CSVFilePath -Encoding UTF8 -NoTypeInformation
	}

view raw export_CSV_by_PowerShell.ps1 hosted with ❤ by GitHub

5行目でテーブル一覧を取得。
7行目でループ処理、Select文とファイルパスを生成。
11行目でSelect文を発行して、パイプでExport-CSV、オプションで文字コードUTF8を指定、「-NoTypeInformation」をつけておくと1行目にデータ型が入らなくなります。

出力されたデータを見てみましょう。

ちゃんとヘッダーも出てるし、ダブルクォーテーションもバッチリ！！

次回

その4：PowerShellでテーブルCreateスクリプトを出力