Use PowerShell to Append CSV Files Easily

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

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

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.

I decide to use a simple filterwith the Get-ChildItem cmdlet to find only my user CSV files. Unfortunately, as seen here, the filterdoes 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

ModeLastWriteTimeLength 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 likeoperator. I could just as easily have used a regular expression pattern and the matchoperator. The revised command and output are shown here:

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

Directory: C:\fso

ModeLastWriteTimeLength 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):

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

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

CommandTypeNameDefinition

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:

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

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

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.

@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:fsoUsersConsolidated.csv) | % {$_ -replace "`"", "" }

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

I’m trying to merge a bunch of small csv files into larger ones but I want them to only have 1 million rows otherwise Excel can’t open the files. I have about 3000 files totally around 3Gb in size. Nothing I do to attempt to export with max rows seems
to work. It just chokes powershell and it dies. Any advice?