Click or drag to resize

Powershell Export example

Shows different export options in CmcLibNet.

Basic export example

I will be taking advantage of some PowerShell 5 features. It is the default version on Windows 10.

First of all, we must tell PowerShell that we want to use CmcLibNet. We also bring in the namespaces we will use so that we don't have to fully qualify them every time. Important: if you installed the Vovin.CmcLibNet.dll somewhere else, change the path reference accordingly.

PowerShell
Copy
using namespace Vovin.CmcLibNet.Database # requires PS 5 or higher
using namespace Vovin.CmcLibNet.Export # requires PS 5 or higher
Add-Type -Path "C:\Program Files\Vovin\Vovin.CmcLibNet\Vovin.CmcLibNet.dll" # the default path of the assembly when you used the installer

We want to use features in the ExportEngine

PowerShell
Copy
$export = New-Object ExportEngine # short for $export = New-Object -TypeName Vovin.CmcLibNet.Export.ExportEngine

The export engine interface is also a property of the Vovin.CmcLibNet.Application object. Depending on the code-style you like you can also get it like this (commented out here because we already have a reference):

PowerShell
Copy
# $app = New-Object -TypeName Vovin.CmcLibNet.Application
# $export = $app.Export

Now define an export path to use for this example (our Desktop)

PowerShell
Copy
$exportPath = [Environment]::GetFolderPath([Environment+SpecialFolder]::Desktop) # we're being PS pedantic here

There are three types of datasets you can export: categories, views and cursors.

Export the Contact category to a file named contacts.xml:

PowerShell
Copy
$export.ExportCategory("Contact", (Join-Path -Path $exportPath -ChildPath "contacts.xml")) # Join-Path is again being pedantic about PS but okay

The export engine cleans up after itself after a succesful export.

Export view Account List (a view in the Tutorial database) to a file named accounts.xml.

PowerShell
Copy
$export = New-Object ExportEngine
$export.ExportView("Account List", $exportPath + "Account List View.xml") # View names in Commence are case-sensitive!

The first line of which is short for: $export = New-Object -TypeName Vovin.CmcLibNet.Export.ExportEngine. An example of exporting cursors can be found in the remainder of this document.

Note that not all viewtypes can be exported. This is a limitation of Commence.

Tip: if you want to export the currently active view, supply either null or an empty string: ExportView(null,file,settings).

In the above examples, the resulting export files will be in Xml format. Xml is the default format of the export engine. Node names and values will be escaped so as to to comply with XML specs

The file-extension you supply to the export files has no special meaning, it can be anything.

CmcLibNet will (by default) export connected items as separate nodes, not as delimited strings like Commence does.

Custom formats

To export to other formats, you have to supply an argument of type IExportSettings.

You can create a one from scratch with

PowerShell
Copy
$BrandNew_ExportSettingsObject = New-Object ExportSettings # create Settings object from scratch

But it is usually easier to use the default settings property provided by the export engine.

PowerShell
Copy
$settings = $export.Settings

The settings object controls all export customization. One of its most important is the export format (see ExportFormat).
CmcLibNet can also export to plain text, Html, Json and Microsoft Excel.

Let's see some of this in action. Set it to Json

PowerShell
Copy
$settings.ExportFormat = [ExportFormat]::Json # export to JSON

Tell it to skip connected values. This is useful when exporting categories when you do not want all connected data.

PowerShell
Copy
$settings.SkipConnectedItems = $true

Make Commence return values in a consistent format. In CmcLibNet this includes connected values, something which Commence does not do natively.

PowerShell
Copy
$settings.Canonical = $true

When you export data, chances are you will want to import them into something else. That often requires some form of field-mapping. For argument's sake, let's say Commence uses the fieldnames 'firstName' and 'lastName', but the receiving application uses 'First_Name' and 'Last_Name'. There are several ways to overcome this. One is to define a view in Commence containing the two fields, and give the fields custom labels using Design View. In that case, you can export the view and tell the export engine to use the labels, not the fieldnames:

PowerShell
Copy
$settings.HeaderMode = [HeaderMode]::Columnlabel

Let's see this in action. It uses a Report view present in the Tutorial database:

PowerShell
Copy
export.ExportView("All Contacts-Report", $exportPath + "Contacts with labels as property names.json", $settings) # Create json file with the field labels (and not field names) as property names

You could also get really funky and define your own headers (columnnames), ignoring everyting in Commence

Beware that this a little more tricky. The custom names must be unique and match the number of Commence fields! I.e. it must match the number of fields present in the dataset (be it a category, view, or cursor) regardless of using SkipConnectedItems.

To show an example with custom headers without actually modifying the Tutorial database, we have to get a little fancy. You will also see the use of some other features of CmcLibNet. Exporting isn't limited to categories or views. You can also export a cursor.

PowerShell
Copy
$settings.ExportFormat = [ExportFormat]::Json
$settings.CustomHeaders = @("First_Name","Last_Name") # supply an array of custom headers
$settings.HeaderMode = [HeaderMode]::CustomLabel
$db = New-Object CommenceDatabase
$cursorToExport = $db.GetCursor("Contact") # create cursor on contact category
$cursorToExport.Columns.AddDirectColumns("firstName","lastName") # define columns to use
$cursorToExport.Columns.Apply() # apply columnset
$cursorToExport.ExportToFile($exportPath + "Contacts with custom property names.json", $settings) # create json file with custom property names 'First_Name' and 'Last_Name'.

CmcLibNet can export to HTML as well. Note that Commence already supports exporting to HTML (Save View as HTML, or via the Report Viewer). CmcLibNet just adds additional functionality. Note that CmcLibNet adds classes and identifiers to every exported element that you can use in an (optional) CSS file.

Copy
$export.Settings.ExportFormat = [ExportFormat]::Html
$export.Settings.CSSFile = "some-stylesheet.css" # include a stylesheet reference in the exported HTML file. Note that this is just a dummy that will be ignored.
$export.Settings.HeaderMode = [HeaderMode]::Columnlabel # reset header mode. Now would have been an appropriate time to create a new Settings object!
# 'All Contacts-Report' is a view in the Tutorial database
$export.ExportView("All Contacts-Report", $exportPath + "Contacts with fieldlabels as table headers.html")

You can also export to Text. Note: in most cases, using the built-in export features in Commence are easier and much faster. CmcLibNet will be considerably slower, but it does give you better control over the export.

The two main enhancements include:

  1. supplying a custom field delimiter

  2. exporting connected fields other than the Name field(!)

PowerShell
Copy
$settings.ExportFormat = [ExportFormat]::Text
$settings.HeadersOnFirstRow = $false # set to $true to include fieldnames/labels at the top
$settings.TextDelimiter = "|" # use pipe symbol as delimiter. Default is tab.
$settings.TextQualifier = "#" # use (pound) symbol as text-qualifier. Default is " (quote).
$export.ExportCategory("Contact", $exportPath + "Contact.txt", $settings)

Last but not least you can export to Microsoft Excel. That is fairly straightforward. There are a few options you can set, refer to ExcelUpdateOptions.

Excel does not have to be installed. The export file will be in the xlsx format, so reading the file requires Excel 2007 or higher.

For this to work, you need a Full install of Vovin.CmcLibNet, since it depends on an additional component.

PowerShell
Copy
$settings.CustomRootNode = "MySheet" #define a sheet name (optional)
$settings.ExportFormat = [ExportFormat]::Excel
$export.ExportCategory("Contact", $exportPath + "Contact.xlsx", $settings)

The end. Have fun exporting!

Complete script

The complete script

PowerShell
Copy
using namespace Vovin.CmcLibNet.Database
using namespace Vovin.CmcLibNet.Export
Add-Type -Path "C:\Program Files\Vovin\Vovin.CmcLibNet\Vovin.CmcLibNet.dll" # the default path, adjust if necessary
$export = New-Object ExportEngine # short for: $export = New-Object -TypeName Vovin.CmcLibNet.Export.ExportEngine
$exportPath = [Environment]::GetFolderPath([Environment+SpecialFolder]::Desktop)
$export.ExportCategory("Contact", (Join-Path -Path $exportPath -ChildPath "contacts.xml"))
$export.ExportView("Account List", (Join-Path -Path $exportPath -ChildPath "Account List View.xml")) # note that view names are case-sensitive!
$BrandNew_ExportSettingsObject = New-Object ExportSettings # create Settings object from scratch
$settings = $export.Settings
$settings.ExportFormat = [ExportFormat]::Json # export to JSON
$settings.SkipConnectedItems = $true
$settings.Canonical = $true
$settings.HeaderMode = [HeaderMode]::Columnlabel
$export.ExportView("All Contacts-Report", (Join-Path -Path $exportPath -ChildPath "Contacts with labels as property names.json"), $settings) # Create json file with the field labels (and not field names) as property names
$settings.ExportFormat = [ExportFormat]::Json
$settings.CustomHeaders = "First_Name","Last_Name" # PS automatically creates the array
$settings.HeaderMode = [HeaderMode]::CustomLabel
$db = New-Object CommenceDatabase
$cursorToExport = $db.GetCursor("Contact") # create cursor on contact category
$cursorToExport.Columns.AddDirectColumns("firstName","lastName") # define columns to use
$cursorToExport.Columns.Apply() # apply columnset
$cursorToExport.ExportToFile((Join-Path -Path $exportPath -ChildPath "Contacts with custom property names.json"), $settings) # create json file with custom property names 'First_Name' and 'Last_Name'.
$export.Settings.ExportFormat = [ExportFormat]::Html
$export.Settings.CSSFile = "some-stylesheet.css" # include a stylesheet reference in the exported HTML file. Note that this is just a dummy that will be ignored.
$export.Settings.HeaderMode = [HeaderMode]::Columnlabel # reset header mode. Now would have been an appropriate time to create a new Settings object!
# 'All Contacts-Report' is a view in the Tutorial database
$export.ExportView("All Contacts-Report", $(Join-Path -Path $exportPath -ChildPath "Contacts with fieldlabels as table headers.html"))
$settings.ExportFormat = [ExportFormat]::Text
$settings.HeadersOnFirstRow = $false # set to $true to include fieldnames/labels at the top
$settings.TextDelimiter = "|" # use pipe symbol as delimiter. Default is tab.
$settings.TextQualifier = "#" # use (pound) symbol as text-qualifier. Default is " (quote).
$export.ExportCategory("Contact", (Join-Path -Path $exportPath -ChildPath "Contact.txt"), $settings)
# Exporting to Excel requires that you did a full install of Vovin.CmcLibNet
# because it relies on an additional component
$settings.ExportFormat = [ExportFormat]::Excel
$settings.CustomRootNode = "MySheet"
$export.ExportCategory("Contact", (Join-Path -Path $exportPath -ChildPath "Contact.xlsx"), $settings)
$export.Close() # optional, ExportEngine releases all COM-references automatically