0

We are receiving file from business users but they are changing delimited in text file which cause data loading process fails.

Is there a way I can identify delimited character using PowerShell Script between quotes and replace it with ^ ?

Get-Content $LatestFile | ForEach-Object { $_ -replace '"[~!@#$%&*()_+",>?/|\]"','"^"'} | Out-File result.txt

Original File (can be any delimited):
"ASSIGNMENT","RESPONSIBLE","NAME","NAME2" 
"ASSIGNMENT"|"RESPONSIBLE"|"NAME"|"NAME2"
"ASSIGNMENT"  "RESPONSIBLE"   "NAME"  "NAME2" 

Required Result:
"ASSIGNMENT"^"RESPONSIBLE"^"NAME"^"NAME2"
Justin
  • 393
  • 1
  • 7
  • 21
  • Have you got a small example of the original file and what you'd like it to look like? If it's a csv file you could potentially just use the ```-Delimiter``` parameter on ```Import-Csv``` and ```Export-Csv```... – mclayton Aug 27 '20 at 16:06
  • It's a text file. "ASSIGNMENT","RESPONSIBLE","NAME","NAME2" and so on. We are using PowerShell to get file from SFTP and using tsql to load file into database. – Justin Aug 27 '20 at 16:15
  • I’m still not really clear what you need - can you add an example file which demonstrates the problem, and what you need it to look like instead? (Add them to your question rather than the comments). – mclayton Aug 27 '20 at 16:37
  • @Justin rather than dumping code/data in comments, please [update the post](https://stackoverflow.com/posts/63619648/edit) with invalid AND valid input samples (ie. what might a file that needs correction look like, and what is the expected output format)? – Mathias R. Jessen Aug 27 '20 at 16:41
  • Your are on the good way, but some regex characters need to be escaped. See: [Is there a RegExp.escape function in Javascript?](https://stackoverflow.com/questions/3561493/is-there-a-regexp-escape-function-in-javascript) – iRon Aug 27 '20 at 17:00

1 Answers1

0
Get-Content $LatestFile | ForEach-Object { $_ -replace '"[\-\[\]{}()*+?.,\\\^$|#\s]"','"^"'} | Out-File result.txt

Thank you @iRon

Justin
  • 393
  • 1
  • 7
  • 21
  • You can escape the delimiters this way, too: ```$delimiters = "~!@#$%&*()_+`",>?/| "; $pattern = '"[' + [regex]::Escape($delimiters) + ']"'```. That way you don't end up quite so crossed eyed when you stare at all the backslashes :-) – mclayton Aug 27 '20 at 17:30
  • @mclayton Thank you so much, your suggestion is better and cleaner. – Justin Aug 27 '20 at 17:46