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!
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.
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.
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.
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.
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
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: