Extract data from excel using powershell

Question

sample excel image I need export data from a column in excel and exclude some characters then export to a txt file.(excel Sample attached). Basically I need to extract ONLY names in the Orders column and output to a text file, here is what I have so far:

#Specify the Sheet name
$SheetName = "Today Order"

# Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false
# Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)
# Load the WorkSheet 'Change Auditor Events'
$WorkSheet = $WorkBook.sheets.item($SheetName)

#====

I can use the replace command below to trim off unneeded characters in the Orders column, I only need the names -replace "order from " -replace " California" How can I assign variable to the orders column and process each line then use the out-file to export? Or do you have any other good suggestion to do this.

Thanks in Advance.

Do you know what column that is? For example, is it always the first column, or the fourth or something? — TheMadTechnician
– TheMadTechnician, Commented Sep 25, 2020 at 22:30

OwlsSleeping · Accepted Answer · 2020-09-25 23:57:29Z

I assumed your data is in column A. Correct as needed.

I used regex to pull the name out from your sentence. -Match writes to the magic variable "$matches"

It's worth mentioning that using COM objects is the "hard" way to do this. The very easy way is saving as csv. The easy way is using a module that handles .xlsx files.

#Specify the Sheet name
$SheetName = "Today Order"
$FilePath = "C:\whatever.xlsx"

# Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false
# Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)
# Load the WorkSheet 'Change Auditor Events'
$WorkSheet = $WorkBook.sheets.item($SheetName)

$MyData = [System.Collections.Generic.List[string]]::new() #Potentially better performance than immutable array if you're running this on thousands of rows.
for($i = 2; $i -le $WorkSheet.UsedRange.Rows.Count; $i++) {
    ($Worksheet.Range("a$i").text) -match 'from (?<name>.+) in'
    $MyData.Add($Matches.name)
}

$MyData | Out-File "C:\output.txt"

Collectives™ on Stack Overflow

Extract data from excel using 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