Write output from Excel with Powershell

Question

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.

Answer 1

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.

Answer 2

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?

Answer 3

@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.

Answer 4

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 !