Database Reference
In-Depth Information
The SQL string is deined using PowerShell's here-string construct with the
@
symbol before
and after the string. This makes it easy to deine a formatted, multiline string.
After loading the
DataTable
, it is given a name with
$dt.TableName = "Employee"
; this
is required later to create the XML ile with
$dt.WriteXml
as it needs a table name to know
what tag name to use for each XML record.
Push-Location
(or
pushd
for short) is then used to add the script's directory to the location
stack and change the current directory location to that path. This makes it easier for ile I/O to
come, as full ilenames will not have to be speciied. Next any iles in this directory matching a
*.HR.*
pattern are removed with
Remove-Item
; this ensures that on any subsequent script
runs we start with a clean slate and does not append duplicate data to existing iles.
Next, the raw data from the
DataTable
is output using different formats. First the data is
formatted as a list with
Format-List
and piped to
Out-File
to create a list-formatted text
ile. In a similar fashion, a table-formatted text ile is produced using
Format-Table
. An XML
ile is written using
$dt.WriteXml(".\Employees.xml").
This could also be done with
($dt | ConvertTo-Xml -NoTypeInformation).Save(".\Employees.xml")
, but
that's more verbose and produces much larger output that isn't formatted as well. Lastly an
Excel-readable CSV ile is produced via a pipe to
Export-CSV
.
Finally the data in the
DataTable
is iltered down with
Where-Object
to those records that
have a job title like
*Sales*
and a hire date greater than or equal to
02/01/2004
. These
matches are then sorted with
Sort-Object
by the manager's name and then the employee's
name. Next
ForEach-Object
is used to iterate over these results and perform processing.
In this example the processing simply consisits of every manager building text iles for his/her
employees by appending the employee name and job title with
Add-Content
.
Finally the script uses
Invoke-Item .
to launch the current directory in Windows
Explorer and
Pop-Location
(or
popd
for short), to remove the script's directory from
the location stack, and change back to the original directory before
Push-Location
(Get-ScriptDirectory)
was invoked.
There's more...
Another popular export option is HTML; let's take a look at that.
Exporting to HTML
One common HTML formatting option is using
ConvertTo-HTML
as follows:
$filename = join-path (Get-ScriptDirectory) Employees.html
if (Test-Path($filename)) {Remove-Item $filename -force}
$head = @"
<style>
table { font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-
Search WWH ::
Custom Search