PowerShell Quirks

This morning I had to figure out how to create 45 insert statements in order to populate a new table in oracle from existing data.

The table is a cross-reference table and only consists of 2 columns that need to be populated by the data . Using an Excel spreadsheet I had created a two column CSV file by hand, which was a long and tedious process. I did not want to duplicate this process and since I had it in a friendly format surely there was an easier way.

After experimenting with the IDE I was using I found that I could not simply import the data because it would either destroy the table and recreate it with only those two columns (destroying the columns that I needed to hold the create date, created by, modified date and modified by values) or it would reject the data because it did not contain enough columns in which case I might as well go back to writing each individual SQL statement by hand.

I thought about writing a macro in Vim to accomplish this which is certainly doable but frankly, it’s been a while since I wrote anything remotely that complex in Vim so I dismissed that idea as too time consuming.

Then I realized that this should be trivial in PowerShell and the solution could be used universally by everyone in the development department in a similar situation.  Indeed, creating the script that would generate the SQL statements was trivial.

An Import-Csv filename created a hash array with each row in my CSV file, a single variable with tokens for the two items I wanted to replace, an Out-File and a foreach loop later and it was done. Coding is awesome!!

What I didn't know was that PowerShell gets quirky with carriage returns and newlines when using Out-File to write to a file. No matter what I tried, I always wound up with a file containing a single line with forty-five SQL statements. After beating my head against it for a while I finally realized I was hurting myself. The simple way to do this (although not elegant) is to simply use the Write-Output string command and pipe that to the Out-File command using the append flag.

The write-output command always adds a windows newline character to the end of the string but since it’s piped to the Out-File it goes to the file rather than the console where it normally goes.

Just something to be aware of if you haven’t played with writing to files in PowerShell. If you’re aware of that issue and have your data in a CSV format, writing this script to create strings that can be tokenized takes about 2 minutes.

I agree with Scott Muc. Many things Microsoft leave a bad taste in my mouth but PowerShell typically isn't one of those.