PowershellでCSVをExcelファイルに転記する
状況
DBの特定のテーブルをちょっと結合したりなんかして特定フォーマットのExcelに出力して欲しい
こんな要件が結構あります。
私が入る前はこんな感じの要件1つに付き1つアプリ組んでたんですね。
今回そんな感じのを頼まれたので、できるだけアプリを組まないようにスクリプトで完結させました。
本記事はその時の成果物です。
転記ツール
構成
- Csv2Excel.ps1
- /lib
- EPPlus.dll
フォルダ構成書きにくい。 今回EPPlusを使っていて、理由は以下です。
- 以前PowershellでComObjectからExcelを使ったところ、色々やってもプロセスが消えなかった。
- サーバー上でロックしている状態で動くので、Comの方のExcelを使うと正常に動かない。
スクリプト
引数
引数名 | 説明 |
---|---|
sourceDataPath | CSVのパス |
templatePath | テンプレートとなるファイルのパス |
destinationPath | 出力先パス |
configPath | 設定ファイルのパス(json) |
設定ファイル
バッチからPowershellスクリプトを実行するので、列になるオブジェクトやバッチ本体を修正せずに調整したい項目はjsonにしました。
{ "delimiter":",", # 区切り文字 "start_location": { # 開始位置 "row": "2", "column": "1" }, "numeric_columns": [10,11] # 数字のカラム(Stringそのまま入れたくない場合指定) }
ソースコード
param( [parameter(mandatory=$true)][string]$sourceDataPath, [parameter(mandatory=$true)][string]$templatePath, [parameter(mandatory=$true)][string]$destinationPath, [parameter(mandatory=$true)][string]$configPath ) <# ライブラリ参照 #> [System.Reflection.Assembly]::LoadFrom((Join-Path (Split-Path $MyInvocation.MyCommand.Path) "\lib\EPPlus.dll")) | Out-Null <# 設定ファイル読む #> $cfg = Get-Content -Path $configPath -Raw | ConvertFrom-Json <# Excelのフォーマットに転記して、出力先に名前を付けて保存する #> $pkg = New-Object OfficeOpenXml.ExcelPackage -ArgumentList $templatePath $sht = $pkg.Workbook[0].Worksheets[1] $csvRows= Get-Content -Path $sourceDataPath -Encoding Default for($ri=0; $ri -lt $csvRows.length; $ri++) { $csvRowData = ($csvRows[$ri]) -split $cfg.delimiter for($ci=0; $ci -lt $csvRowData.length; $ci++) { $row = $ri + $cfg.start_location.row $col = $ci + $cfg.start_location.column $value = if($cfg.numeric_columns.Contains($col)){ [double]($csvRowData[$ci]) } else { [string]$csvRowData[$ci] } $sht.Cells.Item($row, $col).Value = $value } } $pkg.SaveAs($destinationPath) $pkg.Dispose()
以上!
情報リテラシーとは何か。英国図書館情報専門家協会の記事を参考に。
新入社員の教育のため、「情報リテラシー」というネタが振られたので、調べてみました。
参考にしたのは information literacy meaning でググってヒットした CILIP(英国図書館情報専門家協会)の記事です。
以下、下手っぴ翻訳で読んだ内容を書いていきます。
曰く、情報リテラシーの定義は以下のようです。
Information literacy is knowing when and why you need information, where to find it, and how to evaluate, use and communicate it in an ethical manner.
自分の中で翻訳したのが以下(間違ってるかも)
情報リテラシーとは、いつ・なぜ情報が必要か、どこで見つけるか、どうやって倫理的な方法で評価・使用・伝達するか、を知る事です。
続けて情報リテラシーのわかる人になるための必要な理解についても記載されています。
以下の項目への理解が必要だそうです。 ・情報の必要性 ・使用できる資源 ・情報の見つけ方 ・結果を評価する必要性 ・結果を利用する方法 ・使用における倫理や責任 ・発見したものを伝達・共有する方法 ・発見したものを管理する方法
これらの項目の詳細は「Related Documents」のPDFファイルに記載されています。
https://www.cilip.org.uk/sites/default/files/documents/Information%20literacy%20skills.pdf
曖昧になっていた言葉の定義を調べる事である程度腑に落ちたかなと思いました。
SQL ServerのDBの全テーブルをエクスポート/インポートしちゃうバッチ。
開発機でDBを空にしたり戻したりめっちゃ面倒なので作ってみました。
バッチ
エクスポートするバッチ
ExportAllTables.bat
ソースコード
@echo off setlocal pushd %~dp0 :-------------------------------------------------------------------------------- : 概要:対象データベースの全テーブルをエクスポートする。 : 引数: : %1-対象のサーバー : %2-対象のデータベース名 : 特記事項:サーバーへのアクセスは信頼関係接続で行う。 :-------------------------------------------------------------------------------- :-------------------------------------------------------------------------------- : 変数 :-------------------------------------------------------------------------------- : Powershellで日付の文字列[yyyyMMdd_HHmmss]取得 powershell -Command "echo (Get-Date).ToString('yyyyMMdd_HHmmss')" > %temp%\dayvalue set /p ymd=< %temp%\dayvalue : DB設定 set Server=%~1 set DB=%~2 : 引数チェック if "%Server%"=="" ( exit /b 1 ) if "%DB%"=="" ( exit /b 2) : サーバー名の指定 [\\SERVER\INSTANCE] 形式でフォルダ名にできないので [SERVER.INSTANCE]形式に変更する powershell -Command "'%Server%'.Replace('\\','').Replace('\','.')" > %temp%\serv set /p ServerFolderName=< %temp%\serv : 保存先フォルダ(サーバー名とデータベース名で基底のフォルダを作る) set BaseDir=%ServerFolderName%-%DB%\ set SaveDir=%BaseDir%%ymd%\ : 対象DBのテーブル一覧 set TableList=TableList.txt :-------------------------------------------------------------------------------- : メイン処理 :-------------------------------------------------------------------------------- mkdir %BaseDir% mkdir %SaveDir% : 対象DBのテーブルのリストを取得 bcp "select name from %DB%.sys.tables" queryout %TableList% -S%Server% -T -c -t; > nul : 出力したテーブルのリストから1行ずつバックアップする。 for /f "delims=" %%i in (%TableList%) do ( call :ExportTable "%Server%" %DB% %%i "%SaveDir%" ) : テーブルリストを出力先に移動する。 : ※サーバーを(local)で指定された場合に、テーブルリストのパスがforコマンドで読めないパスになるため move %TableList% "%SaveDir%%TableListDst%" popd endlocal exit /b :-------------------------------------------------------------------------------- : テーブルをバックアップする。 :-------------------------------------------------------------------------------- :ExportTable setlocal set server=%~1 set db=%2 set table=%3 set saveDir=%~4 set filePath=%saveDir%%table%.txt bcp %db%.dbo.[%table%] out "%filePath%" -S%server% -T -c > nul endlocal exit /b
動作
対象のサーバーの対象のデータベースの各テーブルをBCPでエクスポートする。
実行日時のフォルダに格納されるのでその時点のデータベースをスナップショットできます。
BCPでエクスポートしているので、テキスト差分などでデータの相違を比較することも簡単になりました。
インポートするバッチ
ソースコード
CleanImport.bat
@echo off setlocal :-------------------------------------------------------------------------------- : 概要:ExportAllTables.batによりエクスポートされたフォルダから : 対象のデータベースにクリーンインポートする : 引数: : %1-対象のサーバー : %2-対象のデータベース名 : 特記事項:サーバーへのアクセスは信頼関係接続で行う。 :-------------------------------------------------------------------------------- :-------------------------------------------------------------------------------- : 変数 :-------------------------------------------------------------------------------- : DB設定 set Server=%~1 set DB=%~2 set tablesDir=%~3 : 引数チェック if "%Server%"=="" ( echo exit /b 1 ) if "%DB%"=="" ( exit /b 2 ) if not exist %tablesDir% ( exit /b 3 ) : 対象DBのテーブル一覧 set TableList=TableList.txt :-------------------------------------------------------------------------------- : メイン処理 :-------------------------------------------------------------------------------- set baseDir=%~dp0 pushd %tablesDir% if not exist %TableList% ( exit /b 4 ) : 出力したテーブルのリストから1行ずつクリーンインポートする。 pushd %tablesDir% for /f "delims=" %%i in (%TableList%) do ( call :CleanImportTable "%Server%" %DB% %%i ) popd endlocal exit /b 0 :CleanImportTable setlocal set server=%~1 set db=%~2 set table=%3 sqlcmd -S (local) -Q "truncate table %db%.dbo.[%table%]" bcp %db%.dbo.%table% in %table%.txt -T -S%server% -c endlocal exit /b pause
動作
指定のサーバーとデータベースに対して指定のフォルダの中のエクスポート済データをインポートする。
前述のエクスポートしたデータベースの状態にします。
TruncateしてBCPでインポートするだけ。割とテストとバグ再現などで活躍してます。
感想
BCP最高~
PowershellからVBScriptの連係
社内でエクセルファイルを取り扱う時VBScriptが実装楽で早い(Excelオブジェクトの開放など)時が多く、
それ以外についてはPowershellが実装が楽です。
連係を円滑に行うための流れを調べました。
連係のための関数・変数
Powershell
コマンドの実行
コマンドの実行は Invoke-Expression で行います。
参考URL: Windows PowerShell の機能
例
$cout = Invoke-Expression -Command "ping localghost"
コンソール出力が戻り値として取得できます(便利)
コマンドの終了コードの取得
Invoke-Expressionで実行したコマンドの結果は $lastExitCode で行います。
VBScript
コンソール出力
コンソール出力は WScript.Echo で行います。
参考URL: Echo メソッド
終了コードの設定
終了コードの設定は WScript.Quit で行います。
参考URL:Quit メソッド
連係例
$foo = Invoke-Expression -Command 'cscript //nologo "C:\ReadExcel.vbs" "Bar.xls"' if($lastExitCode -eq 0) { echo "成功しました。`n結果" + $foo } else { echo "失敗しました。" }
VBScript実装
WScript.Echo "コンソールに呼び元が欲しい情報を出力する。" ' 終了コード If isError Then WScript.Exit 1 Else WScript.Exit 0 End If
以上!
C# Zipまるごと解凍の上書き対応(ExtractToDirectory)
概要
Zipを対象のディレクトリにまるごと解凍する場合 ZipFileExtensions.ExtractToDirectory を使いますが、
このメソッド、上書き対応してないんです。
なので、上書き対応したメソッドを定義しました。
拡張クラスで追加したのは以下メソッド
ZipArchiveEntry.IsDirectory
解凍するエントリーがフォルダかどうか判定するメソッド。
ZipArchive.ExtractToDirectory
上書きフラグを指定して、対象のディレクトリにまるごと解凍するメソッド。
ソースコード
既存の拡張クラスと領域が同じ場合、
拡張クラス名をどうつければいいか判断つかなくなりますね。
ということでとりあえず「My◯◯」形式で命名・・・。(「◯◯Ex」形式の方がいいかな?)
/// <summary> /// ZIP拡張クラス。 /// </summary> public static class MyZipFileExtensions { /// <summary> /// エントリーがディレクトリかどうか取得する。 /// </summary> /// <param name="entry">ZIPアーカイブエントリー</param> /// <returns></returns> public static bool IsDirectory(this ZipArchiveEntry entry) { return string.IsNullOrEmpty(entry.Name); } /// <summary> /// ZIPアーカイブ内のすべてのファイルを特定のフォルダに解凍する。 /// </summary> /// <param name="source">ZIPアーカイブ</param> /// <param name="destinationDirectoryName">解凍先ディレクトリ。</param> /// <param name="overwrite">上書きフラグ。ファイルの上書きを行う場合はtrue。</param> public static void ExtractToDirectory(this ZipArchive source, string destinationDirectoryName, bool overwrite) { foreach (var entry in source.Entries) { var fullPath = Path.Combine(destinationDirectoryName, entry.FullName); if (entry.IsDirectory()) { if (!Directory.Exists(fullPath)) { Directory.CreateDirectory(fullPath); } } else { if (overwrite){ entry.ExtractToFile(fullPath, true); } else { if (!File.Exists(fullPath)) { entry.ExtractToFile(fullPath, true); } } } } } }
以上!
Powershellで古い形式のExcel(xls)を新しい形式(xlsx)に変換する。
概要
下記の記事で作ったものと似たものを簡潔に書いてみました。
実装
ソースコード
$ErrorActionPreference = "Stop" # 例外が出たらその時点で即終了 $srcDir = (Resolve-Path $args[0]).Path $dstDir = (Resolve-Path $args[1]).Path try{ # Excelオブジェクト作成 $excel = New-Object -ComObject Excel.Application $excel.Visible = $false $excel.DisplayAlerts = $false Get-ChildItem -Path $srcDir -Filter "*.xls" | % { $dstPath = Join-Path $dstDir $($_.BaseName + ".xlsx") if(-not (Test-Path -Path $dstPath)) { $book = $excel.Workbooks.Open($_.FullName) $book.SaveAs($dstPath, 51) $book.Close() } else { } } } finally { $excel.Quit() }
呼び出し方(Xls2Xlsx.ps1)
# 相対パス、絶対パス両方OKですが存在しないパスはできません。
Xls2Xlsx.ps1 .\dst .\src
今回試したこと
- Try-Catch-Finally
- Resolve-Pathによる相対パスの解決
- Join-Pathによるパスの結合 (今まで[System.IO.Path]::Combineを呼んでいた…)
- $ErrorActionPreference
感想
- Resolve-Pathが存在しないパスだと例外出るので便利
- PowerShellだとXlFileFormatに名前でアクセスできるから実装が楽で可読性も上がる
- コマンド形式で書き直してみたい(そこらへん無知)
以上
エクセル内に定義された名前の重複を解決するマクロ
やりたいこと
以下ダイアログメッセージの連発を叩きのめす。
移動またはコピーしようとしている数式またはシートには、移動またはコピー先のワークシートに既にある名前 'foo' が含まれています。 この名前を使用しますか?
- コピーまたは移動先のシートに定義されている名前を使用する場合は、[はい]をクリックします。
- 数式またはワークシートで参照する範囲の名前を変更する場合は、[いいえ]をクリックし、[名前の重複] ダイアログボックスに新しい名前を入力します。
先にデメリット
すべて消えます。
この方法は定義された名前をすべて削除します。
そのため、セルに名前を付けているとすべて消えます。
対象としてはまっさらにする時の用途なのでご注意ください。
たまに復活します。
古い形式(.xls)など、削除したはずが謎のタイミングで復活する場合もあります。
そうなると諦めて新しいファイルに書式などを丁寧にコピーするしかないです多分。
やること
開発」タブを表示するようにする。
(ファイル→オプション→リボンのユーザー設定)
「開発」タブの「マクロの記録」をクリック、OKをで初期表示の名前で記録開始。
「開発」タブの「記録終了」をクリック
「開発」タブの「マクロ」をクリック
マクロの記録で付けた名前を選択して、「編集(E)」をクリック
以下のような状態になっていると思います。
Option Explicit Sub Macro1() ' ' Macro1 Macro ' ' End Sub
全部消して以下をコピペする
Option Explicit Sub Macro1() ' ' Macro1 Macro ' Dim n As Name Dim s As Worksheet ' ブックの範囲の名前を削除 For Each n In ThisWorkbook.Names n.Delete Next ' シートの範囲の名前を削除 For Each s In ThisWorkbook.Worksheets For Each n In s.Names n.Delete Next Next msgbox "あいつはもう消した!" ' End Sub
F5キーを押す
メッセージが出たら終わり。
以上!