Powershell replacing Character at postion 12 in CSV file-CodePudding

my problem is to replace in a CSV-File with around 3000 lines at position 12 character " " ( space ) with character ",". Seems to be a easy task, but I stuck. I tried this

Example of CVS File:

03 Aug 2022 00:00:00,212526237600000000,.......................
03 Aug 2022 00:00:00,212526237600000001,.......................
03 Aug 2022 00:00:00,212526237600000002,.......................

My try:

$filename = "File"
$temfile = [System.IO.Path]::GetTempFileName()
Get-Content $filename |
Foreach($line in $filename )
{
   ($line.SubString(0,12) -replace " ",",") )
 } |
Add-Content $temfile
Remove-Item $filename
Move-Item $temfile $newfile

I will appreciate any helpful answer, thanks in advance Lightfoot

CodePudding user response：

The main issue is that you're piping Get-Content into foreach, a language keyword, instead of ForEach-Object, the cmdlet. Then piping the result of the loop to Add-Content. This will result into a syntax error, pipes cannot be at the beginning or end of a language keyword.

There is also an issue with your replacement logic, a simple example:

'03 Aug 2022 00:00:00,212526237600000000,..........'.SubString(0, 12) -replace ' ', ','

Will result in:

03,Aug,2022,

Which is clearly not what you wanted. This regex might do the what you're looking for: (?m)(?<=^.{11})\s.

For example given this multi-line string:

$string = @'
03 Aug 2022 00:00:00,212526237600000000,........
03 Aug 2022 00:00:00,212526237600000001,........
03 Aug 2022 00:00:00,212526237600000002,........
'@

The result would be:

PS ..\> $string -replace '(?m)(?<=^.{11})\s', ','

03 Aug 2022,00:00:00,212526237600000000,........
03 Aug 2022,00:00:00,212526237600000001,........
03 Aug 2022,00:00:00,212526237600000002,........

If you want to keep the white space after inserting the new comma, simply remove \s from the regex:

$string -replace '(?m)(?<=^.{11})', ','

As for how the code would look:

(Get-Content path\to\myfile.csv -Raw) -replace '(?m)(?<=^.{11})\s', ',' |
    Set-Content path\to\myNewFile.csv