{"id":761,"date":"2014-08-30T00:01:00","date_gmt":"2014-08-30T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2014\/08\/30\/weekend-scripter-a-function-to-clean-up-data-import\/"},"modified":"2014-08-30T00:01:00","modified_gmt":"2014-08-30T00:01:00","slug":"weekend-scripter-a-function-to-clean-up-data-import","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/weekend-scripter-a-function-to-clean-up-data-import\/","title":{"rendered":"Weekend Scripter: A Function to Clean Up Data Import"},"content":{"rendered":"

Summary<\/b>: Learn about a Windows PowerShell function you can use to clean up data prior to import.<\/span><\/p>\n

Microsoft Scripting Guy, Ed Wilson, is here. We are entering the Labor Day weekend in the United States today. Labor Day is a sad holiday, but it is also a happy time. It is sad because it traditionally marks the end of summer. It is happy because if you live in Charlotte, North Carolina, it means that the time of high temperatures and high humidity are nearing an end.<\/p>\n

Autumn (September through November) is definitely the best time of the year to visit Charlotte. The temperatures during the day are warm enough to permit short sleeve shirts, and in some cases short pants. In the evening, the temperatures are low enough that a nice jacket is all you need to keep warm. The leaves on the trees change colors, the sky clears, the humidity drops, and there is hardly any rain. Yes, it is my favorite time of the year around here.<\/p>\n

Why a function?<\/h2>\n

During Data Manipulation Week, I have created a series of scripts to parse various aspects of data that came from a data dump. I created scripts to fix the date, the names, and the addresses that were presented inconsistently in a spreadsheet. This is the process I would follow when I needed to normalize data that I got from one database before I import it to another database. Yesterday, I created a single object from these portions of script. Now to provide a high level of versatility, I want to transform yesterday’s script into a function.<\/p>\n

This process is actually pretty easy because I designed my separate scripts with integration in mind. I need to add the Function<\/b> keyword, assign a name to the function, add a parameter, import the CSV file, and then close the function. That is about all I need to do. Here is the command I use to begin my function:<\/p>\n

Function Convert-Data<\/p>\n

{<\/p>\n

Now I need to define a parameter. The parameter I need is a path to the CSV file that holds the data I want to import:<\/p>\n

Param ([string]$path)<\/p>\n

I need to import the CSV file and store it in a variable. I use the same $datain<\/b> variable I have used all week. The path to the CSV file that will be imported is via the $path<\/b> variable that I have set as a parameter to the function. This is shown here:<\/p>\n

$datain = Import-CSV -Path $path <\/p>\n

I close the function:<\/p>\n

} #end function Convert-Data<\/p>\n

And the rest of the script is the same. In fact, because I created regions in my script yesterday, it is pretty easy to look at:<\/p>\n

Foreach ($d in $datain)<\/p>\n

    {<\/p>\n

#region Name<\/p>\n

 If($d.name -match  ',')<\/p>\n

        {<\/p>\n

          $name = (Get-Culture).textinfo.ToTitleCase($d.name).Split(',').trim()<\/p>\n

          $ln = $name[0]<\/p>\n

          $fn = $name[1]<\/p>\n

          }<\/p>\n

        ELSE {<\/p>\n

         $name = $d.Name.Split().trim()<\/p>\n

         $fn = $name[0]<\/p>\n

         $ln = $name[1]<\/p>\n

           }<\/p>\n

#endregion<\/p>\n

 <\/p>\n

 #region Address<\/p>\n

     $a = $d.address.split(',')<\/p>\n

     $str = $a[0] -replace '(Street|street)','ST'<\/p>\n

     $city = If($a[1] -match 'dwpt') {"Dewpoint"} ELSE {$a[1]}<\/p>\n

     $state = if($a[2] -match 'South Carolina') {"SC"} ELSE {$a[2]}<\/p>\n

     $zip = If($a[3].trim().Length -gt 5) {"Error for $a"} ELSE {$a[3]}<\/p>\n

#endregion<\/p>\n

 <\/p>\n

#region Create Custom Object<\/p>\n

     [PSCustomObject]@{<\/p>\n

       Lname = $ln<\/p>\n

       Fname = $fn<\/p>\n

       Date = [datetime]("{0}\/{1}\/{2}" -f $d.month, $d.day, $d.year)<\/p>\n

       Street = $str.ToUpper()<\/p>\n

       City = $city.ToUpper()<\/p>\n

       State = $state.Trim().Substring(0,2).ToUpper()<\/p>\n

       Zip = $zip }<\/p>\n

#endregion<\/p>\n

    } <\/p>\n

The complete script is presented here:<\/span><\/p>\n

Function Convert-Data<\/p>\n

{<\/p>\n

 Param ([string]$path)<\/p>\n

 $datain = Import-CSV -Path $path<\/p>\n

 Foreach ($d in $datain)<\/p>\n

    {<\/p>\n

#region Name<\/p>\n

 If($d.name -match  ',')<\/p>\n

        {<\/p>\n

          $name = (Get-Culture).textinfo.ToTitleCase($d.name).Split(',').trim()<\/p>\n

          $ln = $name[0]<\/p>\n

          $fn = $name[1]<\/p>\n

          }<\/p>\n

        ELSE {<\/p>\n

         $name = $d.Name.Split().trim()<\/p>\n

         $fn = $name[0]<\/p>\n

         $ln = $name[1]<\/p>\n

           }<\/p>\n

#endregion<\/p>\n

 <\/p>\n

 #region Address<\/p>\n

     $a = $d.address.split(',')<\/p>\n

     $str = $a[0] -replace '(Street|street)','ST'<\/p>\n

     $city = If($a[1] -match 'dwpt') {"Dewpoint"} ELSE {$a[1]}<\/p>\n

     $state = if($a[2] -match 'South Carolina') {"SC"} ELSE {$a[2]}<\/p>\n

     $zip = If($a[3].trim().Length -gt 5) {"Error for $a"} ELSE {$a[3]}<\/p>\n

#endregion<\/p>\n

 <\/p>\n

#region Create Custom Object<\/p>\n

     [PSCustomObject]@{<\/p>\n

       Lname = $ln<\/p>\n

       Fname = $fn<\/p>\n

       Date = [datetime]("{0}\/{1}\/{2}" -f $d.month, $d.day, $d.year)<\/p>\n

       Street = $str.ToUpper()<\/p>\n

       City = $city.ToUpper()<\/p>\n

       State = $state.Trim().Substring(0,2).ToUpper()<\/p>\n

       Zip = $zip }<\/p>\n

#endregion<\/p>\n

    }<\/p>\n

} #end function Convert-Data<\/p>\n

When I run the script in the ISE, it loads the function into memory. I can then call it directly from the console pane, as shown in the following image:<\/p>\n

\"Image<\/a><\/p>\n

I invite you to follow me on Twitter<\/a> and Facebook<\/a>. If you have any questions, send email to me at scripter@microsoft.com<\/a>, or post your questions on the Official Scripting Guys Forum<\/a>. See you tomorrow. Until then, peace.<\/p>\n

Ed Wilson, Microsoft Scripting Guy<\/b> <\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Summary: Learn about a Windows PowerShell function you can use to clean up data prior to import. Microsoft Scripting Guy, Ed Wilson, is here. We are entering the Labor Day weekend in the United States today. Labor Day is a sad holiday, but it is also a happy time. It is sad because it traditionally […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[528,3,4,61,45],"class_list":["post-761","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-comma-separated-and-other-delimited-files","tag-scripting-guy","tag-scripting-techniques","tag-weekend-scripter","tag-windows-powershell"],"acf":[],"blog_post_summary":"

Summary: Learn about a Windows PowerShell function you can use to clean up data prior to import. Microsoft Scripting Guy, Ed Wilson, is here. We are entering the Labor Day weekend in the United States today. Labor Day is a sad holiday, but it is also a happy time. It is sad because it traditionally […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/761","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=761"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/761\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=761"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=761"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=761"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}