Forum Discussion
Powershell is corrupting comma delimited files
My PowerShell script is adding double quotes and formatting the text incorrectly. I am removing lines that have a particular string.
This line created by PowerShell script
"RECTYPE,CNTBTCH,CNTITEM,IDVEND,IDINVC,TEXTTRX,DATEINVC,SWCALCTX,AMTGROSTOT,AMTINVCTOT"
This line is the original. No double quotes.
RECTYPE,CNTBTCH,CNTITEM,IDVEND,IDINVC,TEXTTRX,DATEINVC,SWCALCTX,AMTGROSTOT,AMTINVCTOT
Here they are for back-to-back comparison
“RECTYPE,CNTBTCH,CNTITEM,IDVEND,IDINVC,TEXTTRX,DATEINVC,SWCALCTX,AMTGROSTOT,AMTINVCTOT”
RECTYPE,CNTBTCH,CNTITEM,IDVEND,IDINVC,TEXTTRX,DATEINVC,SWCALCTX,AMTGROSTOT,AMTINVCTOT
The files are CSV but Excel puts the new file’s complete line in one cell as if it does not recognize a delimiter
This is the new line in Hex.
This is the original line
This is the .PS1
(get-content c:\exports\'Receiving Export SAGE.CSV'| select-string -pattern ' ,0,,,0,0' -notmatch) | Out-File c:\Exports\'Receiving Export SAGE Zeroes Gone.CSV'
JoyriderBC You can specify the encoding of the files with the -Encoding parameter.
For example:
Out-File c:\Exports\'Receiving Export SAGE Zeroes Gone.CSV' -Encoding utf8
5 Replies
JoyriderBC You can specify the encoding of the files with the -Encoding parameter.
For example:
Out-File c:\Exports\'Receiving Export SAGE Zeroes Gone.CSV' -Encoding utf8diecknet Thank you. Your suggestion fixed the two problems I was having. The updated script put a blank line as the first line in the output file. The input file does not have this blank line. The import into my program fails. How can the blank line be prevented?
Output file
Input file
Hi,
I try it from my side and its working perfect with no double quote when using the Out-File.
Are you saving the content from a custom PSObject.
I got the double quotes when I replace the Out-File with Export-CSV, and manage to remove it by using PowerShell 7
(get-content C:\Users\1.CSV ) | Export-Csv c:\Fixed.CSV -UseQuotes neverfarismalaeb The double quotes are minor problem. It is the record corruption. See the HEX dump where after every character there are unprintable codes.
- Can you share part of the output file, just put dump data in it and let me have a closer look.
Thanks
