Monthly Archives: November 2009

Working with Access dates

Following on from the previous post about updating records one data type that will be a little awkward to work with is dates.

If you use a US locale or any other that uses a date format of Month/Day/Year you can more or less ignore this because your standard formats work OK.

If I run

PS> Get-AccessData -sql "select * from test1" -connection $db

FirstName                         LastName                          DOB
---------                         --------                          ---
Fred                          ... Green                         ... 01/09/1979 00:00:00
John                          ... Green                         ... 01/07/1981 00:00:00
James                         ... Jones                         ... 18/04/1981 00:00:00

In the first record the date is first of September 1979 because I am using a UK date format of day/month/year

if I try to query on that date

PS> Get-AccessData -sql "select * from test1 where DOB = #01/09/1979#" -connection $db

I don’t get anything returned but if I use

PS> Get-AccessData -sql "select * from test1 where DOB = #09/01/1979#" -connection $db

FirstName                         LastName                          DOB
---------                         --------                          ---
Fred                          ... Green                         ... 01/09/1979 00:00:00

 

I get the data I expect.

This is a PowerShell issue.

PS> $host

Name             : ConsoleHost
Version          : 2.0
InstanceId       : 98b88ef9-297c-46a8-bc2c-8cbb4cb273df
UI               : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture   : en-GB
CurrentUICulture : en-US
PrivateData      : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
IsRunspacePushed : False
Runspace         : System.Management.Automation.Runspaces.LocalRunspace

Notice that the CurrentUICulture is en-US but my CurrentCulture is en-GB.  This means that when I input dates at the PowerShell prompt or in scripts they will be interpreted following the US format.

My recommendation for dealing with this is to use this format

PS> Get-AccessData -sql "select * from test1 where DOB = #01-Sep-79#" -connection $db

FirstName                         LastName                          DOB
---------                         --------                          ---
Fred                          ... Green                         ... 01/09/1979 00:00:00

This removes the ambiguity and leaves us able to apply the same commands anywhere (at least if you are using English :-))

Our update command then becomes

Set-AccessData -table test1 -filter "DOB = #01-Sep-1979#" -value "DOB = #02-Sep-1979#" -connection $db

Updating Access data

The last of of our data manipulation tasks is to update the data – we have already seen how to create, read and delete.

001
002
003
004
005
006
007
008
009
010
011
012
013
function Set-AccessData {
[CmdletBinding(SupportsShouldProcess=$true)]
param (
    [string]$table,
    [string]$filter,
    [string]$value,
    [System.Data.OleDb.OleDbConnection]$connection
)
    $sql = "UPDATE $table SET $value WHERE $filter"
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
   
    if ($psCmdlet.ShouldProcess("$($connection.DataSource)", "$sql")){$cmd.ExecuteNonQuery()}
}

 

This function is very similar to the delete function. We have an additional parameter to pass in the values we are updating.

PS> Import-Module accessfunctions
PS> $db = Open-AccessDatabase -name test03.mdb -path c:\test
PS> Get-AccessData -sql "select * from test1" -connection $db

FirstName                          LastName                           DOB
---------                          --------                           ---
Fred                           ... Smith                          ... 01/09/1979 00:00:00
John                           ... Smith                          ... 01/07/1981 00:00:00
James                          ... Jones                          ... 18/04/1981 00:00:00

 

PS> Set-AccessData -table test1 -filter "LastName='Smith'" -value "LastName='Green'" -connection $db
2
PS> Get-AccessData -sql "select * from test1" -connection $db

FirstName                          LastName                           DOB
---------                          --------                           ---
Fred                           ... Green                          ... 01/09/1979 00:00:00
John                           ... Green                          ... 01/07/1981 00:00:00
James                          ... Jones                          ... 18/04/1981 00:00:00

Testing Connection to Access database

Many of the functions we have created so far have taken a connection to an Access database as a parameter.  At the time we pass in the connection we don’t actually know if the connection is open. Test-AccessConnection can be used to test the connection before we use it.  All we do is test the value of the state property on the connection object.  if it is Open we return true other wise false.  Only values at present are open and closed.

001
002
003
004
005
006
007
008
function Test-AccessConnection {
param (
    [System.Data.OleDb.OleDbConnection]$connection
)  
    if ($connection.State -eq "Open"){$open = $true}
    else {$open = $false}
    $open   
}

 

We can use the Test-AccessConnection function like this

PS> Import-Module accessfunctions
PS> $db = Open-AccessDatabase -name test03.mdb -path c:\test
PS> if (Test-AccessConnection $db){Get-AccessData -sql "select * from test1" -connection $db}

FirstName                            LastName                           DOB
---------                            --------                           ---
Fred                             ... Smith                          ... 01/09/1979 00:00:00
John                             ... Smith                          ... 01/07/1981 00:00:00
James                            ... Jones                          ... 18/04/1981 00:00:00

PS> Close-AccessDatabase $db
PS> if (Test-AccessConnection $db){Get-AccessData -sql "select * from test1" -connection $db}else{Write-Host "Database Closed"}
Database Closed
PS>

Use the Test-AccessConnection function in an if statement. If $true is returned we can execute our command.  This could be extended to give us a message if the connection is closed.

Removing Access Records

So far we have seen how to add data to a table in an access database – now we want to delete some records.  This is an action that can cause problems especially if we get the wrong records – ideally we want to a mechanism to check what we are doing.  The PowerShell cmdlets that change system state have –whatif and –confirm parameters to enable us to test our actions.  The Advanced Function capability in PowerShell v2 enables us to duplicate this functionality very simply.

001
002
003
004
005
006
007
008
009
010
011
012
function Remove-AccessData {
[CmdletBinding(SupportsShouldProcess=$true)]
param (
    [string]$table,
    [string]$filter,
    [System.Data.OleDb.OleDbConnection]$connection
)
    $sql = "DELETE FROM $table WHERE $filter"
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
   
    if ($psCmdlet.ShouldProcess("$($connection.DataSource)", "$sql")){$cmd.ExecuteNonQuery()}
}

 

Our function uses the CmdletBinding parameter we saw in our last version of Adding a record.  This time we have added the SupportsShouldProcess attribute.

Our parameters are the table and the connection which we have seen before.  The filter parameter is the stuff in the WHERE clause of the DELETE statement without the WHERE.

As an example

Remove-AccessData -table test1 -filter "LastName = 'Bloggs'" -connection $db

Translates to a SQL statement of

DELETE FROM test1 WHERE LastName = 'Bloggs'

inside the function.

We create the SQL by string substitution and create the command.  Instead of directly executing the command we use the $psCmdlet.ShouldProcess() method to test if –whatif or –confirm have been used before executing the command.

These examples should show how it works.

First we’ll test that the data we want to delete actually exists

PS> Get-AccessData -sql "select * from test1 where LastName = 'Bloggs'" -connection $db

FirstName                               LastName                                DOB
---------                               --------                                ---
Jo                                  ... Bloggs                              ... 24/12/1980 00:00:00

 

Now we use the –whatif parameter to show us what would happen if we ran the command

PS> Remove-AccessData -table test1 -filter "LastName = 'Bloggs'" -connection $db -whatif
What if: Performing operation "DELETE FROM test1 WHERE LastName = 'Bloggs'" on Target "c:\test\test03.mdb".

and we can confirm that the data is still there

PS> Get-AccessData -sql "select * from test1 where LastName = 'Bloggs'" -connection $db

FirstName                               LastName                                DOB
---------                               --------                                ---
Jo                                  ... Bloggs                              ... 24/12/1980 00:00:00

 

Now we can ask for confirmation on the delete

PS> Remove-AccessData -table test1 -filter "LastName = 'Bloggs'" -connection $db -confirm

Confirm
Are you sure you want to perform this action?
Performing operation "DELETE FROM test1 WHERE LastName = 'Bloggs'" on Target "c:\test\test03.mdb".
(Y) Yes  (A) Yes to All  (N) No  (L) No to All  (S) Suspend  [?] Help (default is "Y"): n

note – all () in above line should be []

We have not confirmed so the data is still present.

PS> Get-AccessData -sql "select * from test1 where LastName = 'Bloggs'" -connection $db

FirstName                               LastName                                DOB
---------                               --------                                ---
Jo                                  ... Bloggs                              ... 24/12/1980 00:00:00

 

This time we do confirm

PS> Remove-AccessData -table test1 -filter "LastName = 'Bloggs'" -connection $db -confirm

Confirm
Are you sure you want to perform this action?
Performing operation "DELETE FROM test1 WHERE LastName = 'Bloggs'" on Target "c:\test\test03.mdb".
(Y) Yes  (A) Yes to All  (N) No  (L) No to All  (S) Suspend  [?] Help (default is "Y"): y
1

note – all () in above line should be []

And the data is gone

PS> Get-AccessData -sql "select * from test1 where LastName = 'Bloggs'" -connection $db
PS> Get-AccessData -sql "select * from test1 " -connection $db

FirstName                               LastName                                DOB
---------                               --------                                ---
Fred                                ... Smith                               ... 01/09/1979 00:00:00
John                                ... Smith                               ... 01/07/1981 00:00:00
James                               ... Jones                               ... 18/04/1981 00:00:00
Alex                                ... Green                               ... 17/05/1981 00:00:00

If you are 150% sure of what you are deleting then just run the function without –whatif or –confirm otherwise use them as a final test.

The ability to add a very powerful check to our functions with minimal coding is a great bonus from PowerShell v2.  We will see this technique in more functions as we develop our access module.

Add Access Record Pt III – parameter sets

Last time we added the option of inputting the table and values to our function but we needed a way to discriminate between that and using a full SQL statement.  We can achieve this by dividing the parameters into parameter sets NOTE – This is a PowerShell v2 capability.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
function Add-AccessRecord {
[CmdletBinding()]
param (
    [parameter(ParameterSetName="sql")]
    [string]$sql,
   
    [System.Data.OleDb.OleDbConnection]$connection,
   
    [parameter(ParameterSetName="value")]
    [string]$table,
   
    [parameter(ParameterSetName="value")]
    [string]$values
)
    if($psCmdlet.ParameterSetName -eq "value"){
        $sql = "INSERT INTO $table VALUES ($values)"
    }
   
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
    $cmd.ExecuteNonQuery()
}

 

We add the [CmdletBinding()] parameter and on the sql,varaible we add a parameter set name of “sql”.  The table and values parameters are put into a parameter set of “value”.  The connection parameter is not explicitly placed into either parameter set which means it is in both!

We can then check the ParameterSetName property and create the $sql variable if required. The function can be used as follows

Import-Module accessfunctions
$db = Open-AccessDatabase -name test03.mdb -path c:\test

Add-AccessRecord -sql 'insert into test1 values("James","Jones","18/04/1981")' -connection $db

Add-AccessRecord -connection $db -table test1 -values '"Alex","Green","17/05/1981"'

In these two examples we have restricted ourselves to one or the other of the parameter sets.  if we try to mix parameters across the parameter sets

PS> Add-AccessRecord -sql 'insert into test1 values("Rab","Burns","08/06/1981")' -connection $db -table test1
Add-AccessRecord : Parameter set cannot be resolved using the specified named parameters.
At line:1 char:17
+ Add-AccessRecord <<<<  -sql 'insert into test1 values("Rab","Burns","08/06/1981")' -connection $db -table test1
    + CategoryInfo          : InvalidArgument: (:) [Add-AccessRecord], ParameterBindingException
    + FullyQualifiedErrorId : AmbiguousParameterSet,Add-AccessRecord

we get an error message.

We now have a function that allows us to work in the most efficient way at a particular moment.  Next we’ll see how we can feed the values from a csv file.

Export Access data to csv file

We already have all the functionality we need to achieve this.

Import-Module accessfunctions
$db = Open-AccessDatabase -name test03.mdb -path c:\test
Get-AccessData -sql "select * from test1" -connection $db | Export-Csv -Path c:\test\test1.csv –NoTypeInformation

Open the csv file in Excel and the data is available.  If you open the csv file in notepad be aware that the fields are padded and you need to scroll to the right to see all the data.

 

We could add the export functionality into our Get-AccessData function but if we stick with the PowerShell composability concepts we want to keep the functions small and focussed.  If you want to change the function – feel free – just add a switch for csv output and a parameter for the csv path. Then use another if statement to control writing to the csv.

 

Comments Disabled

I found twenty comments this morning – all adverts for things I don’t want – plastered over recent posts.  I don’t want to spend my time cleaning off inappropriate material from my blog so I have suspended comments on posts on this blog until further notice.

If you want to leave a comment all posts, except this sort,  are mirrored at  http://msmvps.com/blogs/RichardSiddaway/  That will become my main technical blog with this one as a mirror.  Comments on that blog have to be approved I don’t get these clean up operations.

Sorry for any inconvenience – this is the usual story of the actions of a few selfish individuals causing problems for everyone else

Technorati Tags: ,

Reading Access records

Reading data from an Access database is similar to the functionality we have already seen.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
function Get-AccessData {
param (
    [string]$sql,
    [System.Data.OleDb.OleDbConnection]$connection,
    [switch]$grid
)
   
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
    $reader = $cmd.ExecuteReader()
   
    $dt = New-Object System.Data.DataTable
    $dt.Load($reader)
   
    if ($grid) {$dt | Out-GridView -Title "$sql" }
    else {$dt}

}

 

We can pass a SQL statement and the connection information

$dt = Get-AccessData -sql "Select * FROM test1" -connection $db
$dt | ft

in which case we get a DataTable object that we can put through a formatting cmdlet or that we could use for further processing.

if we use the –grid switch

Get-AccessData -sql "Select * FROM test1" -connection $db –grid

We are using the out-gridview cmdlet from within the function to display the data

Get-AccessData -sql "Select * FROM test1" -connection $db | out-gridview

would achieve the same goal.

Add Access Record PtII

We have seen how to add a record to an Access table by passing in the whole SQL string.  This is OK when we want to add a single record or possibly not fill all fields in a row.

001
002
003
004
005
006
007
008
009
010
011
012
function Add-AccessRecord {
param (
    [string]$sql,
    [System.Data.OleDb.OleDbConnection]$connection,
    [string]$table,
    [string]$values
)
    $sql = "INSERT INTO $table VALUES ($values)"
   
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
    $cmd.ExecuteNonQuery()
}

 

If we alter our function to create the SQL as shown we can pass the table name and values into the function. 

Add-AccessRecord -connection $db -table test1 -values '"John","Smith","01/07/1981"'

This will add a record and start applying the values in sequence to the row.  it assumes that values are supplied for all fields.

At the moment the function effectively breaks our previous version because $sql is redefined in the function.  We need to test whether we should create a SQL record or use the SQL statement supplied. We could check if the $sql parameter is given a value but a better way would be to use parameter sets which we will look at after we have learned to read data from a table.

Set Background colour of Excel cell

I needed to set the background colour of a cell in an Excel spreadsheet recently.  The way to do it is to set the ColorIndex property of the Interior properties of the cell as shown in line 14.  The ColorIndex can be set to a number between 1 and 56.  Use –4142 if you don’t want a coloured background.

Using a ColorIndex is OK but what colours are related to the index values.  I found a VBScript amongst the Office information on the Microsoft site and modified it as shown below.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
$xl = New-Object -ComObject "Excel.Application"
$wkbk = $xl.WorkBooks.Add()
$sheet = $wkbk.WorkSheets.Item(1)
$xl.Visible = $true

for($i=1; $i -le 56; $i++) {
    switch ($i) {
        {$_ -le 14}                {$row = $i;    $col = 1}
        {$_ -ge 15 -and $_ -le 28} {$row = $i-14; $col = 3}
        {$_ -ge 29 -and $_ -le 42} {$row = $i-28; $col = 5}
        {$_ -ge 43 -and $_ -le 56} {$row = $i-42; $col = 7}                   
    }
    $sheet.Cells.Item($row, $col).FormulaLocal = $i
    $sheet.Cells.Item($row, $col+1).Interior.ColorIndex = $i

}

$wkbk.SaveAs("c:\test\XLColours.xlsx")
$wkbk.Close()
$xl.Quit()

 

I am using Excel 2010 so WorkBooks.Add() will work OK.  If you are using an earlier version of Excel and are not using US English then you need to use the alternative method of adding workbooks as I discussed in earlier posts.

Create an object for Excel and then add a workbook and use the first worksheet. Make it visible so we can see what happens.

Use a for loop to work through the values 1 to 56.  I want the information arranging in 4 columns so use a switch statement to determine the row and column to use.

The ColorIndex value is displayed and the next cell on the row is coloured using that index.

We can then save and close the workbook.