API extraction to SQL Data Base
Can I get some information on how to extract data from Myob online into an SQL data Base to import into a corporate reporting software model, or is it possible to create a direct link.
Forum Discussion
Hi Andrew999
Not sure if you're referring to AccountRight or Essentials here but there are API endpoints for both that you can send GET requests to in order to fetch and return data to your application.
You can find the documentation for the endpoints here: https://developer.myob.com
Once you have a working application that gets the data from the endpoints you'll just need to whip up something that then posts the returned data into your DB.
Thanks Hayden,
I will go through this documentation.
Best Regards
Andrew
Hello Hayden,
I am trying to extract data from Online Accountright directly to excel using power query
I have my API key but as I am not a developer it is a bit difficult. If you can give me a few steps to guide me to the data I would appreciate it.
I use:
https://api.myob.com/accountright
and my key but get an error " a web API key can only be specified when a web API key name is provided. Help please!!
Then trying to understand the "Advanced "tab in excel PowerQuery "from Web" what do I fill in on URL parts, URL preview, command time out and HTTP request header parameters. If you can give me this as an example to access the API Sandbox demo that would be extremely helpful.
Thank you.
Did you find a solution to this?
Trying to do the same. Feels like MYOB want everyone to purchase Add-ons instead of providing an easy solution to replace ODBC for Excel users.
I feel its against Trade Practices Act to provide new software that no longer performs the same tasks as the old software you paid for. Instead making you pay for Add-ons
Cheers Mark
Hi MAC221267 , Andrew999 , when AccountRight Live was first introduced, MYOB attempted to provide an ODBC equivalent, but they realised that it had significant limitations, and developed the API instead, which is far superior.
To extract data to Excel from a cloud file, whatever process or code you use needs to be able to authenticate via oauth2, and navigate the 2FA requirement.
An easier approach is to make a backup of your cloud file, and restore it to your local library, where all you will need is a username and password.
For example, these are the URL parts of my data source which returns a list of all suppliers:
http://localhost:8080/AccountRight/<your file id>/Contact/Supplier
?x-myobapi-version="v2"
&Authorisation="Basic QWRtaW5pc3RyYXRvcjo="
Notes:
FileID can be found by entering http://localhost:8080/AccountRight/ into your browser and search for your company file
The second part starts with a ?
The third (and subsequent, if any) parts starts with a &
The phrase QWRtaW5pc3RyYXRvcjo= is the encoded form of <username>=<password>, in this case Administrator=, you can get this from base64encode.org.
I specify the file to be opened as json.
The full text of my query in Excel 2016 is as follows:
let
Source = Json.Document(Web.Contents("http://localhost:8080/AccountRight/<my file id>/Contact/Supplier" & "?x-myobapi-version=""v2""" & "&Authorisation=""Basic QWRtaW5pc3RyYXRvcjo=""")),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{0}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"ABN", "ABNBranch", "CreditLimit", "VolumeDiscount", "ExpenseAccountId", "UseSupplierTaxCode", "Memo", "PurchaseLayoutId", "HourlyBillingRate", "PrintedForm", "PurchaseComment", "ShippingMethod", "TaxIdNumber", "TermsId", "TaxCodeId", "FreightTaxCodeId", "CostPerHour", "IsReportable", "CoLastName", "FirstName", "IsIndividual", "EnteredId", "IsActive", "Addresses", "Description", "Tags", "CustomLists", "CustomFields", "CurrentBalance", "Uri", "Id", "RowVersion"}, {"Column1.ABN", "Column1.ABNBranch", "Column1.CreditLimit", "Column1.VolumeDiscount", "Column1.ExpenseAccountId", "Column1.UseSupplierTaxCode", "Column1.Memo", "Column1.PurchaseLayoutId", "Column1.HourlyBillingRate", "Column1.PrintedForm", "Column1.PurchaseComment", "Column1.ShippingMethod", "Column1.TaxIdNumber", "Column1.TermsId", "Column1.TaxCodeId", "Column1.FreightTaxCodeId", "Column1.CostPerHour", "Column1.IsReportable", "Column1.CoLastName", "Column1.FirstName", "Column1.IsIndividual", "Column1.EnteredId", "Column1.IsActive", "Column1.Addresses", "Column1.Description", "Column1.Tags", "Column1.CustomLists", "Column1.CustomFields", "Column1.CurrentBalance", "Column1.Uri", "Column1.Id", "Column1.RowVersion"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Column1",{"Column1.CoLastName", "Column1.Addresses", "Column1.ABN", "Column1.ABNBranch", "Column1.CreditLimit", "Column1.VolumeDiscount", "Column1.ExpenseAccountId", "Column1.UseSupplierTaxCode", "Column1.Memo", "Column1.PurchaseLayoutId", "Column1.HourlyBillingRate", "Column1.PrintedForm", "Column1.PurchaseComment", "Column1.ShippingMethod", "Column1.TaxIdNumber", "Column1.TermsId", "Column1.TaxCodeId", "Column1.FreightTaxCodeId", "Column1.CostPerHour", "Column1.IsReportable", "Column1.FirstName", "Column1.IsIndividual", "Column1.EnteredId", "Column1.IsActive", "Column1.Description", "Column1.Tags", "Column1.CustomLists", "Column1.CustomFields", "Column1.CurrentBalance", "Column1.Uri", "Column1.Id", "Column1.RowVersion"}),
#"Expanded Column1.Addresses" = Table.ExpandListColumn(#"Reordered Columns", "Column1.Addresses"),
#"Expanded Column1.Addresses1" = Table.ExpandRecordColumn(#"Expanded Column1.Addresses", "Column1.Addresses", {"Index", "Street", "City", "State", "PostCode", "Country", "Phone1", "Email"}, {"Column1.Addresses.Index", "Column1.Addresses.Street", "Column1.Addresses.City", "Column1.Addresses.State", "Column1.Addresses.PostCode", "Column1.Addresses.Country", "Column1.Addresses.Phone1", "Column1.Addresses.Email"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Column1.Addresses1", each ([Column1.Addresses.Index] = 1)),
#"Reordered Columns1" = Table.ReorderColumns(#"Filtered Rows",{"Column1.CoLastName", "Column1.Id", "Column1.Addresses.Index", "Column1.Addresses.Street", "Column1.Addresses.City", "Column1.Addresses.State", "Column1.Addresses.PostCode", "Column1.Addresses.Country", "Column1.Addresses.Phone1", "Column1.Addresses.Email", "Column1.ABN", "Column1.ABNBranch", "Column1.CreditLimit", "Column1.VolumeDiscount", "Column1.ExpenseAccountId", "Column1.UseSupplierTaxCode", "Column1.Memo", "Column1.PurchaseLayoutId", "Column1.HourlyBillingRate", "Column1.PrintedForm", "Column1.PurchaseComment", "Column1.ShippingMethod", "Column1.TaxIdNumber", "Column1.TermsId", "Column1.TaxCodeId", "Column1.FreightTaxCodeId", "Column1.CostPerHour", "Column1.IsReportable", "Column1.FirstName", "Column1.IsIndividual", "Column1.EnteredId", "Column1.IsActive", "Column1.Description", "Column1.Tags", "Column1.CustomLists", "Column1.CustomFields", "Column1.CurrentBalance", "Column1.Uri", "Column1.RowVersion"})
in
#"Reordered Columns1"The query expands various lists eg addresses, and filters on address 1.
I hope this is enough to get you started. I don't do much reporting directly into Excel from AccountRight Live as I have our own excellent report writer to work with :).
