Write output from Excel with Powershell

Claus Wessel 1 Reputation point
2021-01-18T03:27:54.427+00:00

I am working on this very tedious task to create GPO Preferences for drive mappings.

Going through a spreadsheet with 3000+ rows i would like to ask if someone could help me along the way to have powershell read the data for me in the spreadsheet and output it for me into my variables.

I have 5 variables, which i need to write data for, which are in my spreadsheet:

$GPO = [GPO Name column]

$Group = [Group Name column]

$Drive = [Drive column]

$Comment = [Comment Column]

$Target = [OU Target Link column]

Hopefully someone could help me, and save me a tremendous load of time from having to put this info in manually.

I can not create a meaningful Drive mapping preference the traditional way with powershell,

and i am fine with just using "Set-GPRegistryValue"

Thanks in advance.

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,660 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,407 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Ian Xue (Shanghai Wicresoft Co., Ltd.) 31,171 Reputation points Microsoft Vendor
    2021-01-18T07:21:12.83+00:00

    Hi,

    You can read Excel spreadsheets using COM objects from powershell.

    $File = "C:\gpo.xlsx"  
    $Excel = New-Object -ComObject Excel.Application  
    $WorkBook = $Excel.Workbooks.Open($File)  
    $WorkSheet = $WorkBook.Worksheets.Item(1)  
    $UsedRange = $WorkSheet.UsedRange  
    $Rows = $UsedRange.Rows.Count  
     
    $Objs = @()  
    for($Row=2; $Row -le $Rows; $Row++){  
        $Objs += [PSCustomObject]@{  
            GPO = $UsedRange.Cells($Row,1).Text    
            Group = $UsedRange.Cells($Row,2).Text    
            Drive = $UsedRange.Cells($Row,3).Text    
            Comment = $UsedRange.Cells($Row,4).Text    
            Target = $UsedRange.Cells($Row,5).Text  
        }      
    }  
      
    $WorkBook.Close($false)  
    $Excel.Quit()  
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null  
    Stop-Process -Name "EXCEL"  
    

    Best Regards,
    Ian Xue

    ============================================

    If the Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Rich Matheisen 45,111 Reputation points
    2021-01-20T15:40:07.977+00:00

    Is this what your entire script looks like? I corrected the mistake on line #6 where you misspelled the $Path variable as "$Patht".

    $GPO_Preferences = Import-CSV “C:\Temp\Excel_GPO_Preferences.csv”
    ForEach ($GPO_Preference in $GPO_Preferences){
        $GPO = $GPO_Preference.GPO
        $Group = $GPO_Preference.Group
        $Drive = $GPO_Preference.Drive
        $Path = $GPO_Preference.Path
        $Target = $GPO_Preference.Target
    #
        New-GPO -Name $GPO -Comment $Path
        Set-GPPermission -Name $GPO -TargetName "Authenticated Users" -TargetType User -PermissionLevel gporead -Replace
        Set-GPPermissions -Name $GPO -PermissionLevel GpoApply -TargetName $group -TargetType Group -Replace
        New-GPLink -Name $GPO -Domain examplet.dk -Target $Target -LinkEnabled Yes
    #
        Set-GPRegistryValue -Name $GPO -Key "HKCU\Network\$Drive" -ValueName ConnectFlags -Type DWORD -Value 0
        Set-GPRegistryValue -Name $GPO -Key "HKCU\Network\$Drive" -ValueName ConnectionType -Type DWORD -Value 1
        Set-GPRegistryValue -Name $GPO -Key "HKCU\Network\$Drive" -ValueName DeferFlags -Type DWORD -Value 4
        Set-GPRegistryValue -Name $GPO -Key "HKCU\Network\$Drive" -ValueName ProviderFlags -Type DWORD -Value 1
    }
    

    On which cmdlet does it fail with the error that the "Name" parameter cannot be null?
    Are there any empty rows in your CSV?


  3. Claus Wessel 1 Reputation point
    2021-01-25T05:46:19.4+00:00

    @Rich Matheisen
    I am getting closer ! I can import a functional GPO Preferences from my .CSV list ... BUT It only reads the very last record in my .cvs??
    Then i can move another record to end of the .csv and it will import that, and so on ... only the last record gets imported.

    When i resolve $GPO_Preferences , i get all the records in my .csv perfectly .. so still, it reads the .csv fine.

    Any ideas please? I am so close now to succes.

    0 comments No comments

  4. Claus Wessel 1 Reputation point
    2021-01-25T15:16:33.223+00:00

    FINALLY i figured this out .. the reason why only last line was written, was the functions was not within the loop. just moving the } down to cover all functions, it works .

    Thank you loads for helping me through this ! Most grateful !

    0 comments No comments