Finding and adding data in Excel via Powershell

Question

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.

What have you tried so far? Did you import the csv file into Excel? What code have you tied? — John Muggins
– John Muggins, Commented May 24, 2017 at 19:11
Yes, I can make it work in excel but need to make it work in powershell as our suppliers are submitting us purchase orders per day in csv format. It would be very beneficial to have a script that can add up the quantities of the products in bulk for our packaging. — redwing88
– redwing88, Commented May 24, 2017 at 19:21
Please add your code by editing your question, so that is properly formatted — BenH
– BenH, Commented May 24, 2017 at 19:29
A .csv file is just a comma delimited ascii text file. By the data above I assume it is two pieces of data per row in the csv file. Can you edit your question to show the first few lines of one of the csv files? Make sure the top line is present. — John Muggins
– John Muggins, Commented May 24, 2017 at 19:40

BenH · Accepted Answer · 2017-05-24 19:58:26Z

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
}

Collectives™ on Stack Overflow

Finding and adding data in Excel via Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related