CODEWITHSUNDEEP
powershellcsv
Funtastic
Funtastic

Reputation: 13

Powershell copy a column from a csv file and paste into the first row of another csv file

I have a csv file named "final results.csv", there is a header row and some data. The header data are "Alias" with underscores, for example User_Name, User_Age, User_firstN, User_LastN

I have another csv file named "header names.csv" where the actual name of the headers are stored. It has one single column, so the data looks like this.

Header
User Name
User Age
First Name
Last Name

The goal is to extract the header actual names from the 2nd file, and paste them into the first row of the first file. So the first file ("final results.csv") has a header with actual header names as opposed to aliases.

How do I achived this using Powershell? Thanks in advance!

Upvotes: 1

Views: 1085

Answers (2)

Doug Maurer
Doug Maurer

Reputation: 8868

If I understand you right you have a CSV with header names you want to replace. Currently they are X_X and you want to replace them with X X. You can do it easily with Select-Object and calculated properties. Now if you have a lot of headers that could be a lot of manual work. Here's a way to take the headers and dynamically build the calculated properties.

Demo data.

# first file
$csv = @"
User_Name,User_Age,First_Name,Last_Name
ab,20,a,b
cd,21,c,d
ef,22,e,f
"@ |ConvertFrom-Csv

# second file
$headers = @"
header
User Name
User Age
First Name
Last Name
"@ | ConvertFrom-Csv

Build the calculated properties

$SelectProps = @($headers | ForEach-Object {
    @{n=$_.header;e={$($_.header.Replace('_',' '))}}
})

Here's what that should look like at this point

Name                           Value                                                                                                                                                          
----                           -----                                                                                                                                                          
n                              User Name                                                                                                                                                      
e                              User_Name                                                                                                                                                      
n                              User Age                                                                                                                                                       
e                              User_Age                                                                                                                                                       
n                              First Name                                                                                                                                                     
e                              First_Name                                                                                                                                                     
n                              Last Name                                                                                                                                                      
e                              Last_Name

Now we simply replace them.

$csv | Select-Object $SelectProps

User Name User Age First Name Last Name
--------- -------- ---------- ---------
ab        20       a          b        
cd        21       c          d        
ef        22       e          f

Making your code

$csv = Import-Csv "final results.csv"

$headers = Import-Csv "header names.csv"

$SelectProps =  @($headers | ForEach-Object {
    @{n=$_.header;e=($_.header.Replace(' ','_'))}
})

$csv | Select-Object $SelectProps |
    Export-Csv -Path "final results.csv" -NoTypeInformation

But you can also just replace the first line with Get-Content, Set-Content, and -join

$headers = Import-Csv "header names.csv"

Set-Content -Value $(
    $headers.header -join ','
    Get-Content "final results.csv" | Select-Object -Skip 1
) "final results.csv"

Import-Csv "final results.csv"

User Name User Age First Name Last Name
--------- -------- ---------- ---------
ab        20       a          b
cd        21       c          d
ef        22       e          f

Upvotes: 0

Steven
Steven

Reputation: 7087

If it were me I wouldn't bother reading in the CSV I'd simply work with it as text. All you're really doing is replacing the first line with the correct headers. Assuming the file isn't more complicated than described something like below should work:

# Get the headers:
$Headers = (Get-Content <2ndFile> | Select-Object -Skip 1) -join ','

# Combine with the CSV file skip the first row to accomplish the replace:
$Headers, @(Get-Content <1stFile> | Select-Object -Skip 1) | Set-Content <NewCsv>

Obviously, adjust file names as needed...

Upvotes: 1

Related Questions