PowerShell, Convert-TsvToCsv

The other day, I exported some data from SQL 2000 analyzer to a CSV-file. The database was across the internet but I had no direct database connection and I had to get it down to my PC for analysis. The easiest way was to export it directly to my PC's file system using \\tsclient\c.

On my PC, I had created a PowerShell script, to analyse the data. After getting strange results, I realized that SQL exported values with commas without quoting them. This made the cmdlet Import-Csv ‘shift’ some of the records, as it parsed the records.

To overcome this, I exported the data as TSV i.e. with the tab character as delimited. But Import-Csv does not read TSV or any other delimited than comma (see also my earlier entry about Export-CsvCultureAware). Consequently, I had to convert it to a correctly-formatted CSV-file. I did it with this script –


PS> type Convert-TsvToCsv.ps1

# Convert a tab separated file to comma-seperated, enclosing each element in double quotes
# quotes within the values are doubled
$v=new-object system.text.stringBuilder 1000
$input | % {
 $v.length=0 # empty sb
 $e=$_.split("`t")
 $e | % {
  $null=$v.append(",")
  if ($_[0] -eq "`"" -and $_.endswith("`"")) {
   # already quoted - strip them
   $_=$_.substring(1,$_.length-2)
  }
  $null=$v.append('"'+$_.replace('"','""')+'"')
 }
 $v.tostring().substring(1)
}

Comments to the code –

• This is a quick and dirty solution
• Pipe your tsv-file into the script file and output will be csv
• A single StringBuilder is used to optimize memory usage, I had to convert a file with 15.000 lines, 13 MB file, so speed mattered
• To make the logic easier, the string always starts with a comma and then does not output it at the end – hence the substring(1)

Share it!

PS. Code formatted with CodeHTMLer