CODEWITHSUNDEEP
powershell
chrtak
chrtak

Reputation: 75

Trouble importing CSV and then exporting with some manipulation

I have the following file:

2018|CIB_C21_A_1_1_FromRangeAmount|.0000

As you can see, it has no headers. The first column can be ignored. I need to take the 2nd column and then export but I also need to add (2) additional columns in the export.

The export should look like this:

Account,Parent,Data Storage

CIB_C21_A_1_1|CIB_C21_A|Never Share

As you can see, I need to take the first column and also use the first 9 chracters as a value for another column. Then I need to add another column with the same value at all times.

I have been trying to play around with this some of this logic but can't quote any anything to work...

Import-Csv -Path "C:\TEMPCT\Revenue_RebatesDataForPlanning.csv" -Header zz,Account -Delim "|" |
sort "Account" –Unique | 
Select "Account", "Parent" | 
Export-Csv -Path "C:\TEMPCT\Revenue_RebatesDataForPlanningzz.csv" -Force -NoTypeInformation

Could anyone recommend some suggestions?

Upvotes: 1

Views: 60

Answers (1)

Santiago Squarzon
Santiago Squarzon

Reputation: 60060

You can use calculated properties with Select-Object to create the desired new columns. To get the 9 first characters of the value from "Account" column you can use the String.Substring Method.

# This would be:
# Import-Csv path/to/csv.csv -Header Ignore, Account -Delimiter '|' | ... 
@'
2018|CIB_C21_A_1_1_FromRangeAmount|.0000
2018|CIB_C22_A_1_1_FromRangeAmount|.0000
2018|CIB_C23_A_1_1_FromRangeAmount|.0000
2018|CIB_C24_A_1_1_FromRangeAmount|.0000
2018|CIB_C25_A_1_1_FromRangeAmount|.0000
'@ | ConvertFrom-Csv -Header Ignore, Account -Delimiter '|' |
Select-Object Account, @{
    Name = 'Parent'
    Expression = { $_.Account.Substring(0,9) }
}, @{
    Name = 'Data Storage'
    Expression = { 'Never Share' }
} | ConvertTo-Csv -NoTypeInformation

How your code should look:

Import-Csv path/to/csv.csv -Header Ignore, Account -Delimiter '|' |
Select-Object Account, @{
    Name = 'Parent'
    Expression = { $_.Account.Substring(0,9) }
}, @{
    Name = 'Data Storage'
    Expression = { 'Never Share' }
} | Export-Csv path/to/newCsv.csv -NoTypeInformation

Upvotes: 1

Related Questions