So far in this series, I’ve spent a lot of time showing how we can work with various ODATA web service APIs to get different sets of data from Business Central.
And as you may recall, the purpose of this series is to describe how you can combine data from Business Central with other applications that may not be in the cloud and may not be using Power BI.
In this post, we’re ready to talk about this specifically.
Please note: I’ve simplified some things for ease of presentation. You’ll see that as I build my script in PowerShell, I’m not including everything I normally would (e.g. try, catch, and secure passwords). However, I plan to post a version of the working script in GitHub, which will be more complete.
Let’s start with the Chart of Accounts request (which has the advantage of having no spaces in the URL):
We can create a PowerShell script that calls this and outputs the data in a grid view. The output is what’s called a PowerShell Custom Object, which is an important term you need to learn if you get involved in PowerShell (scroll to view all of it):
Let’s walk through this program.
The first lines are standard to any PowerShell script where you need to pass a credential. (You use similar code when calling SQL Server when you don’t have integrated security.)
With our credential, we can call our ODATA web service. We’re using a PowerShell cmdlet called Invoke-RESTAPI. It requires several parameters: URI, Authorization and Type. (If you look through the values, you’ll see they’re pretty self-explanatory).
We assign the output of Invoke-RESTAPI to a variable that will become a PowerShell custom object.
The next step (where we take the value of the custom object) will make more sense if we go back to the original JSON output we saw in Postman.
Looking below, you’ll see that the full JSON output has two top-level properties: @odata.context and value.
We only want the value, so we take $BCLISTJSON.value
Once we have that, we can use the standard PowerShell cmdlet Out-GridView to actually look at the data.
It looks like this:
We can do a lot of things with this BCLIST. For example, we can export it to a CSV by adding these two lines to the code:
When we run that and open the file in Visual Studio Code, we see that we have a CSV file:
But, really, what we’d like to do is put this data back into SQL Server.
There are many, many ways to get JSON data into SQL Server. But the easiest way (which works for all versions of SQL Server except Azure SQL Database) is Write-SQLTableData.
In my series on importing Excel to SQL Server, I have a whole set of my observations on the ins and outs of Write-SQLTableData.
The key point is that it takes a PowerShell Custom Object as input. And it will create a table for us if one doesn’t exist.
So, instead of using Out-GridView or Export-CSV, we can pipe the same output to Write-SQLTableData.
(And again, Write-SQLTableData will create a table if none exists.)
Here’s the code we need:
If this were a demo, I’d show you that no table existed (imagine that I just clicked refresh in my demo schema with the name BCGLAccounts):
And now, after we run the script:
One final note for this post: Write-SQLTableData doesn’t do a good job of guessing column types. So, you’ll need to update your columns appropriately.
Also, I’m working on creating a complete advanced example of pulling out individual GL entries and updating the database accordingly.
But that’s a topic for another day.