{"id":757,"date":"2014-08-31T00:01:00","date_gmt":"2014-08-31T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2014\/08\/31\/weekend-scripter-parse-folder-of-data-with-powershell\/"},"modified":"2014-08-31T00:01:00","modified_gmt":"2014-08-31T00:01:00","slug":"weekend-scripter-parse-folder-of-data-with-powershell","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/weekend-scripter-parse-folder-of-data-with-powershell\/","title":{"rendered":"Weekend Scripter: Parse Folder of Data with PowerShell"},"content":{"rendered":"

Summary<\/b>: Use Windows PowerShell to parse a folder that contains data dumps.<\/span><\/p>\n

Microsoft Scripting Guy, Ed Wilson, is here. This morning I got up, and headed to the Farmers Market in town. I picked up a fresh melon for breakfast and some locally made cheese. Melon and cheese with a nice up of English Breakfast tea…not a bad meal.<\/p>\n

I am sitting on the back porch, munching melon, sipping tea, and checking my email on my Surface Pro 3. (The cheese was pretty much gone as soon as I sliced it—not saying what happened to it, just that it did not make it to the porch.)<\/p>\n

I decided that I need to do one more thing for my Data Manipulation Week. I am going to modify the function from yesterday (see A Function to Clean Up Data Import<\/a>), so that I can pass a file or a folder full of files to it, and do the data transformation.<\/p>\n

The situation<\/h2>\n

I have a folder that contains several data dumps. In fact, some of the data dumps are not even germane to my database. Luckily, the data dumps that I need all begin with the word DataDump<\/b>. Here is a look at the DataIn<\/b> folder:<\/p>\n

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

The DataDump2.csv file contains a couple of additional records—in fact, there are a couple new errors that I did not see in my previous DataDump file (such as a capital “O” in the street name ROck Query Road. This is shown here:<\/p>\n

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

Dude, how hard is that? Not very…<\/h2>\n

When I first thought about parsing a directory and finding all the DataDump files, I thought, well…like…I will need to write a new script.<\/p>\n

But I do not have to do that. The reason is that I have a nice function that accepts a path to a file. So all I need to do is to collect my paths to files, and I am set. To do that, I use the Get-ChildItem<\/b> cmdlet, and I specify that I want to recurse through the folder. I can use the –filter<\/b> parameter to specify that I only want files that begin with the word DataDump<\/b>. <\/i><\/p>\n

When I have a collection of FileInfo<\/b> objects that point to the CSV files, I want to process each of the files and run my Convert-Data<\/b> function on them. Here is the command I use (gci<\/b> is an alias for Get-ChildItem<\/b> and %<\/b> is an alias for ForEach-Object<\/b>):<\/p>\n

gci C:\\DataIn -Filter datadump* -Recurse | % {Convert-Data -path $_.FullName}<\/p>\n

That is it. I did not need to write a script to do this.<\/p>\n

A new consolidated CSV file…<\/h2>\n

Now I need a new consolidated CSV file so that I can use it to import data into my new database. No problem, I use the previous command (I simply use the Up arrow). Then at the end, I pipe it to Export-CSV<\/b> and I point it to the DataOut<\/b> folder. Here is the command:<\/p>\n

gci C:\\DataIn -Filter datadump* -Recurse | % {Convert-Data -path $_.FullName} |  Export-Csv -Path C:\\DataOut\\DataDumpOut.csv -Append -NoTypeInformation<\/p>\n

And here is the newly created CSV file:<\/p>\n

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

Join me tomorrow when I will begin a discussion about tuples…you will love it.<\/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: Use Windows PowerShell to parse a folder that contains data dumps. Microsoft Scripting Guy, Ed Wilson, is here. This morning I got up, and headed to the Farmers Market in town. I picked up a fresh melon for breakfast and some locally made cheese. Melon and cheese with a nice up of English Breakfast […]<\/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-757","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: Use Windows PowerShell to parse a folder that contains data dumps. Microsoft Scripting Guy, Ed Wilson, is here. This morning I got up, and headed to the Farmers Market in town. I picked up a fresh melon for breakfast and some locally made cheese. Melon and cheese with a nice up of English Breakfast […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/757","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=757"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/757\/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=757"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=757"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=757"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}