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 — Pac0
– Pac0, Commented Mar 21, 2018 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
– Pac0, Commented Mar 21, 2018 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
– Dave, Commented Mar 21, 2018 at 15:37

henrycarteruk · Accepted Answer · 2018-03-21 15:57:53Z

2

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

answered Mar 21, 2018 at 15:50

henrycarteruk

13.3k2 gold badges40 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1