Create document sets in powershell from xlsx (not csv) file

Question

On occasion we have a need to create multiple document sets in a single library. In the past I have always put the details (e.g. name and any other column values) into a csv file and used powershell to create the document sets using this data via import-csv and
$NewFolder = [Microsoft.Office.DocumentManagement.DocumentSets.DocumentSet]::Create($dlist.RootFolder,$docSetInfo.theName,$cType.Id, $docsetProperties)

However this time I need to take some values from a sharepoint list and use these as the values. Ordinarily I would export the list to a spreadsheet and then save the spreadsheet as a CSV file however one of the columns in the list is a multiple line of text column type and these values are lost in CSV format - probably due to line breaks in the values.

I see no corresponding import cmdlet for dealing with xlsx files so am woindering what my options are here?

Answer 1

You will need to use the COM object to interact with Excel:

(Code taken from StackOverflow post on the same issue): https://stackoverflow.com/questions/16615364/read-excel-data-with-powershell-and-write-to-a-variable

User input can be read like this:

$num = Read-Host "Store number"
Excel can be handled like this:

$xl = New-Object -COM "Excel.Application"
$xl.Visible = $true
$wb = $xl.Workbooks.Open("C:\path\to\your.xlsx")
$ws = $wb.Sheets.Item(1)
Looking up a value in one column and assigning the corresponding value from another column to a variable could be done like this:

for ($i = 1; $i -le 3; $i++) {
  if ( $ws.Cells.Item($i, 1).Value -eq $num ) {
    $GoLiveDate = $ws.Cells.Item($i, 2).Value
    break
  }
}
Don't forget to clean up after you're done:

$wb.Close()
$xl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)