Monthly Archive

Categories

Monthly Archives: January 2010

Appending to a CSV file

One of the frustrations of working with files in PowerShell is that you can’t append to a pre-existing CSV file.

Dmitry has fixed that for version 2 by creating a proxy function that adds that capability.

http://dmitrysotnikov.wordpress.com/2010/01/19/export-csv-append/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+DmitrysPowerblog+%28Dmitry%27s+PowerBlog%29

Excellent

Technorati Tags: PowerShell

AD cmdlets qiuck reference

If you use the AD cmdlets that shipped with Windows 2008 R2 you will want to grab a copy of the quick reference guide that Jonathan has produced.

http://www.jonathanmedd.net/2009/10/active-directory-powershell-quick-reference-guide.html

Technorati Tags: PowerShell,Active Directory

Advanced Warning – WMI & WQL

The UK PowerShell group will present a Live Meeting on PowerShell, WMI and WQL.

Date: Tuesday 9th February 2009

Time: 7.30pm GMT

Live Meeting details to follow

Technorati Tags: PowerShell,User group

Regular Expressions information

I have a bit of a love-hate relationship with regular expressions.  If like me they are not your absolute favourite things you will want to see the series of recent posts from James on the subject

http://blogs.technet.com/jamesone/archive/2010/01/14/the-joy-of-reg-ex-part-1.aspx

http://blogs.technet.com/jamesone/archive/2010/01/14/the-joy-of-reg-ex-part-2-ways-i-use-it.aspx

http://blogs.technet.com/jamesone/archive/2010/01/16/the-joy-of-reg-ex-part-3-select-string.aspx

Getting Access table definitions

So far we have only created a single table but in a database with a number of tables we need to be able to view the table definitions.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
##
## connect to database
##
$conn = New-Object -ComObject ADODB.Connection
$mydb = "c:\test\test03.mdb"
$conn.Open("Provider = Microsoft.JET.OLEDB.4.0; Data Source = $mydb")
$cat = New-Object -ComObject ADOX.Catalog
$cat.ActiveConnection = $conn

## view tables
## note user tables are of type TABLE
$cat.tables | select Name, DateCreated, DateModified

## view columns
$table = $cat.Tables | where {$_.Name -eq "test1"}
$table.Columns | Format-Table  Name, DefinedSize, typeAutoSize

 

Unfortunately we can’t use the SQL methods we have been doing so far.  We need to dip into ADODB and ADOX to get this information.

MAKE SURE THAT THE DATABASE IS CLOSED BEFORE ATTEMPTING TO DO THIS

Create an ADODB connection and open the database using the JET provider and the path to your database.

We then create a ADOX catalog and set its ActiveConnection property to the connection

We have a tables collection where we can view basic table information. For an individual table we can view the table definitions

Name      DefinedSize Type
----      ----------- ----
DOB                 0    7
FirstName         255  130
ID                  0    3
LastName          255  130

 

Next we need to dig into the type definitions so we can understand the listing and see how we can dump this information for all our tables

Add-Computer

I was building some test machines yesterday and needed to add them to the domain. As they were Windows 2008 R2 with PowerShell v2 I decided to try Add-Computer.

Add-Computer -DomainName mydomain -Credential mydomain\myaccount; restart-computer

You will be prompted for the password.

This only works on the local computer – you can’t add a remote machine.

Simple and effective command.

Hyper-V and Virtual PC

Just a quick warning – be careful if you have virtual machines in virtual PC and switch to using Hyper-V.  Not sure what I did wrong (it was late last night) but managed to trash my virtual SQL Server

There are instructions available – e.g. http://blogs.technet.com/andrew/archive/2008/06/30/migrating-a-virtual-machine-from-virtual-pc-to-hyper-v.aspx

Technorati Tags: Hyper-V,Virtual PC

Delete an index

We have seen how to add indexes. We also need to be able to remove them

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
function Remove-AccessIndex {
# .ExternalHelp Maml-AccessFunctions.XML
[CmdletBinding()]
param (
    [string]$table,
    [string]$index,
    [System.Data.OleDb.OleDbConnection]$connection
)

    $sql = "DROP INDEX $index ON $table"
    Write-Debug $sql
   
    $cmd = New-Object System.Data.OleDb.OleDbCommand($sql, $connection)
    $cmd.ExecuteNonQuery()
}

 

The function accepts connection plus table and index names.   Use the function as

remove-accessindex –table test1 –connection $db –index index_name

If you need to remove multiple indexes on a table try

“index1”, “index2”, “index3” | foreach {remove-accessindex –table test1 –connection $db –index $_ }

Technorati Tags: PowerShell,Office 2010,Access

UK User Group meeting Jan 2010

 

First meeting of 2010 and we will be looking at Windows 2008 R2 cmdlets and provider.

Physical meetings will resume later in the year

 

 

 

When: Tuesday, Jan 26, 2010 7:30 PM (GMT)


Where:

*~*~*~*~*~*~*~*~*~*

Notes


Richard Siddaway has invited you to attend an online meeting using Live Meeting.
Join the meeting.
Audio Information
Computer Audio
To use computer audio, you need speakers and microphone, or a headset.
First Time Users:
To save time before the meeting, check your system to make sure it is ready to use Microsoft Office Live Meeting.
Troubleshooting
Unable to join the meeting? Follow these steps:

  1. Copy this address and paste it into your web browser:
    https://www.livemeeting.com/cc/usergroups/join
  2. Copy and paste the required information:
    Meeting ID: 5W5DCQ
    Entry Code: b{t^rPb6R
    Location: https://www.livemeeting.com/cc/usergroups

If you still cannot enter the meeting, contact support

Notice
Microsoft Office Live Meeting can be used to record meetings. By participating in this meeting, you agree that your communications may be monitored or recorded at any time during the meeting.

Technorati Tags: PowerShell,User group

Trouble with csv files

There are many situations where we want to read a csv file as part of our admin script – usually as a way to input data to the script for some kind of bulk processing. This makes the following a fairly common task

PS> import-csv -Path c:\test\csvtests\file1.csv | ft -a

col1 col2 col3
---- ---- ----
A    1    B
B    2    C
C    3    D
D    4    E
E    5    F

Normally we would expect a comma as the delimiter but we can use other characters e.g. “|” the pipeline symbol

PS> import-csv -Path c:\test\csvtests\file3.csv -Delimiter "|" | ft -a

col1 col2 col3
---- ---- ----
A    1    B
B    2    C
C    3    D
D    4    E
E    5    F

Sometimes we don’t get quite what we expect

PS> import-csv -Path c:\test\csvtests\file2.csv | ft -a
Import-Csv : Cannot process argument because the value of argument "name" is invalid. Change the value of the "name" argument and run the operation again.
At line:1 char:11
+ import-csv <<<<  -Path c:\test\csvtests\file2.csv | ft -a
    + CategoryInfo          : InvalidArgument: (:) [Import-Csv], PSArgumentException
    + FullyQualifiedErrorId : Argument,Microsoft.PowerShell.Commands.ImportCsvCommand

Not the most informative of error messages and one that leads to a lot of confusion because the name and path of the file is obviously correct.

One reason for this happening is that the header is wrong.  There is a trailing blank and\or a delimiter at the end of the header row

“col1|col2|col3| “      -  its only in quotes so the blank is obvious

We can use get-content to check this

PS> get-content c:\test\csvtests\file2.csv | select -first 2
col1|col2|col3|
A|1|B

Remove the blank and\or delimiter and import-csv works again.

PS> import-csv -Path c:\test\csvtests\file2.csv -Delimiter "|" | ft -a

col1 col2 col3
---- ---- ----
A    1    B
B    2    C
C    3    D
D    4    E
E    5    F

These extra delimiter can appear if we use Excel to create the csv file and inadvertently bring in too many columns.

CSV files are an important data source – this problem shouldn’t stop us using them

Technorati Tags: PowerShell,csv file