2017/02/05

[PowerShell]固定長ファイルを簡単に1行ずつ読み込みExcelにインポートする


CSV ファイルを読み込むというのもなかなかやっかいなものですが、固定長ファイルはやっかいではないものの自分で区切り位置を指定しなければならず面倒なものです。

ですが、.NET Framework の Microsoft.VisualBasic.FileIO.TextFieldParser クラスを利用するとお手軽に固定長ファイルを読み込むことができ、Excel ファイルにインポートすることもできます。

まず、以下の固定長のデータが UTF-8 (ASCII) で users.txt で保存されているものとします。漢字がないので、UTF-8 でも ASCII でもどちらでもよいのですけどね。 

fixed01

サンプルスクリプトを実行した結果は以下のようになります。問題なく読み込めていますね。 

fixed02

 

では、サンプルスクリプトを以下に示します。 

#
# UTF-8の固定長ファイルをExcelにインポートするスクリプト
#

# アセンブリをロードする
[void][reflection.assembly]::LoadWithPartialName("Microsoft.VisualBasic")

# スクリプトの親フォルダのパスを取得する
$path = Split-Path $MyInvocation.MyCommand.Path -Parent

# テキストファイルのパスを作成する
$txtPath = Join-Path $path "users.txt"

# Excel の保存先のパスを作成する
$xlsPath = Join-Path $path "users.xlsx"

# テキストファイルのエンコーディングを指定する
$enc = [System.Text.Encoding]::UTF8

# パーサーでテキストファイルを開く
$parser = New-Object -TypeName Microsoft.VisualBasic.FileIO.TextFieldParser $txtPath, $enc

# テキストファイルが固定長であることを指定する
$parser.TextFieldType = [Microsoft.VisualBasic.FileIO.FieldType]::FixedWidth

# テキストファイルの区切り位置を指定する(最後に-1を指定すると最後だけ可変長になる)
$parser.SetFieldWidths(5, 15, 2)


# Excel を起動する
$xls = New-Object -ComObject Excel.Application

# WorkBook を追加する
$wb = $xls.WorkBooks.Add()

# シートを選択する
$ws = $wb.WorkSheets.Item("Sheet1")

# 変数を初期化する
$i = 1
$j = 1

# 最終レコードまで読み込む
While($parser.EndOfData -eq $false) {
  # テキストを区切り配列に格納する
  $fields = $parser.ReadFields()

  # 配列を順番に処理する
  foreach ($field in $fields) {

    # セルの書式を「文字列」にする
    $ws.Cells.Item($i, $j).NumberFormat = "@"

   # セルに値を設定する
   $ws.Cells.Item($i, $j).Value = $field

   # 列を1つ進める
   $j++
  }

  # 行を1つ進める
  $i++

  # 変数初期化
  $j = 1
}

# ファイルが既存の場合警告メッセージを表示しないようにする
$xls.DisplayAlerts = $false

# Excel ファイルを保存する
$wb.SaveAs([ref]$xlsPath.ToString())
#$wb.SaveAs([ref]$xlsPath.ToString(), -4143) # xls形式の時はこちらを使用する

# 警告メッセージの表示を元に戻す
$xls.DisplayAlerts = $true

# WorkBook を閉じる
$wb.Close()

# Excel を終了する
$xls.Quit()

# テキストファイルを閉じる
$parser.Close()

# COM 参照を解放する
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($ws)
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($wb)
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($xls)


スポンサーリンク


このエントリーをはてなブックマークに追加




Twitter ではブログにはない、いろんな情報を発信しています。


コメント

コメントを書く



プロフィール

  • 名前:fnya
    経歴:
    SE としての経験は15年以上。様々な言語と環境で業務系システム開発を行い、セキュリティ対策などもしていました。現在は趣味SE。

    Twitter では、ブログでは取り上げない情報も公開しています。


    ブログについて

    このブログは、IT、スマートフォン、タブレット、システム開発などに関するさまざまな話題を取り上げたり、雑感などをつづっています。


    >>ブログ詳細
    >>自作ツール
    >>運営サイト
    >>Windows 10 まとめ

    Twitter のフォローはこちらから Facebook ページはこちら Google+ページはこちら RSSフィードのご登録はこちらから