hello in this video you will learn the basics of how to get JSON data from a
file or URL via an ODBC data source also you will learn how to use the retrieve
data in excel or any other application that supports ODBC connectivity just in
a few minutes in the next videos we will cover the
topics of how to get and post JSON data in reporting tools such as power bi
tableau or SSRS, ETL tools such as SSIS Informatica talent or Pentaho
programming languages such as C# (CSharp) Java or Python and sequel server
database let's get started. So let's say you have this URL and it returns this
JSON and within that JSON you have these products bread milk and win soda etc and
you want all those products to be present and excel sheet so that each
product would be located in its appropriate row right so bread in the
first row milk in the second one etc so to do that
visit ZappySys comm download ODBC PowerPack and install it once you install it
you will see that it installed ZappySys data gateway configuration which we will
not get into license manager a couple of links to ODBC data sources also under
the hood it installed ODBC JSON XML and maybe other drivers which will help us
to achieve our goal so to do that you will have to open all the BC data source
administrator so open it by click start menu item the button then type
ODBC and then locate ODBC data source administrator and simply open it once
you open it now you will see that you can create user DSN and system DSN the
SN stands for data source name and so user DSN is useful then it's only your
windows user will be accessing the data source and create system DSN for all the
users not just your user all the users and what's helpful about system DSN and
useful is that windows services will also be using system DSN since they
cannot access user DSN so for our test let's use user DSN let's click Add
select the episodes JSON driver and actually is JSON slash REST API driver
let's select it let's copy the URL here we can test the connection connection is
okay now go to preview window tab and we can click preview data and you see the
preview is not the result we expect because the driver just took this
property and this probability and converted them into column each one into
a column but there is only single row here no separate individual products so
to so to fix that we can select a table which is generated automatically by JSON
driver and basically all JSON properties that our arrays are generated or
converted into a table and you will see it in this list so I selected value I
can click preview data again and I will see that all my products are listed here
but I want actually to go the more complicated way I will bring back the
query head so this is the wrong one go back to properties and I will use this
filter so this filter allows me to select any data I want so if I select in
here name it will just select the names of the products but I will select value
so it will select all the properties and will bring me back all the properties as
you see here so now let me copy this query we will be ready to bring it into
an excel i'm clicking ok to save the any data source bringing back my excel
clicking data from other sources from Microsoft query selecting the data
source I just created selecting any table because I will be using a query
instead of a table clicking that I want to modify a query clicking sequel button
to modify the query pasting it clicking ok closing clicking ok and voila
all the products were imported so yeah so that concludes our example how to
load data from a URL into an excel or any other application that supports ODBC
connectivity and now we can move and modify our example to load data from
a file I will do it very quickly and we'll show you just the preview and that
it's doable okay I am selecting here persons and you
know what I will use a wild card here and basically it will do the same thing
it will select all the JSON files if I had more than I mean if there were more
than persons JSON so I will use this query this file path going to preview
clicking preview data and as you see persons are listing here so that's it
that's how simple and fast you can load URL or a file with JSON data into an
Excel or any other application that supports ODBC where the ZappySys json
rest api driver in the next video we will delve into a more complicated
example in which we will be making REST API calls with authentication and
pagination thanks for watching