Merge Multiple CSV Files With Same Header Using Windows PowerShell

Currently, I am working on eRoom to SharePoint migration project with the help of Metalogix. Metalogix eRoom console has a feature to run pre-migration check to identify possible migration issues which could occur during migration that includes the following:

• Document Url length
• Document Size
• Document Name
• Unsupported file types

Identifying these possible issues is necessary so that you can plan for these failures by talking to end user. Metalogix eRoom console generate report of each of these issues based on source eRoom and target SharePoint site where the content will be migrated. These reports are generated in the form of .csv files. Every eRoom pre-migration check feature generates 4 such files. For us, we have to migrate approximately 230 eRoom to SharePoint 2010 environment. So we wanted to run pre-migration check to all of these 230 eRooms and generate consolidated report so that we can review it with business user.

Now to combine these 230 csv files for every issue could take days and involve lot of manual efforts. To avoid those manual efforts, I decided to use Windows PowerShell which provides tons of cmdlets (command lets) which could do easy trick to avoid such manual efforts.

I have created PowerShell function with the following:

Accepts 3 parameters

• $Path – Path of the directory where all the csv files are stored.
  
  
• $MergedFileName – Name of the file which would be created by merging target multiple csv. files.
  
  
• $Filter – Any filter criteria which could be used to filter the list of target csv files to be merged.

Based on the path and filter criteria, it finds list of files to be merged.

if($Filter -ne $null -and $Filter -ne "")
{
   $AllFilesToMerge = Get-ChildItem -Path $Path -File -Filter $Filter -Recurse
}
else
{
   $AllFilesToMerge = Get-ChildItem -Path $Path -Recurse
}

Then it process each file by importing it.

$csvFilePath = $FileToMerge.FullName
$csvContent = Import-Csv -Path "$csvFilePath"

Then it reads the file content based on the headers of the csv file. Every row in the csv is stored in temporary PSObject variable which is then added to another global PSObject.

Note: You need to update the script for your csv headers.

if ($csvContent - ne $null) {
    foreach($entry in $csvContent) {#
        You can also get the headers of particular.csv file like below - commented# $CSVContentObj = $csvContent | Get - Member - MemberType NoteProperty | Select - Object - ExpandProperty Name# Creating custom PS Object to hold the content temporarily
        $CSVContentObj = 1 | select RoomName, SourceName, SourceURL, RectifiedName
        $CSVContentObj.RoomName = $roomName# TODO: User need to update the headers manually before using this
        function# $entry.
        "<HeaderName>"
        $CSVContentObj.SourceName = $entry.
        "Source Name"
        $CSVContentObj.SourceURL = $entry.
        "Source URL"
        $CSVContentObj.RectifiedName = $entry.
        "Rectified Name"#
        Adding custom PS object in the arrary
        $CSVContentList += $CSVContentObj
    }
}
else {
    Write - Host - ForegroundColor Yellow "File don't have any content"
}

Finally, if the global PSObject which holds all the content is not null, export the content in target csv file.

if ($CSVContentList - ne $null) {#
    Getting current script execution location
    $CurrentLoc = Get - Location
    if ($MergedFileName.IndexOf(".csv") > 0) {
        $csvPath = "$CurrentLoc\$MergedFileName"
    }
    else {
        $csvPath = "$CurrentLoc\$MergedFileName.csv"
    }#
    Exporting the content as csv file
    $CSVContentList | Export - Csv - Path $csvPath
    Write - Host - ForegroundColor Green "Files merged successfully and exported at "
    $csvPath
}

The complete PowerShell script is attached to this article.