Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →Automate MYOB AccountRight Integration Tasks from PowerShell
Are you in search of a quick and easy way to access MYOB AccountRight data from PowerShell? This article demonstrates how to utilize the MYOB AccountRight Cmdlets for tasks like connecting to MYOB AccountRight data, automating operations, downloading data, and more.
The CData Cmdlets for MYOB AccountRight are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time and bidirectional access to MYOB AccountRight.
PowerShell Cmdlets or ADO.NET Provider?
The Cmdlets are not only a PowerShell interface to MYOB AccountRight, but also an SQL interface; this tutorial shows how to use both to create, retrieve, update, and delete MYOB AccountRight data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for MYOB AccountRight. To access MYOB AccountRight data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for MYOB AccountRight.
Once you have acquired the necessary connection properties, accessing MYOB AccountRight data in PowerShell can be enabled in three steps.
These properties are required when connecting to a company file (both for Cloud and On-Premise instances).
- CompanyFileId: You can find this by starting MYOB, opening your data file, and selecting Help -> About MYOB
- User: The username associated with your company file.
- Password: The password associated with your company file.
Connecting to a Cloud Instance
To connect to a cloud instance of MYOB, you can use the embedded OAuth credentials or create an OAuth app with MYOB. This process is detailed in the Help documentation.
Connecting to an On-Premise instance:
When connecting to an on-premise instance, you will need to set the following connection property in addition to those above:
- InitiateOauth: Set this to OFF.
- Url: The Url of your MYOB instance.
PowerShell
-
Install the module:
Install-Module MYOBCmdlets
-
Connect:
$myob = Connect-MYOB -OAuthClientId "$OAuthClientId" -OAuthClientSecret "$OAuthClientSecret" -CompanyFileId "$CompanyFileId" -CallbackURL "$CallbackURL" -User "$User" -Password "$Password"
-
Search for and retrieve data:
$type = "Bank" $accounts = Select-MYOB -Connection $myob -Table "Accounts" -Where "Type = `'$Type`'" $accounts
You can also use the Invoke-MYOB cmdlet to execute SQL commands:
$accounts = Invoke-MYOB -Connection $myob -Query 'SELECT * FROM Accounts WHERE Type = @Type' -Params @{'@Type'='Bank'}
ADO.NET
-
Load the provider's assembly:
[Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for MYOB AccountRight\lib\System.Data.CData.MYOB.dll")
-
Connect to MYOB AccountRight:
$conn= New-Object System.Data.CData.MYOB.MYOBConnection("OAuthClientId=YourClientId; OAuthClientSecret=YourClientSecret; CompanyFileId=yourCompanyFileId; CallbackURL=http://localhost:33333; User=companyFileUser; Password=companyFilePassword; InitiateOAuth=GETANDREFRESH") $conn.Open()
-
Instantiate the MYOBDataAdapter, execute an SQL query, and output the results:
$sql="SELECT Id, Name from Accounts" $da= New-Object System.Data.CData.MYOB.MYOBDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.id $_.name }
Update MYOB AccountRight Data
PowerShell
Update-MYOB -Connection $MYOB -Columns @('Id','Name') -Values @('MyId', 'MyName') -Table Accounts -Id "MyId"
ADO.NET
$cmd = New-Object System.Data.CData.MYOB.MYOBCommand("UPDATE Accounts SET Type='Bank' WHERE Id = @myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.MYOB.MYOBParameter("@myId","10456255-0015501366")))
$cmd.ExecuteNonQuery()
Insert MYOB AccountRight Data
PowerShell
Add-MYOB -Connection $MYOB -Table Accounts -Columns @("Id", "Name") -Values @("MyId", "MyName")
ADO.NET
$cmd = New-Object System.Data.CData.MYOB.MYOBCommand("INSERT INTO Accounts (Type) VALUES (@myType)", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.MYOB.MYOBParameter("@myType","Bank")))
$cmd.ExecuteNonQuery()
Delete MYOB AccountRight Data
PowerShell
Remove-MYOB -Connection $MYOB -Table "Accounts" -Id "MyId"
ADO.NET
$cmd = New-Object System.Data.CData.MYOB.MYOBCommand("DELETE FROM Accounts WHERE Id=@myId", $conn)
$cmd.Parameters.Add((New-Object System.Data.CData.MYOB.MYOBParameter("@myId","001d000000YBRseAAH")))
$cmd.ExecuteNonQuery()
CodeProject