Parsing CSV files with PowerShell

So there I was, looking at a Caliber requirement, basically a Word document, with a table of values that needed to get into a table in our SQL database.  We were still in development and there was no entry form in the application for the requirement writers to enter the values.  Faced with the prospect of having to manually build a sql script to insert all these values, I desperately called out to Necessity.  You know that Necessity is the mother of invention.  She carries a big purse full of invention, with which she immediately hit me over the head.  She told me that I already had Powershell, mumbled something about the boy that cried wolf and stormed out.  Powershell, eh?  The following is an abbreviated description of what I did to generate my sql script. My insert statement was much larger.

First, I copied the word table into excel, including the headers, and saved the file as a CSV file. The format looked something like this:

State, City, Type

GA, Atlanta, AB

GA, Albany, A

Now for the Powershell.  Check out this bad boy:

Import-Csv Test.csv | % { "INSERT INTO refTestTable (State, City, Type) VALUES ('$_.State', '$_.City', '$_.Type')"}  >> "Test.sql"

After opening up the new Test.sql file, I got this:

INSERT INTO refTestTable (State, City, Type) VALUES ('GA', 'Atlanta', 'AB')
INSERT INTO refTestTable (State, City, Type) VALUES ('GA', 'Albany', 'A')

Not bad for one line of code.  Powershell's Import-Csv command loops through all the records in the CSV file and generates objects.  Then for each object we build a sql string and insert the object's properties.  Notice how the properties were dynamically generated so all you had to do was call them?  Finally, I outputted the resulting string to a text file.  Using this method I was able to generate and run the sql script in less than 10 minutes.