Automating Excel and CSV Work with PowerShell — Practical Recipes for Aggregation, Reconciliation, and Report Output

“Every month, we open the CSV downloaded from the core system in Excel, aggregate it with a pivot table, tidy the layout, and email it.” “We cross-check the name lists produced by two systems and hunt for differences by eye.” — I get asked about this kind of routine work a lot. Even at 30 minutes a time, once it is every week, every month, and across several people, a considerable amount of time dissolves over a year — and manual copy-and-paste always mixes in accidents.

PowerShell is a good fit for this area. It ships with Windows (Windows PowerShell 5.1), it reads CSV as a “table of objects”, and it lets you chain aggregation, reconciliation, and output together in a pipeline. On top of that, with the community-built ImportExcel module you can produce xlsx reports without Excel itself.

At the same time, this field has landmines specific to Japanese-language environments. The default character encodings are completely different between Windows PowerShell 5.1 and PowerShell 7, which is why “it worked on my PC but came out garbled elsewhere” happens so often. This article organises the practical recipes for automating CSV and Excel work with PowerShell — for IT staff and business users at small and mid-sized companies — starting with the encoding pitfalls.

1. The Bottom Line First

• Always state -Encoding explicitly when reading and writing CSV. Windows PowerShell 5.1 has different default encodings per cmdlet, whereas PowerShell 7 uses UTF-8 without BOM uniformly — so the defaults of the two are completely different.¹
• The default for Export-Csv in 5.1 is ASCII. Forget -Encoding and Japanese text is lost at the moment of saving. Also, Import-Csv in 5.1 interprets a file without a BOM as UTF-8, so reading a Shift_JIS CSV as-is produces garbage.¹
• The basic form of aggregation is the combination of Group-Object (grouping) and Measure-Object (sum, average, max/min). Much of what you do with an Excel pivot table every time can be replaced by these two.²³
• To reconcile two CSVs, use Compare-Object if you only need to know whether there are differences, and a hash table if you need to look up columns (join). Compare-Object shows you which side a row exists on via the SideIndicator.⁴
• For reading and writing xlsx, the ImportExcel module (community-built) is the first candidate. No installation of Excel itself is required, and it can create tables, formatting, and pivot tables.⁵
• Driving Excel itself through COM is a last resort. Failing to release references (RCWs) easily leaves EXCEL.EXE behind,⁶⁷ and Microsoft neither recommends nor supports Office automation in unattended environments (services, scheduled execution) in the first place.⁸
• Before putting a script on Task Scheduler, explicitly pin down three things: encoding, paths, and the execution environment. These three account for the majority of cases where a script that worked interactively breaks under scheduled execution.¹⁸

2. The Basics of Import-Csv/Export-Csv — the Biggest Landmine Is Encoding

Import-Csv reads a CSV as a table where “one row = one object, one column = one property”. The header row becomes the column names, and everything downstream can be written in terms of property names.⁹ Export-Csv does the reverse, writing an object’s columns out to CSV.¹⁰ So far, so easy. The problem is character encoding.

As the official documentation states explicitly, the default encodings in Windows PowerShell 5.1 are not consistent across cmdlets.¹ Here is a table of the range that matters in practice for Japanese-language environments.

In other words, in 5.1 the situations “it read fine as Shift_JIS with Get-Content but garbles with Import-Csv” and “after Export-Csv all the Japanese turned into ?” both happen exactly as specified. PowerShell 7 is consistent with UTF-8 without BOM throughout,¹ but now a Shift_JIS CSV arriving from the core system garbles if you read it with the defaults. There is only one conclusion: state -Encoding explicitly on both reads and writes.

# Read the Shift_JIS CSV produced by the core system
# Windows PowerShell 5.1: Default = the system ANSI code page (Shift_JIS in a Japanese environment)
$orders = Import-Csv -LiteralPath 'C:\data\orders.csv' -Encoding Default

# In PowerShell 7 you can specify it by code page number (932 = Shift_JIS)
# $orders = Import-Csv -LiteralPath 'C:\data\orders.csv' -Encoding 932

# Writing output as UTF-8 with BOM makes it less likely to garble when opened in Excel by double-click
# PowerShell 7: UTF8 means no BOM, so state utf8BOM explicitly when you want a BOM
$orders | Export-Csv -LiteralPath 'C:\data\orders_out.csv' -NoTypeInformation -Encoding utf8BOM
# Windows PowerShell 5.1: there is no utf8BOM value. Specifying UTF8 gives you a BOM
# $orders | Export-Csv -LiteralPath 'C:\data\orders_out.csv' -NoTypeInformation -Encoding UTF8

From PowerShell 6.2 onwards, -Encoding also accepts code page numbers (932) and registered names, and from 7.4 onwards the value ansi is available too.¹ Note that -NoTypeInformation exists to suppress the #TYPE line that 5.1 puts at the top; from PowerShell 6 onwards it is no longer emitted by default, so the parameter is unnecessary (specifying it does not cause an error).¹⁰ For scripts that must run under both 5.1 and 7, it is safest to include it.

The traps of the CSV format itself (leading zeros disappearing when opened in Excel, values containing commas or line breaks, injection countermeasures) are covered in detail in “CSV Is Not ‘Just Text’”. For the fundamentals of character encodings and line endings, see “Windows Text Encodings and Line Endings”.

3. Aggregation — Building the Pivot-Table Equivalent with Group-Object and Measure-Object

For aggregations such as “count and total amount per department”, the basic form is to group with Group-Object and total each group with Measure-Object.²³

# We are reading a Shift_JIS CSV in PowerShell 7, so state code page 932 explicitly
# (-Encoding Default in 7 means UTF-8 and will garble. Specify Default if running on 5.1)
$orders = Import-Csv -LiteralPath 'C:\data\orders.csv' -Encoding 932

# Produce the count and total amount per department.
# All values from Import-Csv are strings, so the key point is to convert to [decimal] before totalling
# (the Sum from Measure-Object -Sum is a Double, so keep amounts as decimal and add them up yourself
#  to avoid losing precision on large totals or fractional values)
$summary = $orders | Group-Object -Property Dept | ForEach-Object {
    $total = [decimal]0
    foreach ($row in $_.Group) { $total += [decimal]$row.Amount }
    [pscustomobject]@{
        Dept  = $_.Name    # value of the grouping key
        Count = $_.Count   # number of rows
        Total = $total     # total amount
    }
}

$summary | Sort-Object -Property Total -Descending |
    Export-Csv -LiteralPath 'C:\data\summary.csv' -NoTypeInformation -Encoding UTF8

The easy thing to trip over is that every value in a CSV is a string. What Import-Csv returns is a collection of string properties,⁹ so convert explicitly to [decimal] before passing something you think of as a number to Measure-Object -Sum. Forgetting the conversion can still appear to work in 5.1, which tends to produce the kind of accident where you notice only after the digits of an amount have shifted.

Besides -Sum, Measure-Object can produce -Average, -Maximum, and -Minimum at the same time.³ And Group-Object -AsHashTable gives you a hash table of “key → array of rows in that group” directly, which can also be applied to the reconciliation described below.² Where these one-off commands come in handy is summarised in “Practical PowerShell Command Recipes”.

4. Reconciliation — Choosing Between Compare-Object and a Hash-Table Join

4.1. Compare-Object if You Only Need to Know Whether There Are Differences

The classic reconciliation of “who was added and who was removed” between yesterday’s and today’s name-list CSVs is quickest with Compare-Object. Specify the key column with -Property and it compares on that column’s values alone; the resulting SideIndicator tells you whether a row is => (present only on the difference side) or <= (present only on the reference side).⁴

$yesterday = Import-Csv -LiteralPath '.\users_0716.csv' -Encoding UTF8
$today     = Import-Csv -LiteralPath '.\users_0717.csv' -Encoding UTF8

# Compare on employee number alone. => is a row only in today (added), <= is a row only in yesterday (removed)
Compare-Object -ReferenceObject $yesterday -DifferenceObject $today -Property EmpNo |
    Sort-Object -Property EmpNo |
    Format-Table -Property EmpNo, SideIndicator

There are two things to watch. First, specifying -Property leaves only that column and the SideIndicator in the result, so if you also want to see other columns such as the person’s name, you have to look the original data up again by the key in the result. Second, if either the reference side or the difference side is $null (not zero rows, but null), it stops with an error.⁴ In operations where an empty file is possible, wrapping the load result with @() to force an array is the safe move.

4.2. A Hash Table if You Also Need to Look Up Columns (Join)

The SQL JOIN equivalent — “look up name and department from the master CSV for the employee number in the detail CSV” — is conventionally done by turning the master side into a key → row hash table and then looking up one row at a time. A double loop (every detail row against every master row) becomes visibly slow at a few thousand by a few thousand, whereas a hash table runs at practical speed even at tens of thousands of records.

# Turn the master side into an "employee number -> row" hash table
# Duplicate keys are overwritten by the later row, so check in advance if duplicates are possible
$master = @{}
foreach ($row in (Import-Csv -LiteralPath '.\master.csv' -Encoding UTF8)) {
    $master[$row.EmpNo] = $row
}

# Look up each detail row in turn. Do not swallow rows that are not found - set them aside in a separate file
$unmatched = New-Object System.Collections.Generic.List[object]
$joined = foreach ($row in (Import-Csv -LiteralPath '.\details.csv' -Encoding UTF8)) {
    $hit = $master[$row.EmpNo]
    if ($null -eq $hit) {
        $unmatched.Add($row)
        continue
    }
    [pscustomobject]@{
        EmpNo  = $row.EmpNo
        Name   = $hit.Name
        Dept   = $hit.Dept
        Amount = $row.Amount
    }
}

$joined    | Export-Csv -LiteralPath '.\joined.csv'    -NoTypeInformation -Encoding UTF8
$unmatched | Export-Csv -LiteralPath '.\unmatched.csv' -NoTypeInformation -Encoding UTF8

The heart of this recipe is the last two lines. Do not silently discard rows whose key could not be resolved. The value of reconciliation work lies in “a person being able to check the things that did not match”, so always output the unmatched set and write the count to the log or standard output.

4.3. Variation in Column Names, and Handling Headers

CSVs in the field tend to drift in their column names — 社員番号, 社員No, emp_no. The remedy comes down to normalising to internal names immediately after loading. If everything downstream is written in terms of internal names only, then when the format changes there is just one place — the normalisation — to fix.

# For a CSV with no header row, supply column names with -Header (row 1 is then read as data)
# Encoding follows the same thinking as Section 2: for Shift_JIS, specify 932 in 7 and Default in 5.1
$rows = Import-Csv -LiteralPath '.\no_header.csv' -Header 'EmpNo', 'Name', 'Dept' -Encoding 932

# For a CSV with Japanese headers, normalise to ASCII internal names immediately after loading
$normalized = Import-Csv -LiteralPath '.\jinji.csv' -Encoding 932 |
    Select-Object -Property @{ Name = 'EmpNo'; Expression = { $_.'社員番号' } },
                            @{ Name = 'Name';  Expression = { $_.'氏名' } },
                            @{ Name = 'Dept';  Expression = { $_.'所属部署' } }

-Header is for files with no header row; note that specifying it means row 1 is also read as data.⁹ Also, when a header cell is blank, PowerShell automatically assigns a provisional column name such as H1,⁹ so if you cannot get at a property under the column name you expected, suspect the header row first.

5. Working with xlsx — ImportExcel Is the First Candidate, COM Is the Last Resort

5.1. The ImportExcel Module — Reading and Writing xlsx Without Excel Itself

Japanese workplaces being what they are, aggregated results are asked for “as an Excel file, not a CSV, with the table coloured in”. The first candidate here is the community-built ImportExcel module published on the PowerShell Gallery. It reads and writes xlsx with no installation of Excel itself required, and can create tables, adjust column widths, and even build pivot tables.⁵

# First time only. Install from the PowerShell Gallery for the current user
Install-Module -Name ImportExcel -Scope CurrentUser

# Read an xlsx (it feels just like Import-Csv - the sheet's table becomes an array of objects)
$budget = Import-Excel -Path 'C:\data\budget.xlsx' -WorksheetName 'Budget'

# Output the aggregation from Section 3 as an xlsx with a table, auto-fitted columns, and a pivot table
$summary | Export-Excel -Path 'C:\data\monthly-report.xlsx' `
    -WorksheetName 'Summary' -TableName 'Summary' -AutoSize `
    -IncludePivotTable -PivotRows Dept -PivotData @{ Total = 'Sum' }

Since it is community-built, check it against your organisation’s software adoption rules when introducing it (the module is obtained from the PowerShell Gallery⁵). Even so, compared with an arrangement of “install Excel on the server and drive it through COM”, it is the sounder choice on every front: licensing, stability, and maintenance. A comparison of report-generation approaches (COM/Open XML/templates) is set out in “How to Build Excel Report Output”.

5.2. Excel COM Automation — If You Use It, Write the Release Code Through to the End, and Never Run It Unattended

You want to kick off a macro in an existing xls; you need Excel’s own features (recalculation, printing, resolving defined names). Only in cases like those do you drive Excel itself through COM. From PowerShell you can start it with New-Object -ComObject, but the famous problem is EXCEL.EXE processes being left behind. If references to COM objects (RCWs) are not released, the process remains even after you call Quit().⁶ The .NET-side remedy is explicit release with Marshal.ReleaseComObject.⁷

# COM is a last resort. Guarantee "always close what you opened, always release references" in finally
# Do all COM calls after starting Excel inside the try (so cleanup runs even if the very first line throws)
$excel = New-Object -ComObject Excel.Application
$book = $null
try {
    $excel.Visible = $false
    $excel.DisplayAlerts = $false   # stop confirmation dialogs from blocking us
    $book  = $excel.Workbooks.Open('C:\data\template.xlsx')
    $sheet = $book.Worksheets.Item(1)
    $sheet.Range('B2').Value2 = 12345
    $book.SaveAs('C:\data\output.xlsx')
    $book.Close($false)
}
finally {
    # Even if Quit fails (Excel not responding, say), always run the release and the GC
    try {
        $excel.Quit()
    }
    finally {
        # COM objects you touched will readily leave EXCEL.EXE behind unless their RCWs are released explicitly
        if ($sheet) { [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet) }
        if ($book)  { [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($book) }
        [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
        [GC]::Collect()
        [GC]::WaitForPendingFinalizers()
    }
}

The awkward part is that simply chaining dots, as in $excel.Workbooks.Open(...), creates a reference to an intermediate object (the Workbooks collection in this example) — and that too must be released. Strictly speaking, the code above still holds a reference to Workbooks; to be certain, you would receive intermediate objects into variables and release them as well. This very “difficulty of writing reference management through to the end” is the reason for positioning COM as a last resort. The mechanism in detail and the decision about replacing it are covered in “Why EXCEL.EXE Processes Remain After C# Excel COM Automation”, and COM/.NET calls from PowerShell in general are covered in the simultaneously published “Calling COM and .NET from PowerShell in Practice”.

And there is one more decisive constraint. Microsoft neither recommends nor supports automating Office from unattended, non-interactive clients (services, scheduled execution, and so on). Office is designed on the assumption that an interactive user is present, and the documentation states explicitly that in unattended environments it can behave unstably or deadlock.⁸ The SSIS documentation likewise recommends replacing Excel connections with CSV or Open XML-based approaches in unattended execution environments.¹¹ An arrangement of “open Excel every night from Task Scheduler and build the report” is a castle built on sand, even if it appears to work. Move anything you want to run on a schedule to ImportExcel or an Open XML-based approach.

6. A Checklist Before Putting It on Task Scheduler

Put a script that worked on your machine straight onto Task Scheduler and it will usually break in one of the following ways.

• Character encoding: behaviour does not differ between interactive and scheduled execution, but a common accident is a script that “happened to work under 7 on your machine” garbling because the task’s start command was powershell.exe (= 5.1) and the default encoding therefore changed. If you state -Encoding explicitly on both reads and writes as in Section 2, the result is the same whichever starts it.¹
• Paths: do not use relative paths that depend on the current directory; write absolute paths, or paths relative to $PSScriptRoot. Network drives (Z: and the like) are not visible in a scheduled-execution session, so use UNC paths.
• Execution environment: state explicitly in the start command which PowerShell you are running (powershell.exe or pwsh.exe). For the differences between 5.1 and 7 and how to choose, see the simultaneously published “The Differences Between Windows PowerShell 5.1 and PowerShell 7”.
• Do not put anything involving Excel COM on it: as in the previous section, unattended execution is unsupported.⁸

How to keep logs and audit trails when running under Task Scheduler is written up in detail in “Applied PowerShell Scripting — Safely Automating Log Investigation, Archiving, and Reporting”.

7. Rules of Thumb in Practice (Decision Table)

8. Summary

• The biggest landmine in CSV automation is character encoding. In 5.1 the defaults vary by cmdlet (Export-Csv is ASCII); in 7 it is uniformly UTF-8 without BOM. State -Encoding explicitly on both reads and writes and the environment differences disappear.
• The basic form of aggregation is Group-Object + Measure-Object. Since every CSV value is a string, convert to a number before aggregating.
• For reconciliation, use Compare-Object if you only need whether there are differences, and a hash table if you need a join. Always write rows that could not be resolved to a separate file so a person can check them.
• Absorb variation in column names by normalising immediately after loading, and write everything downstream in terms of internal names only.
• xlsx can be read and written with the ImportExcel module without Excel itself. COM is a last resort for when you can write the release code through to the end, and it is not for unattended execution.
• Before putting anything on Task Scheduler, explicitly pin down the encoding, the paths, and which PowerShell starts it.

Related Articles

• CSV Is Not “Just Text” — CSV in Practice for C# Business Applications (Encodings, Excel Compatibility, Injection Countermeasures)
• Practical PowerShell Command Recipes — Growing the Small Tools You Use Every Day
• How to Build Excel Report Output - COM / Open XML / Templates
• Why EXCEL.EXE Processes Remain After C# Excel COM Automation — Reference Release Patterns and the Replacement Decision
• Windows Text Encodings and Line Endings - The Basics of Mojibake and CRLF/LF
• The Differences Between Windows PowerShell 5.1 and PowerShell 7 — A Practical Guide to Migrating In-House Scripts

Related Consulting Areas

KomuraSoft LLC handles the writing of automation scripts for routine work that goes through CSV and Excel, moving away from report processing that depends on Excel COM (replacing it with ImportExcel/Open XML), and investigating faults such as “it only garbles in one particular environment” or “EXCEL.EXE stays behind”.

• Windows App Development
• Technical Consulting & Design Review
• Legacy Asset Reuse & Migration Support
• Contact Us

References

Related Articles

Recent articles sharing the same tags. Deepen your understanding with closely related topics.

Related Topics

These topic pages place the article in a broader service and decision context.

Where This Topic Connects

This article connects naturally to the following service pages.

Frequently Asked Questions

Common questions about the topic of this article.

Why does text get garbled when I read and write CSVs with PowerShell?

Because Windows PowerShell 5.1 and PowerShell 7 use different default character encodings. In 5.1 the defaults vary from cmdlet to cmdlet: Export-Csv uses ASCII (which destroys Japanese text), Import-Csv interprets a file without a BOM as UTF-8, and Get-Content uses ANSI (Shift_JIS in a Japanese environment). In 7, the default is uniformly UTF-8 without BOM. The only safe measure is to state -Encoding explicitly on both reads and writes.

How do I reconcile (diff) two CSVs?

If you only want to know whether there are differences, the quickest route is Compare-Object with -Property naming the key column. The SideIndicator tells you which rows were added (=>) and which were removed (<=). If you need to look up columns such as name or department from the other CSV and join them, the standard approach is to turn the master side into a key-to-row hash table and then look up each row one at a time; this handles tens of thousands of records quickly. Do not swallow rows whose key is not found — write them to a separate file so a person can check them.

Do I need Excel itself to create an Excel file (xlsx) from PowerShell?

No. With the community-built ImportExcel module you can read and write xlsx, create tables, apply formatting, and even build pivot tables on a machine with no Excel installed. You install it from the PowerShell Gallery with Install-Module. It is safest to treat driving Excel itself through COM as a last resort, for cases where you genuinely need "Excel's own features", such as running macros.

Why does EXCEL.EXE stay running after I automate Excel through COM from PowerShell?

Because as long as references to COM objects (RCWs) remain unreleased, the Excel process does not exit even when you call Quit. Every time you touch a workbook or a cell range, references to intermediate objects accumulate, so release them explicitly with Marshal.ReleaseComObject when you have finished, and prompt collection with GC.Collect. Also, Microsoft neither recommends nor supports Office automation in unattended environments such as Task Scheduler, so anything you want to run on a schedule should be moved to an approach that does not need Excel itself, such as ImportExcel.

Should I aggregate CSVs with an Excel pivot table or with PowerShell?

For a one-off analysis, Excel is fine. If you repeat the same steps every week or every month, it is worth scripting it with Group-Object and Measure-Object. The procedure then lives as code rather than as a document, so the same results can be reproduced when the person responsible changes, and it can be hooked up to scheduled execution in Task Scheduler. If you output the aggregated results to xlsx with ImportExcel, the recipient can still treat it as the usual Excel file.

Author Profile

Profile page for the article author.

Go Komura

Representative of KomuraSoft LLC

Focused on Windows software development, technical consulting, and investigations into failures that are difficult to reproduce.