1

Hi,

I have a CSV file with 25 columns. I need to add [ and ] to the beginning and end respectively to each value in column 2 and in column 9. The values of column 2 will always be 12 digits and in column 9, values can either be 19 digits long or 18 digits long.

I tried the following to start:

import-csv $cardExpiryFile | Select-Object *,@{Expression={"[$($_.column2)]"}} | export-csv -notypeinformation $newCardExpiryFile -Append

But it only changed the value in column 2, row 2.

I then tried:

import-csv $cardExpiryFile | ForEach-Object {

    Select-Object *,@{Expression={"[$($_.column2)]"}} | export-csv -notypeinformation $newCardExpiryFile -Append
    
}

But the resulting csv file was blank.

I then tried the following just to see what was being returned without exporting it to a CSV. I stepped through the code and see that the $changeddata variable is always blank.

import-csv $cardExpiryFile | ForEach-Object {

   $changeddata = Select-Object *,@{Expression={"[$($_.column2)]"}} 
    
}

Any ideas?

5 Spice ups
2 
Import-Csv -Path "$PSScriptRoot\2330004.csv" | ForEach-Object {
    $_.column2 = "[$($_.column2)]" #set the new value of column2 from "value" to "[value]"
    $_.column9 = "[$($_.column9)]" #set the new value of column9 from "value" to "[value]"
    $_ # Export-CSV requires an object when piped so we 'output' our object.
} | Export-Csv -Path "$PSScriptRoot\Modified.csv" -NoTypeInformation
1 Spice up
3

That is perfect! That did the trick! Thank you so much!

@mike9019

4

Hi,

I actually have another question…not sure if a new thread is required or not…I now need to exclude two columns from the CSV in addition to adding the square brackets. The two columns that need to be removed is column2 and column4. I’ve removed columns for a previous solution, but am not sure how to marry it with code to add the square brackets.

I’m passing in the columns in a comma separated string as a parameter and then splitting them into an array. The parameter looks like “column2,column4” and the paramter variable is $excludeColumns.

$excludeColumnsArray = $excludeColumns -split ","

In my other solution I simply imported the CSV, excluding the columns, and then exported the data back to a CSV.

$NewCSV = Import-Csv $AuraFileFullPath | select-object * -ExcludeProperty $ExcludeColumnsArray -Verbose
$NewCSV | Export-Csv -NoTypeInformation -LiteralPath $AuraFileFullPath -Verbose

For this solution, I tried the following:

Import-Csv -Path $cardExpiryFile | select-object * -ExcludeProperty $excludeColumnsArray | ForEach-Object {

            $_.column9 = "[$($_.column9)]"  #set the new value of column9 from "value" to "[value]"
            $_ # Export-CSV requires an object when piped so we 'output' our object. 
        } | Export-Csv -Path $newCardExpiryFile -NoTypeInformation

When I step through the code and view the values in $_, I see the two columns are removed and column9 is enclosed in square brackets, but the resulting CSV file is empty.

I also tried

Import-Csv -Path $cardExpiryFile | ForEach-Object {

            $_.column9 = "[$($_.column9)]"  #set the new value of column9 from "value" to "[value]"
            $_ # Export-CSV requires an object when piped so we 'output' our object. 
        } | select-object * -ExcludeProperty $excludeColumnsArray | Export-Csv -Path $newCardExpiryFile -NoTypeInformation

With the same result. I could import the CSV and exclude the columns and then save the file and then re-import it and add the square brackets to column9, but I’m sure there is a much more elegant way to do this.

@mike9019

5

Sorry but you’ll have to do more debugging - this works as expected;

$csvFile = "$PSScriptRoot\2330004-data.csv"
<#2330004-data.csv contents (created in Excel) ....
column1,column2,column8,column9,column10
c1_r1,c2_r1,c8_r1,c9_r1,c10_r1
c1_r2,c2_r2,c8_r2,c9_r2,c10_r2
c1_r3,c2_r3,c8_r3,c9_r3,c10_r3
c1_r4,c2_r4,c8_r4,c9_r4,c10_r4
c1_r5,c2_r5,c8_r5,c9_r5,c10_r5
#>
$newCardExpiryFile = "$PSScriptRoot\modified_output.csv"
$excludeColumns = "column2,column10" #made an assumption on what this was.
$excludeColumnsArray = $excludeColumns -split ","

Import-Csv -Path $csvFile | Select-Object * -ExcludeProperty $excludeColumnsArray | ForEach-Object {
    $_.column9 = "[$($_.column9)]"
    $_
} | Export-Csv -Path $newCardExpiryFile -NoTypeInformation

<#$newCardExpireFile => modified_output.csv contents...
"column1","column8","column9"
"c1_r1","c8_r1","[c9_r1]"
"c1_r2","c8_r2","[c9_r2]"
"c1_r3","c8_r3","[c9_r3]"
"c1_r4","c8_r4","[c9_r4]"
"c1_r5","c8_r5","[c9_r5]"
#>

What’s $newCardExpiryFile set to? If this is also built using variables make sure your variables are being declared or result in expected values. In my example above I’m using $PSScriptRoot to build my path for the $newCardExpiryFile - if I changed that to something like "$PSScriptRootNotExist\modified_output.csv " (an undeclared variable) the file would output to “\modified_output.csv” which from a Windows perspective translates to a location directly on the C:\ drive.

To help with debugging you could do assign Import-Csv to a variable, remove the export-csv pipe and then look at the assigned variable data. if it’s correct pipe the variable to Export-Csv - like so…

$csvFile = "$PSScriptRoot\2330004-data.csv"

$newCardExpiryFile = "$PSScriptRoot\modified_output.csv"
$excludeColumns = "column2,column10" #made an assumption on what this was.
$excludeColumnsArray = $excludeColumns -split ","

$modifiedData = Import-Csv -Path $csvFile | Select-Object * -ExcludeProperty $excludeColumnsArray | ForEach-Object {
    $_.column9 = "[$($_.column9)]"
    $_
}

if (-not($modifiedData)) {
    "`$modifiedData is empty...something went wrong."
    $modifiedData
}
else {
    #show me $modifiedData in the host console...
    $modifiedData
    #looks good? Alright - export to CSV
    $modifiedData | Export-Csv -Path $newCardExpiryFile -NoTypeInformation
}

#make sure to restart your session host or variable data could linger, maybe even remove your variables like $modifiedData so on the next script run it's cleared.  Important when using an IDE like VS Code.
Remove-Variable -Name modifiedData -ErrorAction Ignore
6

Hmmm…I tried it again and this time it worked…The $cardExpiryFile is the path, including the CSV file, on a server. The $newCardExpiryFile is the path to local drive i.e. E:\temp\card_expiry_with_addresses.csv. I double checked and the values assigned to the variables were correct so I don’t know what to say…I did change the code to import the csv and exclude the columns, export it back out, then import it back in to add the brackets as a workaround. I then changed it back to what I had in my first code example and ran it again…this time it worked. Thank you for taking the time to work through this with me. Much appreciated!

@mike9019