0

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 1

0

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)
1
  • 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
    – Penfold
    Feb 22, 2016 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.