0

I've never utilised Powershell before but I'm trying to set up an easy way of removing text qualifiers that might cause skewing ( " / ' / "/,) while not having to manually go into each file to remove them. These files are either in .txt or .csv format.

Would like it to basically loop through all files at a in a filepath, remove the text qualifiers and then save the files.

Sample:

ID  |Description     |Date
================================
0001|"xasfdsghdfshdf"|05/02/2022
0002|abc "xyz" abc   |31/01/2022
0003|abcde"          |01/02/2022 
0004|"               |01/06/2022

Expected output:

ID  |Description   |Date
==============================
0001|xasfdsghdfshdf|05/02/2022
0002|abc xyz abc   |31/01/2022
0003|abcde         |01/02/2022 
0004|              |01/06/2022

Found a bit of script in an archive that ~should~ technically do this, but for the life of me I can't make it work. Would much appreciate any help! :)

$Folder = (Get-ChildItem -Path "YOUR_FOLDER_HERE" -Recurse -Filter *.CSV).FullName
foreach ($File in $Folder) {[System.IO.File]::WriteAllText("$File", [System.IO.Files]::ReadAllText("$File").Replace("`"",""))}

# Method 1 - Non-scalable
###foreach ($File in $Folder) { (Get-Content $File).replace("""," ") | Set-Content $File }
Shireeve
  • 11
  • 5
  • Please post a sample text file and desired output. – Mathias R. Jessen Sep 01 '22 at 12:02
  • Both methods look good, so what is failing or doesn't work? Can you give more details on that? – Santiago Squarzon Sep 01 '22 at 13:08
  • Removing text qualifiers (`"`) in CSV files is hazardous as the quote(s) may be part of the field value itself. Also, if a field contains the csv file's delimiter character, this will lead to field-shifting making the csv file unusable. See [here](https://stackoverflow.com/a/60681762/9898643) – Theo Sep 01 '22 at 13:20
  • Your ```ReadAllText``` version replaces the ```"``` character with an empty string, whereas the ```Get-Content``` version replaces it with a space. From your example output it looks like you want ```[System.IO.Files]::ReadAllText("$File").Replace("`""," "))``` to preserve column alignment - i.e. the second parameter to ```Replace``` is a space (```" "```), not an empty string (```""```) – mclayton Sep 01 '22 at 19:24

0 Answers0