Put Comma (,) after every word in csv cell using powershell

Question

I have been asked to create a script to put comma (,) after every word in csv cell using powershell. However the actual csv has not been shared with me due to some security reason.

I have created a test csv and tried to put comma (,) after every word (Screen shot 1). I am able to create the script and it is working 100% fine (Screen shot 2). However the actual file size is approx 250 MB. I am assuming if that file has 100s of columns, do I have to mention each column name in the script as I mentioned in the foreach loop. Is there any easy way to accomplish this task without mentioning each column name. Any help would be appreciated. Below is my script that is working fine:

$csv = Import-Csv -Path C:\temp\test.csv
$outcsv = Read-Host "Enter the name of the output csv"     
$outfile  = "$env:USERPROFILE\Downloads" + "" + $outcsv + ".csv"
$data = @()

foreach ($item in $csv) {

        $col1 = $item.Name + ","
        $col2 = $item.Location  + ","
        $col3 = $item.Company  + ","
        $col4 = $item.Profile  + ","
        $col5 = $item.Shift  + ","

        $Properties = [ordered]@{
                'Name'      = $col1
                'Location'  = $col2
                'Company'   = $col3
                'Profile'   = $col4
                'Shift'     = $col5
        }
$data += New-Object -TypeName PSObject -Property $Properties
}

$data | Export-Csv -Path $outfile -NoTypeInformation

Screen shot 1:

Screen shot 2 with comma (,):

Is the input really already going to be csv (containing commas already) or are you trying to turn a text file into csv using this method? If it is already csv, do you know what the use case is for adding additional commas to the end of each value? — Daniel
– Daniel, Commented Jan 21, 2023 at 18:35
@Daniel: Yes, the input is already csv and I am putting commas after each word and output is also in csv. No text file involved. Referring your answer on use case. This csv is given to me by dba sql team. They need commas after every word so that they can put the data in MSSQL. Apart from this, I am not aware of any other reason. I am in windows team. Thanks. — JPS
– JPS, Commented Jan 22, 2023 at 8:34
Do the specs for your work include enclosing each field in double quotes? Export-Csv will do that for you. But if the DBAs do not want that done, then thety will say your output is incorrect. This relates to @Daniel question about the use case. — Walter Mitty
– Walter Mitty, Commented Jan 22, 2023 at 12:38

Santiago Squarzon · Accepted Answer · 2023-01-21 18:22:33Z

2

This is an easier way to go about it that could handle the logic dynamically by accessing the object's PSObject.Properties.

$outcsv = Read-Host "Enter the name of the output csv"
if(-not [System.IO.Path]::GetExtension($outcsv)) {
    $outcsv = $outcsv + '.csv'
}
$outfile = Join-Path "$env:USERPROFILE\Downloads" -ChildPath $outcsv

Import-Csv -Path C:\temp\test.csv | ForEach-Object { $firstObject = $true } {
    if($firstObject) {
        # get the property names of the first object
        $properties = $_.PSObject.Properties.Name
        $firstObject = $false
    }

    # enumerate each property of the object
    foreach($property in $properties) {
        # update the value
        $_.$property = $_.$property + ','
    }

    # output the updated object
    $_
} | Export-Csv $outfile

edited Jan 21, 2023 at 18:22

answered Jan 21, 2023 at 18:10

Santiago Squarzon

65.7k5 gold badges26 silver badges60 bronze badges

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

Collectives™ on Stack Overflow

Put Comma (,) after every word in csv cell using powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related