Use PowerShell to Append CSV Files Easily

Use PowerShell to Append CSV Files Easily

  • Comments 9
  • Likes

Summary: Learn how to use Windows PowerShell to easily append one CSV file to another CSV file.

 

Hey, Scripting Guy! QuestionHey, 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 and Out-File 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.

—RH

 

Hey, Scripting Guy! AnswerHello RH,

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.

Image of CSV files containing information about users

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.

Image of one of the CSV files

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

PS C:\> dir c:\fso -Filter users?.csv 

 

    Directory: C:\fso 

 

Mode               LastWriteTime                            Length Name

-a---                 12/7/2007   3:14 PM                   155 users.csv

-a---                 10/28/2011   2:24 PM                 272 UsersA.CSV

-a---                 10/28/2011   2:27 PM                 300 UsersB.csv

-a---                 10/28/2011   2:31 PM                 296 UsersC.csv

 

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

PS C:\> dir c:\fso -Filter *.csv | ? {$_.basename -like 'users?'}

 

    Directory: C:\fso

 

Mode               LastWriteTime                            Length Name

-a---                 10/28/2011   2:24 PM                 272 UsersA.CSV

-a---                 10/28/2011   2:27 PM                 300 UsersB.csv

-a---                 10/28/2011   2:31 PM                 296 UsersC.csv

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

dir c:\fso -Filter *.csv | ? {$_.basename -like 'users?'} | Import-Csv

The command and associated output are shown in the following figure.

Image of command and associated output

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:

dir c:\fso -Filter *.csv | ? {$_.basename -like 'users?'} | Import-Csv | sort lname,fname

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).

Image of command and associated output

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

PS C:\> Get-Alias -Definition import-csv

 

CommandType              Name                           Definition

Alias                              ipcsv                             Import-Csv

I also decide to do other sorts, such as sort by group and sort by OU. These commands are shown here:

dir c:\fso -Filter *.csv | ? {$_.basename -like 'users?'} | Ipcsv | sort group

dir c:\fso -Filter *.csv | ? {$_.basename -like 'users?'} | Ipcsv | sort OU

I decide to stick with the last name (lname column) and first name (fname column) sort, and I use the Export-CSV cmdlet to export my consolidated listing. I use the NoTypeInformation 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 is the alias for Get-ChildItem; ? is the alias for Where-Object; and sort is an alias for Sort-Object.

dir c:\fso -Filter *.csv | ? {$_.basename -like 'users?'} | Import-Csv | sort lname,fname | Export-Csv -Path c:\fso\UsersConsolidated.csv -NoTypeInformation

The command produces no output to the Windows PowerShell console:

Image showing command produces no output

The consolidated CSV file is shown in the following figure (a CSV file directly opens up in Microsoft Excel).

Image of consolidated CSV file

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, 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.

 

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.

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

Ed Wilson, Microsoft Scripting Guy

 

 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Nice article! But, why use notepad?

    ?.Replace("`"","")

  • @Jeffrey S. Patton, you are right that I could have used code to replace the quotation mark with nothing, but it is a lot more work for this particular scenario. I do not believe your code below will actually work, but it is definately on the right track. I came up with the following:

    (Get-Content C:\fso\UsersConsolidated.csv) | % {$_ -replace "`"", "" }

    But this is only half of the story. Because next I need to write this back to a file.

  • Hi Ed,

    this is a really easy way to accomplish the task!

    In fact the quotation marks are sometimes unwanted and the best solution would be an additional switch parameter to export-csv that supresses them!

    But of course we can get around it with notepad or a little code ...

    Klaus

  • When I try this, the first csv is imported and then there is a long blank space under it and nothing else. The second csv does not show as in your example. Any ideas what could be causing this?

    I'm doing this on a Win 7 SP1 machine. Powershell 2.0

    Thanks

  • Hi,

    Nice solution ! Thanks.

    For the problem of quotation marks, i have found a solution by modifying your command :

    dir c:\fso -Filter *.csv | ? {$_.basename -like 'users?'} | Import-Csv -Delimiter ";" | sort lname,fname | Export-Csv -Path c:\fso\UsersConsolidated.csv -NoTypeInformation -Delimiter ";"

    My solution is to force to interpret the delimiter. For me, i use always the ";" delimiter because Excel interpret it as i want, with separated column.

    In my case, i export CSV with ";" delimiter and i use it always.

    Of course, it depends of your type of your delimiter. By default it seems to be "," and i think you can optimize your command with :

    dir c:\fso -Filter *.csv | ? {$_.basename -like 'users?'} | Import-Csv -Delimiter "," | sort lname,fname | Export-Csv -Path c:\fso\UsersConsolidated.csv -NoTypeInformation -Delimiter ","

    Regards.

  • For those with more complicated scenarios where you have different columns/headers, and want to use one or more of them as an ID / reference point, I wrote this generic CSV (really custom PowerShell object) merger: http://www.powershelladmin.com/wiki/An_Advanced_Cmdlet_to_Merge_Csv_Files_in_PowerShell As for stripping double quotes from a file (-replace works on collections, then pipe to Set-Content): (gc csvfile.csv) -replace '"' | sc -enc UTF8 csv-noquotes.csv

  • @Joakim Svendsen: Thank you! Wonderful work!

  • I am trying to merge two csv files which I get after running the below command

    Get-Recipient -OrganizationalUnit "OU Name" -filter{CustomAttribute3 -eq $null } | Select Name, RecipientType | Export-Csv D:\Aziz\CustAtt3Aus.csv

    Get-Recipient -OrganizationalUnit "OU Name" -filter{CustomAttribute3 -eq $null} | Select Name, RecipientType | Export-Csv D:\Aziz\CustAtt3NZ.csv

    When I run the above commands I get the output perfect

    Then I run the below command, however the files doesn't merge and there is no error message

    #Merge both the CSV file in one
    dir D:\Aziz -Filter *.csv | ? {$_.basename -like 'CustAtt?'} | Import-Csv

    dir D:\Aziz -Filter *.csv | ? {$_.basename -like 'CustAtt?'} | Import-Csv | sort Name, RecipientType | Export-Csv -Path "D:\Aziz\AUS&NZCustAtt3.csv" -NoTypeInformation

    Now I tried to crack down the Merge command
    ----------------------------------------------------
    When I run the below command I am able to view the output on the powershell

    dir D:\Aziz -Filter *.csv

    Then I added the next parameter in the commands and ran it, I get no output even when I add -lik and try to autocomplete it doesn't complete

    dir D:\Aziz -Filter *.csv | ? {$_.basename -like 'CustAtt?'} or
    dir D:\Aziz -Filter *.csv | ? {$_.basename -like 'CustAtt3?'}

    any help will be appreciated.

  • Hello,

    I fixed the issue.

    The issue was because of the naming convention
    the output file after running the below commands were CustAtt3Aus and CustAtt3NZ


    Get-Recipient -OrganizationalUnit "OU Name" -filter{CustomAttribute3 -eq $null } | Select Name, RecipientType | Export-Csv D:\Aziz\CustAtt3Aus.csv

    Get-Recipient -OrganizationalUnit "OU Name" -filter{CustomAttribute3 -eq $null} | Select Name, RecipientType | Export-Csv D:\Aziz\CustAtt3NZ.csv

    So I changed the Output file name to CustAtt1 & CustAtt2 instead of CustAtt3Aus and CustAtt3NZ

    Later I ran the below command

    >Dir D:\Aziz -Filter custatt?.csv and got the output perfectly on the powershell

    Later ran the merge command it completed successfully

    Dir D:\Aziz -Filter *.csv | ? {$_.basename -like 'CustAtt?'} | Import-Csv
    Dir D:\Aziz -Filter *.csv | ? {$_.basename -like 'CustAtt?'} | Import-Csv | Select Name, RecipientType | Export-Csv -Path "D:\Aziz\AUS&NZCustAtt3.csv" -NoTypeInformation

    So the conclusion is the file name should be Alpha numeric otherwise the merge wont work

    Example
    CustAttAUS should be CustAtt1
    CustAttNS should be CustAtt2