Write output from Excel with Powershell RRS feed

  • Frage

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

    PS: I had to chose German language, was not able to chose Forum Category in English.

    Sonntag, 17. Januar 2021 20:14


Alle Antworten

  • PS: I had to chose German language, was not able to chose Forum Category in English.

    That's because the English forums have all been migrated to Q&A:

    Evgenij Smirnov

    Sonntag, 17. Januar 2021 22:09
  • You search for something as simple as below?

    $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
        $Comment = $GPO_Preference.Comment
        $Target = $GPO_Preference.Target
        Write-Host $GPO $Group $Drive $Comment $Target

    Montag, 18. Januar 2021 07:40
  • @Tobias Redelberger. Thank you , looks brilliant ! it is that simple ? 

    - Except it is an excel spreadsheet, not a .csv 

    - I gathered the .xlsx from several .csv's (several queries, which i don't understand how to make in one query output, in one .csv) 

    But i guess, if i can save the .xlsx back to an .csv i am home free 

    Montag, 18. Januar 2021 07:53
  • Hello,

    As Evgeni already mentioned, all English Technet forums have been migrated to Microsoft Q&A. All US Technet Forums are read-only from the 6th of January. As this is a German Technet forum I would kindly ask if you prefer to continue in English to post your question in Microsoft Q&A. If you choose to continue your discussion on the German Technet platform, I would ask you to translate your post into German.

    Nevertheless, I hope you have received helpful guidance to help solve your problem.

    Thank you!

    Kind regards,


    Bitte haben Sie Verständnis dafür, dass im Rahmen dieses Forums, welches auf dem Community-Prinzip „IT-Pros helfen IT-Pros“ beruht, kein technischer Support geleistet werden kann oder sonst welche garantierten Maßnahmen seitens Microsoft zugesichert werden können.

    Montag, 18. Januar 2021 12:47
  • Ich entschuldige mich, ich verstehe Ihren Punkt.
    Aber ich habe immer noch ein Problem damit, mein Skript zum Laufen zu bringen
    Mittwoch, 20. Januar 2021 11:00
  • Vielen Dank, es funktioniert perfekt Ihre Eingabe. Ich kann $GPO_Preferences perfekt ausgeben. Aber wie kann ich es in meinen anderen Teil integrieren, um das Gruppenrichtlinienobjekt tatsächlich zu erstellen?

    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 -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
    Set-GPRegistryValue -Name $GPO -Key "HKCU\Network\$Drive" -ValueName ProviderName -Type String -Value "Microsoft Windows Network"
    Set-GPRegistryValue -Name $GPO -Key "HKCU\Network\$Drive" -ValueName ProviderType -Type DWORD -Value 131072
    Set-GPRegistryValue -Name $GPO -Key "HKCU\Network\$Drive" -ValueName RemotePath -Type String -Value $Path
    Set-GPRegistryValue -Name $GPO -Key "HKCU\Network\$Drive" -ValueName UserName -Type DWORD -Value 0

    Mittwoch, 20. Januar 2021 11:06