Tuesday, April 03, 2007

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

2 comments:

/\/\o\/\/ said...

I show an alternative way here creating custom objects from the TSV file first then using export-csv , this way you do not need to worry about the quoting inside the TSV file, in a followup post I will also show a way using ODBC / ADO

http://thepowershellguy.com/blogs/posh/archive/2007/03/31/powershell-examples-used-on-ars-technica.aspx

h.t.h.

Greetings /\/\o\/\/

Anonymous said...

Whаt's up to every one, the contents existing at this web page are in fact amazing for people knowledge, well, keep up the good work fellows.
All across the globe, Online users find strategies to keep anonymous towards the Internet sites they stop by. The same customers happen to be looking for strategies to access to well-liked Websites which are hindered from total teams of consumers. Individuals have got relied on a strong anonymizer or even Net proxy expert services to stay undetectable or access to online Telly, Xbox 360 system Live, Hulu, and various significant written content web pages from around the globe.

The favorite Ukrainian anonymizer provider HideME.ru features finally created a English language Website for his or her providers known as InCloak.org. “Our expert services let consumers to improve their particular real Ip with a furnished nameless IP that permits these to browse the web properly with no leaving a brief history in addition to achieve 100-percent entry to formerly hindered Internet sites,” reported the InCloak.com consultant.

A InCloak.com anonymizer enables the person to put a server state connected with foundation, select any readily available IP address, separate out destructive website programs, cut advertising banners along with create favorite and quite often went to Web pages for the program’s alexa tool. Whilst the anonymizer is employed by a unique picked out software, your provider gives a digital Individual Circle (VPN) generally known as OpenVPN. This VPN purchaser service provides highest possible privacy in addition to to protect almost all uses which take advantage of the Online in the subscriber’s computer. “The main VPN edge on every proxy or perhaps anonymizer is definitely the approach it includes anonymity along with to protect all the laptop or computer right away,” mentioned your professional.

InCloak.org supplies Site gear including Website proxy, proxy listing, Checker and even more at as few as $0.Apr daily and also VPN accessibility as low as $0.12 on a daily basis by using one-year subscriptions. Additional options include things like each day, per month and two-year subscribers. Your high grade gain access to strategy presents complete access to the anonymizer (plus VPN, if your rule will be obtained with this selection) without the need of standards about use. Numerous more features may also be included.
My webpage :: Fettabsaugen