-2

I have a CSV file with delimiter as | , the values between the delimiter are not enclosed between quotes or any character. I am trying out a way to accept this file as an input and export a new CSV file with all the column to be enclosed in ^

Example:

Input:

1|Test|3

Output:

^1^|^Test^|^3^

Have tried below code but in vain:

get-content C:\ip.csv | foreach-object { $_ -replace '|' ,'^'} | set-content C:\op.csv
Filburt
  • 17,626
  • 12
  • 64
  • 115
Joseph
  • 530
  • 3
  • 15
  • 37
  • You should check out [Import-Csv](https://technet.microsoft.com/en-us/library/ee176874.aspx) and [Export-Csv](https://technet.microsoft.com/en-us/library/ee176825.aspx) instead. – Filburt Jun 23 '17 at 08:05
  • Possible duplicate of [How to convert text file to csv in powershell?](https://stackoverflow.com/questions/43927080/how-to-convert-text-file-to-csv-in-powershell) – Filburt Jun 23 '17 at 08:07
  • Thanks. That's true but Export-Csv by default enclosed double quote between values, I instead need the values to be enclosed between ^ – Joseph Jun 23 '17 at 08:19
  • Using `Import-Csv` at least you can get rid of the brittle `-replace`. – Filburt Jun 23 '17 at 08:46
  • You could try adopting from [ConvertTo-Csv Output without quotes](https://stackoverflow.com/a/39647567/205233) (replacing quotes instead of removing) – Filburt Jun 23 '17 at 08:53

1 Answers1

0

Try this:

$cont = get-content "D:\path\file.csv" 
$cont | % {
$info = "^" + $_.Split("|")[0] + "^" + $_.Split("|")[1] + "^" + $_.Split("|")[2] + "^"
$info  
}
Vitaly
  • 75
  • 4
  • This solution is limited to only 3 columns, I can have variable number of columns. – Joseph Jun 23 '17 at 10:02
  • 1
    Yes =). Try this: $inform = Get-Content D:\csvs\comp.csv $inform | % { $info = $_.ToString().Replace("|","^") $info += "^" $var = "^" + $info $var | Out-file D:\csvs\test.csv -Append } – Vitaly Jun 23 '17 at 12:11
  • Works! I tweaked a bit to retain | in output CSV file. – Joseph Jun 23 '17 at 13:33
  • I am using below code: ` $inform = Get-Content E:\Test.csv.csv $inform | % { $info = $_.ToString().Replace("|","""|""") $info += """" $var = """" + $info $var | Out-file F:\Test1.csv -Append } ` Above code works for small file uptop 100 MB but if the file is huge (>200MB), the code takes huge time (more than 45 minutes) to convert, do we have more simpler approach... – Joseph Jul 10 '17 at 11:43