Import-Xlsx

Being satisfied with being able to easily export PowerShell objects directly to Excel I was wondering if I could do the reverse as well. Of course, importing from Excel would be rather arbitrary once you know how to export to Excel. Simply reverse the process.
Basically that is true. However, there are some caveats. For example, how do I know which worksheet to import in case there is more than one valid worksheet?
Or how do I know there actually is a valid worksheet in the Excel file?

Based on the Export-Xlsx function I’ve built an import function that deals with these questions.
Again, the function works with both .xls and .xlsx files. You can select which worksheet to import by specifying the worksheet name, or the worksheet index, or even by specifying nothing in particular.
If an Excel workbook contains more than one valid worksheet and you haven’t specified which one to import the function will let you select which worksheet to import.

Additionally, you can specify if the data on the worksheet is transposed. Meaning the headers are not in the first row, but in the first column. And you can specify if the worksheet has an additional title (For how this works see my Export-Xlsx post).

In order to ensure that the order of the properties remain untouched I’ve made use of the [Ordered] cast. So as a result of that the function only works in PowerShell v3. If you don’t care much about the order of the properties you can remove the [Ordered] cast and the #requires line to make the function work in PowerShell v2.

The function returns the data as a custom PowerShell object containing only String values. And as usual I’m declaring the function in the Global context due to how I load my functions.

Updated: 17-01-13 – fixed object output

Update Feb 01 2013

The function has been updated similarly to Export-Xlsx.
With support for relative paths, different system culture, etc. See the updated post on Export-Xlsx.

Tagged . Bookmark the permalink.

Comments are closed.