Tag Archives: “MS Access”

PowerShell, Working with MS Access Database

This is my second post on PowerShell, continuing my earlier post. The more I am working with PS, more i am getting the interest. For an administrator and geeky programmer the possibilities are endless. Today my aim is to connect to MS Access, read the table and create another table with unique records. Initially i thought to use SQL in MS Access to remove the duplicated by grouping them and taking top 1. But the issue is, i can get unique records only for “Doc Number” and “Doc Revision” columns of the table (check my  previous post). As soon as i include all other columns i could not get unique records. Now, i left with one option that is write C# code to do that job. But i want to experiment with PowerShell to do that job.

Once you install PowerShell, you get two application;

  1. PowerShell
  2. PowerShell ISE (Integrated Scripting Environment)

ISE looks like below and this is where you can write shell script for your needs. Of course, you can use any editor for that matter to write script. But using ISE give you syntax highlighting, intellisence, Commandlets help and also a shell to test your script.

image

As usual, I divided my whole problem into small milestones to achieve progressively. The following are my milestones to meet;

  1. Connect the MS Access MDB file through OleDB
  2. Create a dataset with two tables, one with schema (DT1) and another one with data (DT0).
  3. Go through each row and check if DT1 has the record or not and if not then insert the current record
  4. Exporting DT1 to excel so that, i can use it further

With this milestones, I pulled up my ISE and started scripting for first milestone.

Connecting to MDB file:

To achieve this, i need three variables to store 1. path to MDB file, 2. store the SQL command and 3. to store the connection string. In PowerShell a variable is denoted with “$”, check the following code;

$dataSource = "C:\Users\pradeepy\Documents\uote_docs.mdb"
$selectQuery = "SELECT * FROM FULL_DOCS_INFO"
$dsn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$dataSource"

Here, “$dataSource”, “$selectQuery” and “$dsn” are variables that i am going to use. Since my variables are ready, now i need to connect to the database. The following code will do the job;

Write-Host "Creating connection to database" -ForegroundColor Cyan
$dbCon = New-Object System.Data.OleDb.OleDbConnection $dsn
$sqlCmd = New-Object System.Data.OleDb.OleDbCommand $selectQuery, $dbCon
$dbCon.Open()

In this code, “Write-Host” is a commandlet that we can use to print some thing onto the screen. We can also set the foreground and background colours to the text. In this case, i added “Cyan” colour to the text. Now i should able to run the code and I ran the code to only hit back by an exception;

image

It seems, I cannot access OleDB provider for MS Access. Immediately I checked my ODBC driver by firing the command Get-OdbcDriver. This list all the drivers installed  and found my Access Drivers. That means it is not the issue with drivers but some thing else. A quick bing search revealed that, this is a typical issue if you run ISE in 64-Bit mode. Ok, lets close and open 32-Bit version of ISE and run the script. Now its working fine.

Note:

In some cases, system might throw an exception saying “cannot run the script due to script execution policy”. In this case you can bypass the policy by following commandlet;

Set-ExecutionPolicy -ExecutionPolicy Bypass

This will bypass the policy so that you can run the script. To set it back fire the above command again after replacing the “Bypass” to “Restrict”.

Creating the DataSet:

Now, second milestone. We need two datatables to handle the data. One is for data from MDB file and another one is unique records. Following code will do that;

Write-Host "Creating datasets" -ForegroundColor Cyan
$dataSet = New-Object System.Data.DataSet
$sourceDataTable = New-Object System.Data.DataTable "SOURCE_UOTE_DOCS"
$finalDataTable = New-Object System.Data.DataTable "FINAL_UOTE_DOCS"
$dataSet.Tables.Add($sourceDataTable)
$dataSet.Tables.Add($finalDataTable)

Once the datatables are ready, we need to pull the data into the DT0. Below code will do that;

Write-Host "Fill schema of the table into a data datable" -ForegroundColor Cyan
$dataAdaptor = New-Object System.Data.OleDb.OleDbDataAdapter $sqlCmd
$dataAdaptor.FillSchema($dataSet.Tables[1], [System.Data.SchemaType]::Mapped)
[void] $dataAdaptor.Fill($dataSet.Tables[0]);

Write-Host "Closing the connection" -ForegroundColor Cyan
$dbCon.Close();

Remove duplicate records:

It’s time to load only unique records. To do that, for each row in the DT0 we check if DT1 has any row with same Doc Number and REV. If not then put the row into DT1. In the following code, we are piping the DT0.Rows object to ForEach-Objectloop. In this loop, we try to select a records with current object’s DOCUMENT_NUMBER column and Doc Revision column form DT1. If the count is 0 then insert else just log it. Have a look at below code;

$duplicateRecordsCount = 0
$uniqRecordsCount = 0
$dataSet.Tables[0].Rows | ForEach-Object{
    If($dataSet.Tables[1].Select("[Doc Number] like '"+$_["Doc Number"]+"' and [Doc Revision] like '"+$_["Doc Revision]+"'").Count -eq 0){
        
        $uniqRecordsCount++
        $newRow = $dataSet.Tables[1].NewRow()
        $newRow[0] = $uniqRecordsCount ##  ID
        $newRow[1] = $_[1]             ##  Name
        $newRow[2] = $_[2]             ##  DirectoryName 
        $newRow[3] = $_[3]             ##  FullName
        $newRow[4] = $_[4]             ##  Doc Name
        $newRow[5] = $_[5]             ##  Doc Number
        $newRow[6] = $_[6]             ##  Doc Revision
        $newRow[7] = $_[7]             ##  PROC_CODE
        $newRow[8] = $_[8]             ##  SUB_PROC_CODE
        $newRow[9] = $_[9]             ##  DOC_REV
        $newRow[10] = $_[10]           ##  DOC_REV_EXT
        $newRow[11] = $_[11]           ##  EXT

        $dataSet.Tables[1].Rows.Add($newRow)

        $rowCmt = "New row added...["+$dataSet.Tables[1].Rows.Count+"]"

        Write-Host $rowCmt  -ForegroundColor Green
    }
    Else {
        $duplicateRecordsCount++
        Write-Host "Already exists..."  -ForegroundColor Red
    }
}

In the above statement, $_ represents the current object in the loop. PS don’t have any quality operator like “==” in C#, but it uses “-eq” for equality check. Once the loop reaches to end, we will have unique records in DT1.

Export to CSV:

Now our last milestone, pushing them into EXCEL sheet. Follow the below code;

Write-Host "Exporting to excel " -ForegroundColor Cyan
$dataSet.Tables[1] | Export-Csv c:\logs\new_uote_docs.csv

With this, I am able to create a CSV file to be used in my JAVA program to upload into Windchill.

The following is teh complete code that I used to do this.

$dataSource = "C:\Users\pradeepy\Documents\uote_docs.mdb"
$selectQuery = "SELECT * FROM FULL_DOCS_INFO"
$dsn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$dataSource;"

Write-Host "Creating connection to database" -ForegroundColor Cyan
$dbCon = New-Object System.Data.OleDb.OleDbConnection $dsn
$sqlCmd = New-Object System.Data.OleDb.OleDbCommand $selectQuery, $dbCon
$dbCon.Open()

Write-Host "Creating datasets" -ForegroundColor Cyan
$dataSet = New-Object System.Data.DataSet
$sourceDataTable = New-Object System.Data.DataTable "SOURCE_UOTE_DOCS"
$finalDataTable = New-Object System.Data.DataTable "FINAL_UOTE_DOCS"
$dataSet.Tables.Add($sourceDataTable)
$dataSet.Tables.Add($finalDataTable)

Write-Host "Fill schema of the table into a data datable" -ForegroundColor Cyan
$dataAdaptor = New-Object System.Data.OleDb.OleDbDataAdapter $sqlCmd
$dataAdaptor.FillSchema($dataSet.Tables[1], [System.Data.SchemaType]::Mapped)
[void] $dataAdaptor.Fill($dataSet.Tables[0]);

Write-Host "Closing the connection" -ForegroundColor Cyan
$dbCon.Close();

Write-Host "No. of rows in dataset >" -ForegroundColor Green
$dataSet.Tables[0].Rows.Count | Select-Object

$duplicateRecordsCount = 0
$uniqRecordsCount = 0
$dataSet.Tables[0].Rows | ForEach-Object{
    If($dataSet.Tables[1].Select("[Doc Number] like '"+$_["Doc Number"]+"' and [Doc Revision] like '"+$_["Doc Revision"]+"'").Count -eq 0){
        
        $uniqRecordsCount++
        $newRow = $dataSet.Tables[1].NewRow()
        $newRow[0] = $uniqRecordsCount ##  ID
        $newRow[1] = $_[1]             ##  Name
        $newRow[2] = $_[2]             ##  DirectoryName 
        $newRow[3] = $_[3]             ##  FullName
        $newRow[4] = $_[4]             ##  Doc Name
        $newRow[5] = $_[5]             ##  Doc Number
        $newRow[6] = $_[6]             ##  Doc Revision
        $newRow[7] = $_[7]             ##  PROC_CODE
        $newRow[8] = $_[8]             ##  SUB_PROC_CODE
        $newRow[9] = $_[9]             ##  DOC_REV
        $newRow[10] = $_[10]           ##  DOC_REV_EXT
        $newRow[11] = $_[11]           ##  EXT

        $dataSet.Tables[1].Rows.Add($newRow)

        $rowCmt = "New row added...["+$dataSet.Tables[1].Rows.Count+"]"

        Write-Host $rowCmt  -ForegroundColor Green
    }
    Else {
        $duplicateRecordsCount++
        Write-Host "Already exists..."  -ForegroundColor Red
    }
}

Write-Host "No. of rows in new dataset >" -ForegroundColor Green
$dataSet.Tables[1].Rows.Count | Select-Object

Write-Host "Total duplicate records :" -ForegroundColor Red
Write-Host $duplicateRecordsCount

Write-Host "Exporting to excel :" -ForegroundColor Cyan
$dataSet.Tables[1] | Export-Csv c:\logs\new_uote_docs.csv
Advertisements