Powershell Find and Replace

I am trying to find and replace 2 different text strings, I am using the following code:

Get-Content “C:\ATR\Karen\adminreport3.csv” | ForEach-Object {$_ -replace “44444444”,“IN PUNCH” } | Set-Content “C:\ATR\Karen\punchreport.csv” -Force

It works great, but I need to do one more find and replace before creating the output file. I need to find and replace “11111111” with OUT PUNCH, I cant figure out how to do it for both? Any help is greatly appreciated!

SOLVED I figured it out!

Joey,
Welcome to the forum. :wave:t4:

Great. :+1:t4:

Would you like to share your solution here? This might help other comming here with the same or a similar issue.

Thanks in advance.

BTW: When you post code, sample data, console output or error messages please format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

How to format code in PowerShell.org <---- Click :point_up_2:t4: :wink:

I was able to just to just repeat the
ForEach-Object {$_ -replace.
I do still have an issue though. The time format is coming in as HH:MM:SS.000, I need it to only show HH:MM. I can do a replace to remove the .000, but I cant figure out how to remove the :SS.

what it looks like now in the output file:
197613,2022-11-01 07:10:09.000,IN PUNCH
It needs to be
197613,2022-11-01 07:10,IN PUNCH.

I cant figure out how to identify the variable numbers behind the last colon to remove them. Any insight is greatly appreciated.

Hmmm … you know you can chain multiple -replace operators, don’t you?

'arbitrary striing with some uniqque typoos' -replace 'ii', 'i' -replace 'qq', 'q' -replace 'oo', 'o'

I’d recommend to actually convert the string to a proper [DateTime] type …

$TimeSting = '2022-11-01 07:10:09.000'
[datetime]::ParseExact($TimeSting,'yyyy-MM-dd HH:mm:ss.fff',[cultureinfo]::InvariantCulture).toString('HH:mm')

Please … when you post code, sample data, console output or error messages format it as code using the preformatted text button ( </> ). Simply place your cursor on an empty line, click the button and paste your code.

Thanks in advance

How to format code in PowerShell.org <---- Click :point_up_2:t4: :wink:

Olaf, thank you very much, I am very new to Powershell and am just figuring some of this out. I was not sure how to chain the multiple -replace operators, so I thank you for that. I am looking at the code you sent to convert the DateTime, but I am not sure exactly where to put it in my script?
Here is my script, and I am sure it is a mess, but I am getting it lined out as best as I can.

sqlcmd -h-1 -S ZARIRIS\IRIS -d IA3000SDB -Q "SET NOCOUNT ON; Select Distinct TTransactionLog_1.DecisionTimeInterval,
TTransactionLog_1.UserID, TTransactionLog_1.OccurDateTime, TTransactionLog_1.StableTimeInterval

From TTransactionLog_1
Inner join TSystemLog1 On TTransactionLog_1.NodeID=TSystemLog1.NodeID
Inner join TUser On TTransactionLog_1.UserID=Tuser.UserID
where TSystemLog1.NodeID = 3 and TTransactionLog_1.OccurDateTime >= dateadd(HOUR, -12, getdate())" -s  "," -W -o  "C:\atr\karen\adminreport3.csv"



Get-Content "C:\ATR\Karen\adminreport3.csv" | ForEach-Object {$_ -replace "44444444","IN PUNCH"} | ForEach-Object {$_ -replace "11111111","OUT PUNCH"} | Set-Content "C:\ATR\Karen\punchreport1.csv" -Force

Get-Content "C:\ATR\Karen\punchreport1.csv" | ForEach-Object TrimStart("0",",") | Set-Content "C:\ATR\Karen\punchfinal.csv"
Get-Content "C:\ATR\Karen\adminreport3.csv" | 
    ForEach-Object {
        $_ -replace '44444444', 'IN PUNCH' -replace '11111111', 'OUT PUNCH'
    } | 
        Set-Content "C:\ATR\Karen\punchreport1.csv" -Force

Since you do not share any information where you want to change the format of the date & time I cannot recommend something meaningful. :man_shrugging:t4:

In general - if you have a proper CSV file you should treat it as such and use

instead of Get-Content. :point_up_2:t4:

1 Like

Thank you, I will try to figure out the Import-Csv. I apologize for my lack of terminology, this is my first attempt at this. I am not sure how to answer your question about where i want to change the format, It can be changed on any of the output files, it would be best for it to be changed where it is first created I would think

where TSystemLog1.NodeID = 3 and TTransactionLog_1.OccurDateTime >= dateadd(HOUR, -12, getdate())" -s  "," -W -o  "C:\atr\karen\adminreport3.csv"

I tried using Format on the above line after getdate, but I was never able to get anything to work

That’s a very good idea. I just cannot help you with that because I don’t know SQL. :man_shrugging:t4:

Do you do further steps with the CSV files you create with this code? If yes - you could tweak the date/time cell during this process.

Yes, after that file is generated by sql, I am using powershell to trim and replace some items in the file, and then generate a new one with the replaced data:

Get-Content "C:\ATR\Karen\adminreport3.csv" | ForEach-Object {$_ -replace "44444444","IN PUNCH"} | ForEach-Object {$_ -replace "11111111","OUT PUNCH"} | Set-Content "C:\ATR\Karen\punchreport1.csv" -Force

Get-Content "C:\ATR\Karen\punchreport1.csv" | ForEach-Object TrimStart("0",",") | Set-Content "C:\ATR\Karen\punchfinal.csv"

I have tried doing it here, but couldnt figure it out

!!! User Import-Csv and Export-Csv !!!

Could you please share some (sanitized) lines of the CSV file you want to tweak? (use the original format and format it as code please)