Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help writing a loop in a Powershell script

Posted on 2013-12-03
8
Medium Priority
?
553 Views
Last Modified: 2013-12-03
Hi guys,

I am teaching myself Powershell 3.0 while I am hacking together a script that will take a CSV file of names and emails and reformat its contents into a new file that CSVDE.exe can import into AD as new contacts for a distribution list.

I have successfully learnt how to import a CSV into a variable, export it to a new CSV file as well as confirmed that I can create a CSV that CSVDE can import into AD.

Where I am stuck is understanding how loops work and getting the individual vaules from an array properly. I can only seem to return all values in a column or only one value. Because I dont yet understand which method of creating a loop I should be using and how.

I have been using a "foreach" loop thinking, perhaps incorrectly, that it was best suited to my needs by looking at other examples online and in help files.

My source file has two columns of data only.
Column A is 'name' and column B is 'email'
I want to take the name and email from each line and reformat it into a new file with these 3 columns and properties

DW,objectClass,email

so to demonstrate how that should look with my data it should look like below.

"CN=(NAME from CSV),OU=All Suppliers,OU=Company,OU=Domain Users,DC=domain,DC=local",contact,(EMAIL from CSV)

How do I read each line from the souce CSV manipulate it it looks like the above using Powershell?

Thanks in advance,
AC
0
Comment
Question by:defecta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 40

Accepted Solution

by:
Subsun earned 2000 total points
ID: 39694114
You don't need a foreach loop to convert the csv file. You can do it by creating custom properties.. Try..
Import-Csv C:\temp\input.csv | Select @{N="DN";E={"CN=$($_.Name),OU=All Suppliers,OU=Company,OU=Domain Users,DC=domain,DC=local"}},@{N="objectClass";E={"contact"}},email | Export-Csv C:\Temp\result.csv -NTI

Open in new window

Ref :
http://blogs.technet.com/b/heyscriptingguy/archive/2011/05/19/create-custom-objects-in-your-powershell-script.aspx

Let me know if you have any questions..

BTB, here is a nice tutorial about loop processing in PowerShell
http://www.powershellpro.com/powershell-tutorial-introduction/logic-using-loops/
0
 

Author Closing Comment

by:defecta
ID: 39694146
Wow, thats amazing! Thank you so much!

I am thinking I could also use this method to take an extra step out of my script too.

The original file I start with has many columns I dont need that I have been using PS to create a new file with just the data I need but this is much better!

Thanks again!

AC
0
 

Author Comment

by:defecta
ID: 39694165
Quick question though.

This creates a CSV with double quotes around every attribute which CSVDE doesn't like.

It only needs to be around the DN attribute.

I tried to remove them from the code but it breaks. Is there a switch to prevent them from being created in the output or something?


EDIT: Nevermind I think its something else because the attribute name didn't match and I had to change it. Working on it.

Cheers,
AC
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 40

Expert Comment

by:Subsun
ID: 39694274
Export-csv output will have the quotes. If you want to exclude it then you need to use a different method to create the output file..
0
 

Author Comment

by:defecta
ID: 39694294
hmm ok, thanks for that.

Do you have any suggestions on alternatives?

I have just done some testing and I dont think the quotes are an issue as a manually created test file works with the quotes.

But I do have an issue with names field containing commas. I am learning how to remove/replace them now.
0
 
LVL 40

Expert Comment

by:Subsun
ID: 39694345
Try..
$file = "C:\Temp\result.csv"
Add-Content $file "DN,objectClass,email"
Import-Csv C:\Temp\input.csv | % {
  Add-Content $file "`"CN=$($_.Name),OU=All Suppliers,OU=Company,OU=Domain Users,DC=domain,DC=local`",contact,$($_.email)"
}

Open in new window

In future, Please create new request for any additional requirements which is out of the current question scope..
0
 

Author Comment

by:defecta
ID: 39694378
Sure, sorry I was actually happy to work this out myself.

Unless I am mistaken, the above script creates the file without all the additional quotes, does it not?

BTW I found the problem. The 3rd attribute is meant to be 'mail' but it was 'email'. :)
0
 

Author Comment

by:defecta
ID: 39694433
So i have finally figured out how to get it all working perfectly.

Here is my final code that cleans up commas from the names before creating the result.CSV.

Import-Csv C:\temp\input.csv | 
where {$_.EmailAddress -ne "" -and $_."Is Enabled" -eq "yes"} | 
Select @{N="DN";E={"CN=$($_."name" -replace ",", """ ),OU=All Suppliers,OU=Domain Users,DC=domain,DC=local"}},@{N="objectClass";E={"contact"}},@{N="mail";E={"$($_.EmailAddress)"}} | 
Export-Csv C:\Temp\result.csv -NTI

Open in new window


Thanks for all your help Subsun.

Now if only I could get it piping directly to CSVDE?? :P (thats not a request for help Subsun. :D)
0

Featured Post

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
There are times when we need to generate a report on the inbox rules, where users have set up forwarding externally in their mailbox. In this article, I will be sharing a script I wrote to generate the report in CSV format.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question