CODEWITHSUNDEEP
excelpowershellvba
redwing88
redwing88

Reputation: 11

Finding and adding data in Excel via Powershell

I have a CSV file that has similar products within it and quantities of each product beside it.

Sample from CSV file

Qty Ordered         Product/Item Description    Top row (header)
   7                Product1
   3                Product2
   5                Product1
   3                Product3

I need a method to find all the similar product#s, add up their Quantities, and place the total of each similar product in a new row.

Add-Type -AssemblyName System.Windows.Forms
$FileBrowser = New-Object System.Windows.Forms.OpenFileDialog -Property 
@{
Multiselect = $false # Multiple files can be chosen
Filter = 'Excel (*.csv, *.xlxs)|*.csv;*.xlsx' # Specified file types
}

[void]$FileBrowser.ShowDialog()

$file = $FileBrowser.FileNames;




[Reflection.Assembly]::LoadWithPartialName
("Microsoft.Office.Interop.Excel")|Out-Null
$excel = New-Object Microsoft.Office.Interop.Excel.ApplicationClass
$excel.Visible = $true 
$wb = $excel.Workbooks.Open($file)
$ws = $wb.ActiveSheet
$c = $ws.Columns
$c.Item(2).hidden = $true

This code, asks the user to select the csv file, hides useless columns and auto-sizes the important columns as well.

Upvotes: 0

Views: 49

Answers (1)

BenH
BenH

Reputation: 10034

Rather than using Excel as a COM Object you could use Import-CSV and then Group-Object. Then loop through the groups for the information you need.

Add-Type -AssemblyName System.Windows.Forms 
$FileBrowser = New-Object System.Windows.Forms.OpenFileDialog -Property @{
    Multiselect = $false # Multiple files can be chosen 
    Filter = 'Excel (.csv, *.xlxs)|.csv;*.xlsx' # Specified file types 
} 
[void]$FileBrowser.ShowDialog() 
ForEach ($file in $FileBrowser.FileNames) {
    $CSV = Import-CSV $file | Add-Member -Name Total -Value 0 -MemberType NoteProperty
    $Groups = $CSV | Group-Object "Product/Item Description"
    $NewCSV = Foreach ($Group in $Groups) {
        $Count = 0
        $Group.Group."Qty Ordered" | ForEach-Object {$Count += $_}
        Foreach ($value in $CSV) {
            If ($value."Product/Item Description" -eq $Group.Name) {
                 $value.Total = $Count
                 $value
            }
        }
    }
    Export-CSV "$filenew" -NoTypeInformation
}

Upvotes: 1

Related Questions