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

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? 1 Answer 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)  • Thanks, I'm not quite sure on the variable assignments in this example and how I can change them to match my needs. I have 270 rows in the xlsx and 7 columns With the A column being the desired docset name. With that in mind, how could I re-write the code to populate these 7 variables and then call the doc set creation line until we reach the end of the rows. Maybe a$ws.Cells.Count or something Feb 22, 2016 at 19:48