Import Excel, unpivot data, output tab-delimited for SQL Import
Path Joining



$srcDir = "C:"
$srcFile = "$SrcDir\ClientGL.xlsx"
$srcTab = "Sheet1"
$outFile = "$SrcDir\ClientGL.txt"


Although it may seem unnecessary in this case, you should know that Join-Path exists.

$srcDir = "C:"
$srcFile = $SrcDir | Join-Path -ChildPath "ClientGL.xlsx"
$srcTab = "Sheet1"
$outFile = Join-Path $SrcDir "ClientGL.txt"


The nice thing about using this (again, unnecessary here) is that it takes care of leading and trailing path separators, so the following:

Join-Path "C:\" "\Windows"
Join-Path "C:" "Windows"


Will both produce C:\Windows. That makes it great for user input.



Write-Host vs Write-Verbose

This can be debated, but some (myself included) take a cautious approach with Write-Host. The reason is that it forces the output to the screen, instead of letting the caller decide whether they want status messages.

Ultimately, you take this on a case-by-case, and sometimes Write-Host is the right tool, and maybe it is for you in this case.

Using Write-Verbose instead, you would normally not see the output, unless you call your script with -Verbose, and then you will see it.

To add support for this, you need to add a parameter block to your script, even if you aren't going to take any parameters. 

Top of script:

[CmdletBinding()]
param()


That will automatically add support for -Verbose.



Aliases

If(ps excel){kill -name excel}


and

(Get-Content $outFile) | % {$_ -replace '"', ""} | Out-File -FilePath $outFile -Force


ps is an alias for Get-Process.
kill is an alias for Stop-Process.
% is an alias for ForEach-Object.

I love PowerShell's aliases, but when it comes to writing a script that's meant to be re-used often or might ever be read by other people, I am of the school that one should use the full cmdlet/function name, and use full parameter names.

I use aliases when I'm using PowerShell as a shell and typing commands to run immediately, or to quickly prototype something.

For me it's a matter of clarify and readability, but this is of course personal preference (I have met at least one person who feels differently and uses aliases everywhere).



Reading the first line of a file

$inHeaders = (Get-Content $outFile | Select-Object -First 1).Split("`t")


If you're using PowerShell 2.0, the above line will read every line of the file, and even though Select-Object will only give you back the first 1, it has no way of stopping the pipeline. If your file is large, this could take a very long time.

If you're using PowerShell 3+, then the above will actually only read the first line because Select-Object will stop the pipeline.

Note that Get-Content has a -TotalCount parameter though, so you can use:

$inHeaders = (Get-Content $outFile -TotalCount 1).Split("`t")


and it will read just the first line.



.Split() vs -Split

$inHeaders = (Get-Content $outFile | Select-Object -First 1).Split("`t")


Here you're using the .Split() method of a [String]. PowerShell also has a -Split operator. The operator has a lot of nifty features that from what I've seen no one ever uses, because they don't know they exist or (in my case) they always forget about them when needed.

But the main difference that you probably will remember is that the operator takes a regular expression, and the method takes a string.

There's nothing wrong with either, just wanted you to be aware of the differences.



Accepting Parameters

$srcDir = "C:"
$srcFile = "$SrcDir\ClientGL.xlsx"
$srcTab = "Sheet1"
$outFile = "$SrcDir\ClientGL.txt"


Consider making these parameters that can be passed to the script. You could also make them have defaults that way.

Top of script:

[CmdletBinding()
param(
    [Parameter(
        Mandatory=$false
    )]
    [String]
    $Path = "C:" ,

    [Parameter(
        Mandatory=$false
    )]
    [String]
    $srcTab = "Sheet1"
)

$srcFile = "$Path\ClientGL.xlsx"
$outFile = "$Path\ClientGL.txt"


Now you can call the script like so:

.\MyScript.ps1 -Path "C:\Some\Other\Path\" -srcTab "Expenses"


Now you can see why using Join-Path would save you some headache because you're accepting user input!

Just to break down this param() block a bit:

[Parameter()] can take multiple comma separated options; I've shown you Mandatory. Mandatory means mandatory to be supplied by the caller not to have a value, so when you use a default value like we are here, you don't want to make the parameter mandatory. Parameters are by default optional, so this was unnecessary; I was just demonstrating it.

The entire [Parameter()] attribute is also optional, and could be left out.

[String] is specifying the data type for the parameter. PowerShell will try to coerce input values into this type if possible. Data type is also optional.

$Path: this is the name of the parameter. It is the only part that's mandatory. I used $Path instead of $srcDir because PowerShell has conventions for certain names to represent certain things.

So generally any cmdlet or function that accepts a path will use -Path. Same for -ComputerName, -Identity, -Credential.

Also note that all of these pieces could have been on a single line. I like to separate them for clarity.

For more on these, have a look at Create a Truly Advanced PowerShell Function and the about_Functions_Advanced and about_Functions_Advanced_Parameters help topics.