Well, just for a change, lets cut straight to the script ([new] 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: (more…)