Thursday, May 14, 2015

Powershell - not good for pipe-separated files. At all.

(I swore there was an easy way - yeah, use Cygwin).

Needed to remove a few fields from a pipe-separated file.  No problem, right?  Import it via import-csv, SELECT out the fields needed, then use export-csv.

Try 1:
import-csv "C:\temp\TEST.csv" -Delimiter "|" |
select a, c, d, e, g |
 export-csv "C:\temp\TEST2.csv" -Delimiter |

Nope.  Doesn't like the Pipe.  Throws an error.

Try 2: put the pipe in quotes. Notype info otherwise you get a pre-header line with posh info.
 import-csv "C:\temp\TEST.csv" -Delimiter "|" |
select a, c, d, e, g |
export-csv "C:\temp\TEST2.csv" -Delimiter "|" -NoTypeInformation

Nope, looks like it's adding the quotes to everything.  

(aborted tries trying to use a different character than pipe and substitute - hard because almost every character is used!)

Try 5: Oh, I like this.  Use the ASCII value of the pipe symbol
import-csv "C:\temp\TEST.csv" -Delimiter "|" |
select a, c, d, e, g |
 export-csv "C:\temp\TEST2.csv" -Delimiter "$([char]0x7C)" -NoTypeInformation

Nope, same thing - quotes. Huh?

Yup, turns out that export-csv will only produce MS's version of a comma-separated file, where every value is quoted.  Which I don't want.  And there's no switch to get rid of.  And I can't just replace the double quotes because my source data has some in it.  Thanks for nothing.  vote on a connect item to fix.

Back to Cygwin for now:
cut -d\| -f1,3,4,5,7 C:\temp\TEST.csv > c:\temp\TEST2.csv

No comments: