CODEWITHSUNDEEP
powershell
Pablo_Beezo
Pablo_Beezo

Reputation: 33

Remove white space from the 5th column only in a text file

I have a text file that comes in from an API and i need to remove the white space from a particular column only (5th Column)

example:

"Number"|"Message"|"Reference"|"SendFrom"|"CampaignName"
"4478xxxxxxxx"|"Test message from test system"|"104"|"dunno"|"Campaign Name Here 2"
"4479xxxxxxxx"|"Test message from test system"|"105"|"dunno"|"Campaign Name Here 2"

I need the output to be as follows:

"Number"|"Message"|"Reference"|"SendFrom"|"CampaignName"
"4478xxxxxxxx"|"Test message from test system"|"104"|"dunno"|"CampaignNameHere2"
"4479xxxxxxxx"|"Test message from test system"|"105"|"dunno"|"CampaignNameHere2"

so only the last column has the white space removed from it and not the rest of the file.

I was able to isolate the last column and remove the space:

$columnToGet4 = 4
$columns4 = gc $Report | 
   %{ $_.Split("|",[StringSplitOptions]"RemoveEmptyEntries")[$columnToGet4] }

$columns4 = $columns4 -replace '\s',''

but trying to knit the data back together was not working.

any advice on how this can be accomplished?

thanks

Upvotes: 3

Views: 53

Answers (4)

f6a4
f6a4

Reputation: 1782

This is a one-liner:

Import-Csv -Path $source -Delimiter '|' -Encoding UTF8 | % { $_.CampaignName = $_.CampaignName.Replace(' ',''); $_ } | Export-Csv $dest -NoTypeInformation -Delimiter '|'

Upvotes: 0

DarkLite1
DarkLite1

Reputation: 14695

Maybe this makes it a bit more clear on what is happening:

$Report = Get-Content ./Data.txt

$Result = foreach ($R in $Report) {
    # Split the original text string into pieces, an array
    $OriginalArray = $R.Split('|')

    # Only execute when there are 4 elements
    if ($OriginalArray.Count -ge 5) {
       # Remove the spaces of the 4th element in the array
       $UpdatedValue = $OriginalArray[4] -replace '\s'

       # Update the original value
       $OriginalArray[4] = $UpdatedValue
    }

    # Join the array back together into one string
    $OriginalArray -join '|'
}

$Result

# Export the data to a file
$Result | Out-File .File.txt -Encoding UTF8

Upvotes: 0

Lee_Dailey
Lee_Dailey

Reputation: 7479

presuming that the pipe symbols are the delimiters and not just a display marker, this does the job ... [grin] 

# fake reading in a CSV file
#    in real life, use Import-CSV -Delimiter '|'
$InStuff = @'
"Number"|"Message"|"Reference"|"SendFrom"|"CampaignName"
"4478xxxxxxxx"|"Test message from test system"|"104"|"dunno"|"Campaign Name Here 2"
"4479xxxxxxxx"|"Test message from test system"|"105"|"dunno"|"Campaign Name Here 2"
'@ | ConvertFrom-Csv -Delimiter '|'

foreach ($IS_Item in $InStuff)
    {
    $IS_Item.CampaignName = $IS_Item.CampaignName.Replace(' ', '')
    }

# on screen
$InStuff

# send to CSV file
$ECSV_Params = @{
    LiteralPath = "$env:TEMP\Pablo_Beezo_-_DeSpacedCampaignNameVersion.csv"
    Delimiter = '|'
    NoTypeInformation = $True
    }
$InStuff |
    Export-Csv @ECSV_Params

onscreen ... 

Number       : 4478xxxxxxxx
Message      : Test message from test system
Reference    : 104
SendFrom     : dunno
CampaignName : CampaignNameHere2

Number       : 4479xxxxxxxx
Message      : Test message from test system
Reference    : 105
SendFrom     : dunno
CampaignName : CampaignNameHere2

CSV file content ... 

"Number"|"Message"|"Reference"|"SendFrom"|"CampaignName"
"4478xxxxxxxx"|"Test message from test system"|"104"|"dunno"|"CampaignNameHere2"
"4479xxxxxxxx"|"Test message from test system"|"105"|"dunno"|"CampaignNameHere2"

Upvotes: 1

TessellatingHeckler
TessellatingHeckler

Reputation: 28963

You can possibly use PowerShell's CSV handling for this:

# Import the file as if it was a CSV separated by the pipe,
# and process each row

Import-Csv -LiteralPath 'c:\temp\input.txt' -Delimiter '|' | 
ForEach-Object {

    # Replace spaces in the campaignName column, by name
    $_.CampaignName = $_.CampaignName -replace '\s'

    # and output the changed row item, for export to file
    $_

} | Export-Csv -LiteralPath 'c:\temp\output.txt' -Delimiter '|' -NoTypeInformation

Or plain text handling and regex replace:

Get-Content -LiteralPath 'c:\temp\input.txt' | ForEach-Object {
    $_ -replace '\s(?=[^|]+$)'
} | Set-Content -LiteralPath 'c:\temp\output.txt' -Encoding ASCII

Where the regex picks out "spaces where there are no more pipes on the line afterwards" (might not be a valid assumption).

Or you could use plain text handling, picking out where the last pipe character is:

Get-Content -LiteralPath 'c:\temp\input.txt' | foreach-object {

    $afterLastPipe = $_.lastindexof('|')+1

    $_.Substring(0, $afterLastPipe) + $_.Substring($afterLastPipe).Replace(' ', '')

} | Set-Content...

Again, might not be a valid assumption that there are no more pipes, especially if there could be one inside the quotes.

Upvotes: 1

Related Questions