發表者	討論內容
冷日 (冷日)	發表時間：2015/12/18 8:32
Webmaster 註冊日: 2008/2/19 來自: 發表數: 15771	[轉貼]CSV, yeah you know me! – PowerShell and the Import-Csv CmdLet CSV, yeah you know me! – PowerShell and the Import-Csv CmdLet – Part 1 Managing data inside a CSV file is surprisingly simple thanks to the Import-CSV CmdLet. I’m often presented with data from different sources in a variety of formats, and by and large the common format used for data extracts is CSV. Regardless of the system that generates the content, we can all render and consume data using this simple and effective format. You will find the Microsoft TechNet documentation here: http://technet.microsoft.com/en-us/library/dd347665.aspx The file that we will use as our example is one that contains a username, email address, street address, postal code (zip code for my US friends) and a phone number. This is what the file would look like for our sample which will be named UserList.CSV: username,full name,email,address,postalcode,phone ewright,Eric Wright,eric@somedomain.com,123 Any Street,L4C 1N8,555-1212 jvoigt,Jens Voigt,jens@somedomain.com,456 Any Street,L4C 1N8,555-3456 ppocklington,Peter Pocklington,peter@anotherdomain.com,890 Back Street,M2H 4Y1,555-9876 Note that there is a header row which is one of the most important aspects for our script. If the file that you want to use does not have a header row, we can add one. I’ll go into that more deeply in an upcoming article. What we do first is to bring the file into a variable as an array. This is done by creating a variable which we will call $userobjects and assigning it a value using the Import-CSV CmdLet. Let’s assume that you have the import file in a folder X:ImportData $userobjects = Import-CSV x:ImportDataUserList.CSV No really, it’s just that simple. Now you have an array variable which you can use to access the data in a number of ways. Let’s look at a couple of methods to pick out data: Display all instances in the array: $userobjects Display the first element in the array: $userobjects[0] Ok, hold on a second. Why did we use 0 (zero) to pull the first record? This is a done because when an array is populated, the first element is in position 0. For a 2 dimensional array, the first element is 0,0 rather than 1,1 which can be a source of confusion. The purpose of the article was to talk about headers so now you will see where that comes into play. We have drawn out the different elements inside the array, so now we can use the headers to be able to gather the content of each element in a meaningful way. Let’s say that we want to see the email address of the first element. $userobjects[0].email It’s so dangerously simple really. Because the array was imported with the Import-CSV, the header row was assigned to columns just as we would see it inside a spreadsheet program. Just think of it as element[instance].property which means you can select any assigned property (column header) from any instance of the element. We can also do things such as count the elements which can be a handy piece of information to have: $userobjects.count In part 2 we will expand our script to loop through the elements and show you different ways that we can manage our data from the CSV file and begin to perform other operations using the contents. CSV, yeah you know me! – PowerShell and the Import-Csv CmdLet – Part 2 In part 1 of this series we touched on the basics of using the Import-Csv CmdLet by reading a file into an array variable and extracting elements and data from our input data. To enhance this process we can now take the data in memory and using a ForEach CmdLet to parse the data and do a multitude of actions with it. One thing that you may have noticed in the first part of the script is that we named the variable using the pluralized $userobjects rather than the singular $userobject. Why is that you ask? Great question! To simplify the identification of objects in our array we want to refer to them in a logical way of a singular userobject in an array, or list, of userobjects. While PowerShell doesnt formally use pluralization, I just find it easier to follow and hopefully it’s a better practice of programming. First we will add the very basic ForEach to output the Usernames to the screen. Using our pluralization format this is what we do: ForEach ( $userobject in $userobjects ) { Write-Host Username is $userobject.Username } Remember that everything within the curly braces will be performed against each object in the array sequentially and then move on to the next until it reaches the end of the array and moves to the next command outside of the curly braces. In other words the ForEach loops through the data and then move onwards, so if we want to display all of the usernames, and emails followed by a count of the total records it would look like this: ForEach ( $userobject in $userobjects ) { Write-Host Username is $userobject.Username had email $userobject.email } Write-Host Total Records = $userobjects.count Because we are capturing elements and the contained data, it is better to assign these as variables within our loop so that we can address them and also perform other tasks such as concatenation, splitting, calculation and any of a variety of other actions. I prefer to assign a meaningful name in a format like $FileVariableName or $InputVariableName. Here is what I would do in this particular case: ForEach ( $userobject in $userobjects ) { #Assign the content to variables $FileUsername = $userobject.username $FileFullName = $userobject.”full name” $FileEmail = $userobject.email $FileAddress = $userobject.address $FilePostalCode = $userobject.postalcode $FilePhoneNumber = $userobject.phone # Output the content to the screen Write-Host $FileFullName has a username of $FileUsername with email $FileEmail } Write-Host Total Records = $userobjects.count Notice that because one of our columns had a space in the name (Full Name) we had to enclose the column header name in double quotes as *$userobject.”full name”* or else it would throw an error. At this point you can see the capabilities of using your ForEach loop process. Recall that you can also pass along multiple commands to the loop. As long as you have the correct modules loaded, you can now use this process to run Microsoft Exchange CmdLets such as Get-Contact to check our Exchange directory if these are existing contacts: ForEach ( $userobject in $userobjects ) { #Assign the content to variables $FileUsername = $userobject.username $FileFullName = $userobject.”full name” $FileEmail = $userobject.email $FileAddress = $userobject.address $FilePostalCode = $userobject.postalcode $FilePhoneNumber = $userobject.phone # Output the content to the screen Write-Host $FileFullName has a username of $FileUsername with email $FileEmail if ( Get-Contact -Identity $FileEmail ) { Write-Host $FileFullName exists in the Exchange directory } } Write-Host Total Records = $userobjects.count Now we’ve expanded our script to make it more reliable, and depending on the complexity of the data we could see many other operations that we require to make use of the input data. The next article in the series will have us attack some additional tasks including: Adding a Header row where one does not exist Expand our Get-Contact query to perform a New-MailContact command if the record does not exist I hope that you find this helpful. The goal is to lay out the fundamentals that I’ve used for some of the more complex scripts that have been created for tasks such as importing and managing Exchange Contacts, managing Active Directory user account information and many such tasks. CSV, yeah you know me! – PowerShell and the Import-Csv CmdLet – Part 3 This was a long overdue post, so thanks for sticking with me while I finally got back on track with our CSV, yeah you know me series (Here are Part 1 and Part 2). As I’d mentioned in the closing of Part 2, we want to be able to deal with files that have no header in the CSV which we haven’t encountered yet. Also, we want to expand the use of our CSV content by running the Get-Contact query and creating a contact in the event that one doesn’t exist already. The Head(er)less Horseman In our previous posts, we have been using a file which contained some field data in the file and a header row containing the field names which made our job nice and easy with parsing out the data. Let’s take a look at the same source file, but this time we have no header row and our task will be to insert a header. This isn’t necessarily a common task, but we may find that we receive some batch data from a vendor and they have either no header, or the field names would have been useless to us, so we opt to create our own header row for ease of use. Here is our starter file named NoHeader.CSV We know that the field names that we wish to apply are as follows: username,full name,email,address,postalcode,phone so this will become part of our routine before parsing the data. I’m a big fan of leaving source data intact when manipulation of the content occurs, so the steps I’m going to use include working with a new file which is populated from the clean source file and my custom inserted header row. Getting back to basics, this was the command to import the data to begin with. $userobjects = Import-CSV x:ImportDataUserList.CSV Now that we want to start with a clean file though, we change the tactic to reading the content using the Get-Content CmdLet and then piping the output into a new file. But first, let’s setup our new target file. I’m also going to start referring to our script as CSVProcess.PS1 rather than just pasting all of my content in the command line each time. When using temporary files we need to do some simple steps: Define the file name and location (best done in a variable) Empty out the file if it exists because we are creating the content on the fly Send the header row data to the new empty file Append the content from our header-less file Revel in the joy of having done a cool task So our new filename will be called ImportData.CSV and I will assign it a variable $ImportFile at the start of our script. While we are at it, we will also define the NoHeader.CSV file as $ImportFileNoHeader at the same time: $ImportFile = “x:ImportDataImportData.CSV” $ImportFileNoHeader = “x:ImportDataNoHeader.CSV” Next we will use the Clear-Content CmdLet to empty out the file. The first time this line is executed it will generate an error because the file doesn’t exist, but that’s not a big problem because it will be created during the first script run: Clear-Content $ImportFile This is the error because if the missing file. We could handle the error and be fancy, but because this is a one-time error I would rather skip on lots of coding. I’ve used the Test-Path CmdLet for checking the file location, but it’s overkill for the simple process we are running here. So now we have our empty file (ImportData.CSV) and we will use the Add-Content CmdLet to append our header row: Add-Content $ImportFile “username,full name,email,address,postalcode,phone” The file now contains our header row. Yay! Now we read our header-less file (NoHeader.CSV) using the Get-Content CmdLet and pipe it into an Add-Content CmdLet to append to our ImportData.CSV file: Get-Content $ImportFileNoHeader \| Add-Content $ImportFile Yup, it’s just that easy. And here is our new file to be used for the contact management section of the script: So we now have our full process defined to add the header data, import the CSV data into the new import file, import the data into an array and loop through to use the Get-Contact process to check if they exist. Adding Contacts if they Don’t Exist Adding Exchange contacts is easily done (sort of) by using the New-MailContact CmdLet but there is a catch: Only some field can be added using the New-MailContact CmdLet and the rest have to be set after creation using the Set-MailContact CmdLet. Confused? Don’t worry, it takes a little getting used to, but this becomes second nature once you build the process once. I’m not going to dive into the features of the New-MailContact and Set-MailContact section here, so I’m doing the old Martha Stewart trick by putting the raw turkey in the top oven, and showing you the cooked one a minute later from the bottom oven. Here is the cooked version of the contact creation section of the script: So we have now reached the end of the quick 3 part series on using the Import-CSV CmdLet and how CSV data is easy to manage and manipulate for using in other processes. If you want to really dive deep, you can go over to my Importing, and Updating Exchange 2010 Contacts from CSV post which is probably the most popular overall post on the site. Hope that this has helped and if you have any questions, drop me a a comment below! Here is the final copy of the whole script so that you can see it from end to end. Happy Scripting! $ImportFile = “x:ImportDataImportData.CSV” $ImportFileNoHeader = “x:ImportDataNoHeader.CSV” Clear-Content $ImportFile Add-Content $ImportFile “username,full name,email,address,postalcode,phone” Get-Content $ImportFileNoHeader \| Add-Content $ImportFile $userobjects = Import-CSV $ImportFile ForEach ( $userobject in $userobjects ) { #Assign the content to variables $FileUsername = $userobject.username $FileFullName = $userobject.”full name” $FileEmail = $userobject.email $FileAddress = $userobject.address $FilePostalCode = $userobject.postalcode $FilePhoneNumber = $userobject.phone # Output the content to the screen Write-Host $FileFullName has a username of $FileUsername with email $FileEmail if ( Get-Contact -Identity $FileEmail ) { Write-Host $FileFullName exists in the Exchange directory } # First we create the contact with the required properties New-MailContact -Name “$FileUsername” ` -OrganizationalUnit “OU=YourContactsOU,DC=yourdomain,DC=com” ` -ExternalEmailAddress $FileEmail ` -Alias $FileUsername ` -DisplayName $FileFullName ` -PrimarySmtpAddress $FileEmail # Now we set the additional properties that aren’t accessible by the New-MailContact cmdlet Set-Contact -Identity $FileEmail ` -DisplayName $FileFullName ` -SimpleDisplayName $FileFullName ` -Phone $FilePhoneNumber ` -PostalCode $FilePostalCode ` -StreetAddress $FileAddress ` -WindowsEmailAddress $sourceEmail } Write-Host Total Records = $userobjects.count 原文出處： CSV, yeah you know me! – PowerShell and the Import-Csv CmdLet – Part 1 – DiscoPosse.com
回覆
冷日 (冷日)	發表時間：2015/12/18 8:36
Webmaster 註冊日: 2008/2/19 來自: 發表數: 15771	[轉貼]PowerShell Basics #1: Reading and parsing CSV PowerShell Basics #1: Reading and parsing CSV I will be giving a talk on the topic of “PowerShell for Developers” at TechDays 2010 in Helsinki, Finland. As a warm-up to my presentation, I will be publishing a series of blog posts on various aspects of PowerShell. My viewpoint is specifically one of developer utility: What can PowerShell do to make a dev’s life easier? I want to start with something that touches on data. Often, developers receive data in Excel format – usually in order to then import it into a database. Reading data from Excel is somewhat painful, but fortunately, Excel allows for easy saving to the CSV format. PowerShell, on the other hand, provides for several quite easy manipulations. Simple imports with Import-Csv Let’s start with a simple CSV file, customers.csv: ID,Name,Country 1,John,United States 2,Beatrice,Germany 3,Jouni,Finland 4,Marcel,France Turning this into objects in PowerShell is very straightforward: PS D:\temp> import-csv customers.csv ID Name Country -- ---- ------- 1 John United States 2 Beatrice Germany 3 Jouni Finland 4 Marcel France As you can see, the first line in the text file gets parsed as a header, specifying the names on the PowerShell objects. The import doesn’t have a notion of strong typing; therefore, all the properties are imported as pure text. Often this is enough, but if it isn’t, look below… Headerlessness and other cultures There are a few scenarios where this won’t work. For example, if your CSV doesn’t have headers, you would get objects with column names such as “1”, “John” and “United States”. Lacking headers, you can supply them as a parameter: import-csv .\customers.csv -header ID,Name,Country That was easy (but don’t do it when your data has headers, or you end up duplicating them). Well then, perhaps you live in a region where the field separator isn’t the usual comma? This is no problem to PowerShell, either PS D:\temp> type customers-fi.csv ID;Name;Country 1;John;United States 2;Beatrice;Germany PS D:\temp> import-csv .\customers-fi.csv -delimiter ';' ID Name Country -- ---- ------- 1 John United States 2 Beatrice Germany If you know the file originated from your current UI culture, you can just dispense with the delimiter specification and type `import-csv –useCulture customers-fi.csv`. That will pick up the delimiter from your Windows settings. When your CSV ain’t a file… Often you get your CSV data in a file. Occasionally, you might download it through HTTP, or even pull it from a database. No matter how, you may end up with an array of strings that contains your CSV data. The Import-Csv cmdlet reads a file, but if you need to parse the data from another source, use ConvertFrom-Csv. PS D:\temp> $csv = 'ID,Name,Country >> 1,John,United States >> 2,Beatrice,Germany' >> PS D:\temp> ConvertFrom-Csv $csv ID Name Country -- ---- ------- 1 John United States 2 Beatrice Germany As far as the culture switches go, everything discussed above also applies to ConvertFrom-Csv. How about CSV content oddities? There are some uses of CSV that veer away from the normal, safe path. The first and a reasonably common scenario is having the field delimiter in the data, something that is usually handled by quoting the field. Of course, up next is the scenario where a field contains the quotation mark. And finally, there is the really controversial aspect of having a newline in a CSV field. Many parsers struggle with this, and in fact, the correct behavior isn’t exactly clear. Of course, for practical purposes, anything that makes Excel exports work correctly is usually good. But let’s look at an example that contains all of these anomalies (the original content for this is shown in the Excel screenshot to the right). Number,String,Multiline,Date 512,"Comma,Rocks","Line 1 Line 2",15.1.2010 15:14 57,"Cool ""quotes""","First Second",7.1.2010 9:33 The data is split across five lines, but actually contains two records and a header. This alone is somewhat controversial, given CSV’s starting point of one line per record. Anyway, it often still needs to be parsed, and PowerShell does a good job here: PS D:\temp> Import-Csv .\oddstrings.csv Number String Multiline Date ------ ------ --------- ---- 512 Comma,Rocks Line 1... 15.1.2010 15:14 57 Cool "quotes" First... 7.1.2010 9:33 There is one key thing to notice. Import-Csv works, because it treats the data source as a single whole. However, ConvertFrom-Csv misparses the multiline fields, as it handles the input line-by-line. PS D:\temp> type .\oddstrings.csv \| ConvertFrom-Csv Number String Multiline Date ------ ------ --------- ---- 512 Comma,Rocks Line 1 Line 2" 15.1.2010 15:14 57 Cool "quotes" First Second" 7.1.2010 9:33 Strong typing, then? For this, there are no pre-cooked solutions. But once your data is imported correctly, separators, multiline fields and all, it’s rather easy to just typecast the stuff, providing you input validation at the same time. Consider this CSV file of event descriptions: StartsAt,Title,Venue 9.3.2010,TechDays 2010,Helsinki 15.2.2010,Mobile World Congress,Barcelona Next, you want to filter the data to just show the occurring within the next 30 days. For this, you'll want the datetimes parsed into System.DateTime objects. PS D:\temp> $events = Import-Csv event.csv \| foreach { New-Object PSObject -prop @{ StartsAt = [DateTime]::Parse($_.StartsAt); Title = $_.Title; Venue = $_.Venue } } PS D:\temp> $events StartsAt Venue Title -------- ----- ----- 9.3.2010 0:00:00 Helsinki TechDays 2010 15.2.2010 0:00:00 Barcelona Mobile World Congress Now, filtering the list is a snap. PS D:\temp> $events \| where { $_.StartsAt -lt (get-date).AddDays(30) } StartsAt Venue Title -------- ----- ----- 15.2.2010 0:00:00 Barcelona Mobile World Congress One more thing to notice here: In the example above, it worked because the date format (“15.2.2010”) was in Finnish and I happened to run the PowerShell in a thread with the Finnish culture. However, if your data happens to come from a culture different than your UI, you need to pass in the correct culture specifier. For example, to parse a date in the US locale, use the following: $usCulture = [System.Globalization.CultureInfo]::CreateSpecificCulture("en-US") [DateTime]::Parse("04/07/2009", $usCulture) Note that specifying the culture explicitly is always a good idea if you plan on saving the script and reusing it later. Although you might pay attention to the parsing details at the time of writing, it is quite conceivable for someone to run the script later on in another thread. As dates are prone to silent misparsing (for example, is 04/07/2009 7th April or 4th July?), you could end up manipulating incorrect data. In addition to dates, you’ll want to look at cultures when parsing decimal numbers and such. Remember: the –UseCulture switch on Import-Csv only applies to the separator settings, not the optional parsing phase. Enjoy! 原文出處： Heikniemi Hardcoded » PowerShell Basics #1: Reading and parsing CSV
回覆