{"id":12231,"date":"2011-10-31T00:01:00","date_gmt":"2011-10-31T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2011\/10\/31\/use-powershell-to-append-csv-files-easily\/"},"modified":"2011-10-31T00:01:00","modified_gmt":"2011-10-31T00:01:00","slug":"use-powershell-to-append-csv-files-easily","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/use-powershell-to-append-csv-files-easily\/","title":{"rendered":"Use PowerShell to Append CSV Files Easily"},"content":{"rendered":"

Summary:<\/strong> Learn how to use Windows PowerShell to easily append one CSV file to another CSV file.<\/span><\/span><\/p>\n

 <\/span><\/p>\n

\"Hey,<\/span>Hey, Scripting Guy! I have a problem, and I have searched everywhere on the Internet to find an answer. I need to be able to append one comma separated value (CSV) to another CSV file and create a new CSV file. I know that I can use Get-Content<\/b> and Out-File<\/b> cmdlet to sort of do this, but the problem is that I end up with the header information from the files getting in the way. I have to then go in and edit the resulting file, and I would really like to automate this process if I could. I would LOVE a script to do this. Please help me; I really need to get this done. <\/span><\/p>\n

—RH<\/span><\/p>\n

 <\/span><\/p>\n

\"Hey,<\/span>Hello RH, <\/span><\/p>\n

Microsoft Scripting Guy Ed Wilson here. Ever since the birth of XML, people have been proclaiming the end of CSV files. The simple fact of the matter is, however, that CSV files are convenient ways to store and represent data. The other simple fact is that Windows PowerShell makes using CSV files super easy. In my scratch directory, I have a few CSV files that contain information about users. These files appear in the following figure.<\/span><\/p>\n

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

Those CSV files contain information that might be needed when creating a new user: it has the first name, last name, primary group assignment, and the organizational unit (OU) where their account will be created. One of those CSV files is shown in the following figure.<\/span><\/p>\n

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

I decide to use a simple filter<\/b> <\/i>with the Get-ChildItem<\/b> cmdlet to find only my user CSV files. Unfortunately, as seen here, the filter<\/b> <\/i>does not appear to correctly honor the meaning of a single ?<\/b> wildcard character, and I end up with an additional CSV file that does not contain a letter following the word users. <\/i>The command and associated output are shown here:<\/span><\/p>\n

PS C:\\> dir c:\\fso -Filter users?.csv<\/span> <\/span><\/p>\n

 <\/span><\/p>\n

    Directory: C:\\fso<\/span> <\/span><\/p>\n

 <\/span><\/p>\n

Mode<\/span>               LastWriteTime<\/span>                            Length Name<\/span><\/span><\/p>\n

-a—                 12\/7\/2007   3:14 PM                   155 users.csv<\/span><\/p>\n

-a—                 10\/28\/2011   2:24 PM                 272 UsersA.CSV<\/span><\/p>\n

-a—                 10\/28\/2011   2:27 PM                 300 UsersB.csv<\/span><\/p>\n

-a—                 10\/28\/2011   2:31 PM                 296 UsersC.csv<\/span><\/p>\n

 <\/span><\/p>\n

This is no problem. I decide to modify the filter to return only CSV files, and use the Where-Object<\/b> cmdlet to look for files that begin with the word user<\/i> and have a single character following the word. I use the wildcard character pattern users? <\/i>and the like<\/b> <\/i>operator. I could just as easily have used a regular expression pattern and the match<\/b> <\/i>operator. The revised command and output are shown here:<\/span><\/p>\n

PS C:\\> dir c:\\fso -Filter *.csv | ? {$_.basename -like ‘users?’}<\/span><\/p>\n

 <\/span><\/p>\n

    Directory: C:\\fso<\/span><\/p>\n

 <\/span><\/p>\n

Mode<\/span>               LastWriteTime<\/span>                            Length Name<\/span><\/span><\/p>\n

-a—                 10\/28\/2011   2:24 PM                 272 UsersA.CSV<\/span><\/p>\n

-a—                 10\/28\/2011   2:27 PM                 300 UsersB.csv<\/span><\/p>\n

-a—                 10\/28\/2011   2:31 PM                 296 UsersC.csv<\/span><\/p>\n

Now that I know I can find only the CSV files I need, I use the Import-CSV<\/b> cmdlet to import all the CSV files and display the output. This command is shown here (dir<\/b> is an alias for Get-ChildItem<\/b>, and ?<\/b> is an alias for the Where-Object<\/b> cmdlet):<\/span><\/p>\n

dir c:\\fso -Filter *.csv | ? {$_.basename -like ‘users?’} | Import-Csv<\/span><\/p>\n

The command and associated output are shown in the following figure.<\/span><\/p>\n

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

Before I decide to throw the output to a new CSV file, I can sort the consolidated information. For example, I might want to sort by last name and first name. Here is the command that does that:<\/span><\/p>\n

dir c:\\fso -Filter *.csv | ? {$_.basename -like ‘users?’} | Import-Csv | sort lname,fname<\/span><\/p>\n

The command and associated output appear are shown in the following figure (note that it looks like I might have a duplicate user, and should therefore check with the personnel office to see if they really hired two Adam Barrs on the same day).<\/span><\/p>\n

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

I decide to see if there is an alias for Import-CSV<\/b> because my command appears to be getting a little long. I use the Get-Alias<\/b> cmdlet and look for a definition<\/b> of Import-CSV<\/b>. As shown here, I found the alias ipcsv<\/b>:<\/span><\/p>\n

PS C:\\> Get-Alias -Definition import-csv<\/span><\/p>\n

 <\/span><\/p>\n

CommandType<\/span>              Name<\/span>                           Definition<\/span><\/span><\/p>\n

Alias                              ipcsv                             Import-Csv<\/span><\/p>\n

I also decide to do other sorts, such as sort by group and sort by OU. These commands are shown here:<\/span><\/p>\n

dir c:\\fso -Filter *.csv | ? {$_.basename -like ‘users?’} | Ipcsv | sort group<\/span><\/p>\n

dir c:\\fso -Filter *.csv | ? {$_.basename -like ‘users?’} | Ipcsv | sort OU<\/span><\/p>\n

I decide to stick with the last name (lname<\/b> column) and first name (fname<\/b> column) sort, and I use the Export-CSV<\/b> cmdlet to export my consolidated listing. I use the NoTypeInformation<\/b> <\/i>switch to keep Windows PowerShell from adding information to my CSV file that I do not need. Here is the command I ended up using. This is a one-line command that has wrapped. I did not add any line-continuation characters or anything else to the command. Dir<\/b> is the alias for Get-ChildItem<\/b>; ?<\/b> is the alias for Where-Object<\/b>; and sort<\/b> is an alias for Sort-Object<\/b>.<\/span><\/p>\n

dir c:\\fso -Filter *.csv | ? {$_.basename -like ‘users?’} | Import-Csv | sort lname,fname | Export-Csv -Path c:\\fso\\UsersConsolidated.csv -NoTypeInformation<\/span><\/p>\n

The command produces no output to the Windows PowerShell console:<\/span><\/p>\n

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

The consolidated CSV file is shown in the following figure (a CSV file directly opens up in Microsoft Excel).<\/span><\/p>\n

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

The only “bad” thing is my nice clean CSV files now have each element surrounded with quotation marks. Normally, this does not make any difference. For example, when reading the file with Import-CSV<\/b>, the quotation marks are not displayed. If this really becomes a problem, you can easily use the find and replace feature of Notepad to find quotation mark characters and replace them with nothing. <\/span><\/p>\n

 <\/span><\/p>\n

RH, that is all there is to using Windows PowerShell to append one CSV file to another CSV file. As you can see, using Windows PowerShell requires no scripting to perform this operation. Join me tomorrow for more Windows PowerShell goodness. <\/span><\/p>\n

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

<\/span><\/span><\/p>\n<\/p>\n

Ed Wilson, Microsoft Scripting Guy<\/b><\/span><\/p>\n

<\/span> <\/p>\n

 <\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Summary: Learn how to use Windows PowerShell to easily append one CSV file to another CSV file.   Hey, Scripting Guy! I have a problem, and I have searched everywhere on the Internet to find an answer. I need to be able to append one comma separated value (CSV) to another CSV file and create […]<\/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":[169,3,4,45],"class_list":["post-12231","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-csv-and-other-delimited-files","tag-scripting-guy","tag-scripting-techniques","tag-windows-powershell"],"acf":[],"blog_post_summary":"

Summary: Learn how to use Windows PowerShell to easily append one CSV file to another CSV file.   Hey, Scripting Guy! I have a problem, and I have searched everywhere on the Internet to find an answer. I need to be able to append one comma separated value (CSV) to another CSV file and create […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/12231","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=12231"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/12231\/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=12231"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=12231"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=12231"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}