Posts Tagged ‘Import-Csv’
PowerShell Convert Delimiters, Import Tab Delimited Text…
Well, just for a change, lets cut straight to the script ( ![[new]](http://huddledmasses.org/wordpress/wp-includes/images/smilies/../../../wp-content/plugins/smilingmasses/new.gif)
5pm EST Sunday):
Function Convert-Delimited([regex]$from,[string]$to)
{
process
{
$_ = $_ -replace "(?:`"((?:(?:[^`"]|`"`"))+)(?:`"$from|`"`$))|(?:((?:.(?!$from))*.)(?:$from|`$))","Þ`$1`$2Þ$to"
$_ = $_ -replace "Þ(?:$to|Þ)?`$","Þ"
$_ = $_ -replace "`"`"","`"" -replace "`"","`"`""
$_ = $_ -replace "Þ((?:[^Þ`"](?!$to))+)Þ($to|`$)","`$1`$2"
$_ = $_ -replace "Þ","`"" -replace "Þ","`""
$_
}
}
Basically, this is a set of regular expressions that can be run in the pipeline to convert delimited text from one delimiter to another. If the new delimiter needs quotes added to make it work correctly, the converter will do so (actually, it adds a marker character Þ around every field, and then removes them where they are not needed, and replaces them with quotes where they are — long story, and I welcome any improvements).
Just as an example, assume a column-based text file like this Hosts.txt file (notice that the columns are separated by two or more spaces, since there are spaces within the text columns):
Host Standout Features Languages Space/Bandwidth Monthly
BlueHost PostgreSQL + MySQL, Free Cert PHP5,Perl,Python,Ruby 300GB/3TB .95/mo for 2yrs
DreamHost Jabber,SVN,WebDAV PHP4,PHP5,Perl,Python,Ruby 500GB/5TB .95/mo for 2yrs
NetFirms Adv 2 domains, Shared Cert PHP4,PHP5,Perl,Ruby,Python 250GB/2TB .95/yr with "995" code
NetFirms Bus Windows + Linux, MsSQL, 5 domains ASP,ASP.NET,PHP4,PHP5,Perl,Ruby,Python 300GB/3TB .95/mo for 3mo
You can convert it to CSV by using this function, and write it back out to file like this: Get-Content Hosts.txt | Convert-Delimiter " + " "," | Set-Content Hosts.csv. Now, since my original problem was all about importing the files I also went ahead and wrote a slightly more magical function called Import-Delimited: Read the rest of this entry »
Posted in 
Tags: 