Powershell - Insert column in between specific columns in csv file

Question

I have 2 csv files

First file:

firstName,secondName
1234,Value1
2345,Value1
3456,Value1
4567,Value3
7645,Value3

Second file:

firstName,fileSplitter,Csv2ColumnOne,Csv2ColumnTwo,Csv2ColumnThree
1234,,1234,abc,Value1
1234,,1234,asd,Value1
3456,,3456,qwe,Value1
4567,,4567,mnb,Value1

I want to insert column secondName in the second file in between columns firstName and fileSplitter.

The result should look like this:

firstName,secondName,fileSplitter,Csv2ColumnOne,Csv2ColumnTwo,Csv2ColumnThree
1234,Value1,,1234,abc,Value1
1234,Value1,,1234,asd,Value1
3456,Value1,,3456,qwe,Value1
4567,Value3,,4567,mnb,Value1

I'm trying the following code:

Function InsertColumnInBetweenColumns
{
Param ($FirstFileFirstColumnTitle, $firstFile, [string]$1stColumnName, [string]$2ndColumnName, [string]$columnMergedFileBeforeInput)

Write-Host "Creating hash table with columns values `"$1stColumnName`" `"$2ndColumnName`" From $OimFileWithMatches"
$hashFirstFileTwoColumns = @{}
Import-Csv $firstFile | ForEach-Object {$hashFirstFileTwoColumns[$_.$1stColumnName] = $_.$2ndColumnName}
Write-Host "Complete."

Write-Host "Appending Merge file with column `"$2ndColumnName`" from file $secondCsvFileWithLocalPath"
Import-Csv $outputCsvFileWithLocalPath | Select-Object $columnMergedFileBeforeInput, @{n=$2ndColumnName; e={
if ($hashFirstFileTwoColumns.ContainsKey($_.$FirstFileFirstColumnTitle)) {
    $hashFirstFileTwoColumns[$_.$FirstFileFirstColumnTitle]
} Else {
    'Not Found'
}}}, * | Export-Csv "$outputCsvFileWithLocalPath-temp" -NoType -Force
Move-Item "$outputCsvFileWithLocalPath-temp" $outputCsvFileWithLocalPath -Force
Write-Host "Complete."
Write-Host ""
}

This function will be called in a for loop for each column found in the first file (can contain an indefinite number). For testing, I am only using 2 columns from the first file.

I'm getting an error output resulting the following:

Select : Property cannot be processed because property "firstName" already exists.
At C:\Scripts\Tests\Compare2CsvFilesOutput1WithMatchesOnly.ps1:490 char:43
+     Import-Csv $outputCsvFileWithLocalPath | Select $columnMergedFileBeforeInput, @ ...
+                                              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (@{firstName=L...ntName=asdfas}:PSObject) [Select-Object], PSArgume
   ntException
    + FullyQualifiedErrorId : AlreadyExistingUserSpecifiedPropertyNoExpand,Microsoft.PowerShell.Commands.SelectObjectC
   ommand

I know the issue is where it says Select-Object $columnMergedFileBeforeInput,.

How can I get the loop statement to insert the column in between the before column (name is specified), and append the rest using *?

Update

Just an fyi, changing this line Select-Object $columnMergedFileBeforeInput, @{n=$2ndColumnName..... to this line Select-Object @{n=$2ndColumnName..... works, it just attaches the columns out of order. That is why I'm trying to insert the column in between. Maybe if i do it this way but insert the columns in backwards using the for loop, this would work...

Your expected output makes no sense. File 1 has 5 records, the last refers to Value3 being associated with 7645. File 2 has 4 records, and does not reference either of those values, but somehow your expected output includes Value3 with the fourth record. Please update the question with more reasonable examples. — TheMadTechnician
– TheMadTechnician, Commented Jan 13, 2016 at 1:15
Are you simply going line-by-line? As in Record 2 from File1 will be merged with Record 2 in File2 regardless of the content? Will both files have the same number of records? This may be a job for a For($i=0;$i -lt $file1.count;$i++){} kind of loop. — TheMadTechnician
– TheMadTechnician, Commented Jan 13, 2016 at 1:18
updated first file in question. amount of records in both files can either be same or different. — Fiddle Freak
– Fiddle Freak, Commented Jan 13, 2016 at 1:19
Your error states that you are trying to have two properties with the name 'firstname'. It would appear that you are effectively doing the following once your variables are expanded: Select firstname,firstname,* So if there is a conflict in property names, what should win? — TheMadTechnician
– TheMadTechnician, Commented Jan 13, 2016 at 1:35
Yes, the column firstName beforehand was added in test2.csv file to match the values with the column Csv2ColumnOne. Now that the value matches found has been added as the first column firstName in the test2.csv file, we need to insert the 2nd column from test1.csv file to match the row from the first column firstName in test2.csv. — Fiddle Freak
– Fiddle Freak, Commented Jan 13, 2016 at 1:46

xXhRQ8sD2L7Z · Accepted Answer · 2016-01-13 01:52:13Z

2

Not sure if this is the most efficient way to do it, but it should do the trick. It just adds the property to the record from file2, then reorders the output so secondName is the second column. You can output results to csv where required too (ConvertTo-Csv).

$file1 = Import-Csv -Path file1.csv
$file2 = Import-Csv -Path file2.csv

$results = @()
ForEach ($record In $file2) {
   Add-Member -InputObject $record -MemberType NoteProperty -Name secondName -Value $($file1 | ? { $_.firstName -eq $record.firstName } | Select -ExpandProperty secondName)
   $results += $record
}

$results | Select-Object -Property firstName,secondName,fileSplitter,Csv2ColumnOne,Csv2ColumnTwo,Csv2ColumnThree

answered Jan 13, 2016 at 1:52

xXhRQ8sD2L7Z

1,7261 gold badge14 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Fiddle Freak Over a year ago

issue with this is if the values from the first columns are out of order, this will print a missmatch in values. Also the values can't be hardcoded due to both files having changeable column names and amount of columns.

xXhRQ8sD2L7Z Over a year ago

This condition should take care of rows being out of order: -Value $($file1 | ? { $_.firstName -eq $record.firstName } | Select -ExpandProperty secondName). The data having no column can be taken care of as well. One method would be the -Header property of Import-Csv

xXhRQ8sD2L7Z Over a year ago

But if columns are out of order, and you don't which column is which because they have no header either - that is a problem.

Fiddle Freak Over a year ago

indeed, that is why using get-content is very messy, and I prefer the import-csv method. I updated my question with a finding. Might want to take a look.

Johan de Haan · Accepted Answer · 2016-01-13 13:36:46Z

I've created the following function. What it does is find the match (in this case "firstname") and adds the matching columnname to the new array afther the columnname on which the match is made (little difficult to explain in my poor English).

function Add-ColumnAfterMatchingColumn{
[CmdletBinding()]
param(
    [string]$MainFile,
    [string]$MatchingFile,
    [string]$MatchColumnName,
    [string]$MatchingColumnName
)

# Import data from two files
$file1 = Import-Csv -Path $MainFile
$file2 = Import-Csv -Path $MatchingFile

# Find column names and order them
$columnnames = $file2 | gm | where {$_.MemberType -like "NoteProperty"} | Select Name | %{$_.Name}
[array]::Reverse($columnnames)

# Find $MatchColumnName index and put the $MatchingColumnName after it
$MatchColumnNameIndex = [array]::IndexOf($columnnames, $MatchColumnName)
if($MatchColumnNameIndex -eq -1){
    $MatchColumnNameIndex = 0
}
$columnnames = $columnnames[0..$MatchColumnNameIndex] + $MatchingColumnName + $columnnames[($MatchColumnNameIndex+1)..($columnnames.Length -1)]

$returnObject = @()
foreach ($item in $file2){
    # Find corresponding value MatchingColumnName in $file1 and add it to the current item
    $item | Add-Member -Name "$MatchingColumnName" -Value ($file1 | ?{$_."$($MatchColumnName)" -eq $item."$($MatchColumnName)"})."$MatchingColumnName" -MemberType NoteProperty

    # Add current item to the returnObject array, in the correct order
    $newItem = New-Object psobject
    foreach ($columnname in [string[]]$columnnames){
        $newItem  | Add-Member -Name $columnname -Value $item."$columnname" -MemberType NoteProperty 
    }
    $returnObject += $newItem
}
return $returnObject
}

When you run this function you will have the following output:

Add-ColumnAfterMatchingColumn -MainFile C:\Temp\file1.csv -MatchingFile C:\Temp\file2.csv -MatchColumnName "firstname" -MatchingColumnName "secondname" | ft

firstName secondname fileSplitter Csv2ColumnTwo Csv2ColumnThree Csv2ColumnOne
--------- ---------- ------------ ------------- --------------- -------------
1234      Value1                  abc           Value1          1234         
1234      Value1                  asd           Value1          1234         
3456      Value1                  qwe           Value1          3456         
4567      Value3                  mnb           Value1          4567

Collectives™ on Stack Overflow

Powershell - Insert column in between specific columns in csv file

Update

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related