Copy data from excel to outlook using powershell

Question

I tried the following code but it pastes some garbage text while copying data from excel to outlook. Using powershell. Code used:

$body=""
get-content "C:\Users\smi00019\Desktop\AO\Book1.xlsx" | foreach{$body+="$_`n"}

Excel data:

Name    Place   Animal
ABC     Mumbai  Dog
XYZ     Pune    Cat

I am trying to copy above data range A1:c3

Possible duplicate of [Read Excel data with Powershell and write to a variable](https://stackoverflow.com/questions/16615364/read-excel-data-with-powershell-and-write-to-a-variable) — Pac0, Mar 21 '18 at 15:15
you are not reading "garbage data", you are trying to get what's in the file, which is more than simply the values in the cells. — Pac0, Mar 21 '18 at 15:16
You need the link Pac0 posted or save the file as csv rather than xlsx and use Import-Csv to bring the data into Powershell. — Dave, Mar 21 '18 at 15:37

henrycarteruk · Answer 1 · 2018-03-21T15:57:53.877

Get-Content is for use with text-based files. Excel files are not text based and contain other elements (formatting, formulas, macros, graphs etc)

I would recommend using the PSExcel Module as it contains the Import-XLSX function which makes working with Excel files very easy.

Import-XLSX works like Import-CSV, and generates an 'array' object from the file.

Excel:

$Imported = Import-XLSX -Path C:\Temp\Demo.xlsx -Header samaccountname, EID, Date

PS Object:

You can then use Select-Object to get the Property (column names) you want and that you only the First two entries (rows).

$Imported | Select-Object -Property Column1,Column1,Column1 -First 2

1 Answers1