James O'Neill's Blog

December 5, 2017

Using the Import-Excel module: Part 1 Importing

Filed under: Office,Powershell — jamesone111 @ 9:15 am

The “EEPLus” project provides .NET classes to read and write XLSx files without the need to use the Excel object model or even have Excel installed on the computer (XLSx files, like the other  Office Open XML Format are actually .ZIP format files, containing XML files describing different aspects of the document – they were designed to make that sort of thing easier than the “binary” formats which went before.)   Doug Finke, who is well known in the PowerShell community, used EEPlus to build a PowerShell module named ImportExcel which is on GitHub and can be downloaded from the PowerShell gallery (by running Install-Module ImportExcel on PowerShell 5 or PS4 with the Package Management addition installed) As of version 4.0.4 his module contains some of my contributions. This post is to act as an introduction to the Export parts of the module that I contributed to; there are some additional scripts bundled into the module which do require Excel itself but the core Import / Export functions do not. This gives a useful way to get data on a server into Excel format, or to provide users with a work book to enter data in an easy to use way and process that data on the server – without needing to install Microsoft Office or translate to and from formats like .CSV.

The Import-Excel command reads data from a worksheet in an XLSx file. By default, it assumes the data has headers and starts with the first header in Cell A1 and the first row of data in row 2. It will skip columns which don’t have a header and but will include empty rows. If no worksheet name is specified it will use the first one in the work book, so at its simplest the command looks like :
Import-Excel -Path .\demo.xlsx  

It’s possible that the worksheet isn’t the first sheet in the workbook and/or has a title above the data, so we can specify the start point explicitly
Import-Excel -Path .\demo.xlsx -WorkSheetname winners -StartRow 2  

We can say the first row does not contain headers and either have each property (column) named P1, P2, P3 etc, by using the ‑NoHeader switch or specify header names with the -HeaderName parameter like this

Import-Excel -Path .\demo.xlsx -StartRow 3 -HeaderName “Name”,"How Many"

The module also provides a ConvertFrom-ExcelSheet command which takes -Encoding and -Delimiter parameters and sends the data to Export-CSV with those parameters, and a ConvertFrom-ExcelToSQLInsert command which turns each row into a SQL statement: this command in turn uses a command ConvertFrom-ExcelData, which calls Import-Excel and then runs a script block which takes two parameters PropertyNames and Record.

Because this script block can do more than convert data, I added an alias “Use-ExcelData” which is now  part of the module and can be used like this
Use-ExcelData -Path .\NewUsers.xlsx -HeaderRow 2 -scriptBlock $sb

If I define the script block as below, each column becomes a parameter for the New-AdUser command which is run for each row

$sb = {
  param($propertyNames, $record)
  $propertyNames | foreach-object -Begin {$h = @{} }  -Process {
      if ($null -ne $record.$_) {$h[$_] = $record.$_}
  } -end {New-AdUser @h -verbose}
}

The script block gets a list of property names and a row of data: the script block gets called for each row and creates a hash table, adds an entry for each property and finally Splats the parameters into a command. It can be any command in the end block provided that the column names in Excel match its Parameters , I’m sure you can come up with your own use cases.

Advertisements

Blog at WordPress.com.

%d bloggers like this: