{"id":4828,"date":"2018-09-14T19:08:36","date_gmt":"2018-09-14T19:08:36","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=4828"},"modified":"2018-09-28T17:34:39","modified_gmt":"2018-09-28T17:34:39","slug":"call-rest-api-powershell-script-export-json-csv","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/call-rest-api-powershell-script-export-json-csv\/","title":{"rendered":"Call REST API in PowerShell Script – Export JSON to CSV"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"<\/a>In this article we will cover how to call REST API in PowerShell and export result to CSV file. If you want to export JSON based API result to file then you have to consider few things. Such as API Authentication, API Pagination, Parsing JSON, Error Handling and many more. Luckily we can use ODBC Driver like this one<\/a> to call API services and get data from RESTful services using familiar SQL query language.<\/p>\n

ZappySys ODBC driver<\/strong> can take care most of details about your API calls and data parsing so you can focus on business problem.<\/p>\n

Requirements<\/h2>\n

Before we start step by step tutorial let’s make sure we have necessary tools installed.<\/p>\n

    \n
  1. You have PowerShell ISE – Editor for PowerShell (Search in your Start menu to confirm)<\/li>\n
  2. Make sure you have installed ZappySys ODBC PowerPack<\/a><\/li>\n<\/ol>\n

    Getting Started<\/h2>\n

    Once you have installed ODBC PowerPack<\/a> we are ready to look at few examples on how to call REST API in PowerShell.<\/p>\n

    Example 1 – Call REST API and Export JSON data to CSV in PowerShell<\/h3>\n

    Open PowerShell ISE (Editor) and Paste below code. Click<\/p>\n

    $conn = New-Object System.Data.Odbc.OdbcConnection\r\n$conn.ConnectionString = \"DRIVER={ZappySys JSON Driver}\"\r\n\r\n#--OR-- Use DSN name\r\n#$conn.connectionstring = \"DSN=MyDSNName\"\r\n\r\n$conn.Open()\r\n\r\n# -------------------------------------------------------------------------------\r\n# In powershell $ is special char so we used `$ in below string to escape it. \r\n# Also We used multi string start with \"@<new line> and ends with <new line>\"@\r\n# -------------------------------------------------------------------------------\r\n$sql = \r\n@\"\r\nSELECT * FROM value \r\nWITH (SRC='https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?`$format=json')\r\n\"@\r\n\r\n$cmd = $conn.CreateCommand()\r\n$cmd.CommandText = $sql\r\n\r\n$dataset = New-Object System.Data.DataSet\r\n#Load data in DataSet\r\n(New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($dataSet)\r\n\r\n#Export datatable to file in CSV format\r\n$dataset.Tables[0] | ConvertTo-csv -NoTypeInformation -Delimiter \"`t\" | Out-File \"c:\\temp\\dump.csv\" -fo\r\n\r\nWrite-Host \"Total rows $($dataSet.Tables[0].Rows.Count)\"\r\n$conn.Close()<\/pre>\n
    Here is what it will look like once you execute.<\/p>\n
    \"Example<\/a>
    Example : Call REST API in PowerShell and Export JSON data to CSV File<\/p><\/div>\n
     <\/p>\n
    Example 2 – Reading REST API data using ODBC DSN (User Interface Mode) in PowerShell<\/h3>\n
    In previous section we saw simple script which was using DSN less connection string (i.e. DRIVER={xxxxxxx} ).<\/p>\n
    Now let’s look at another way to call API (i.e. Use DSN=xxxxx in your connection string). We will use ODBC Driver’s User Interface to configure various properties. We will also use query preview tool to test your API query. Finally we will use DSN name in Our PowerShell Script.<\/p>\n
    Advantage of using ODBC DSN is you don’t have to expose credentials in your PowerShell Script. Also when you have multiple scripts using same connection to query multiple API endpoints it becomes very easy to reuse just DSN name rather than many connection string settings.<\/p>\n
    So let’s get started<\/p>\n
      \n
    1. To do this, we will first open the ODBC Data Source (32 bit):\n
    2. Use the User DSN page and press Add<\/strong>\n
    3. Add the ZappySys JSON Driver\n
    4. Name your data source (e.g. in below screenshot its ZappySys JSON PowerBI<\/strong>). This will be used in our Script to refer in DSN=xxxxxxx for connectionstring.<\/li>\n
    5. It is time to connect with OData. In\u00a0Data Source (URL or File Path),<\/strong> we will use the following URL.\n
      https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json<\/pre>\n
      After URL select Filter (Basically its a setting to flatten your data from selected hierarchy). In our case Filter = $.value[*]<\/strong><\/p>\n
      UI Mode (Simple View) – New Version<\/strong><\/p>\n
      \"Simple
      Simple View – Configure ZappySys JSON \/ REST API Driver Properties<\/p><\/div>\n
      Grid Mode (Advanced View) – Old Version<\/strong><\/p>\n
      \"Advanced<\/a>
      Advanced View – ZappySys JSON \/ REST API Driver Properties<\/p><\/div>\n
       <\/li>\n
    6. We can now generate queries using a format similar to SQL. Click Select Table<\/strong> and select value<\/strong>. This option will generate a query to retrieve all the data automatically. Next, press Preview Data:<\/strong><\/strong>\"create<\/a><\/li>\n
    7. When Preview<\/strong> is pressed, you can visualize the data.\n
    8. You should also review several other examples to learn many other features of Driver Query language.\n
      \"ZappySys
      ZappySys JSON \/ REST Driver – Query Examples for PowerShell<\/p><\/div><\/li>\n
    9. ZappySys is pretty cool because you can run any type of query like SQL. For example, you can run a query to RESP API using like<\/strong> and or<\/strong>:\n
      \/* \r\nObject name can be wrapped in double quotes or rectangle brackets\r\nuseful if name has space, dash or dot \r\n*\/\r\nSELECT \r\n CustomerID,\r\n CompanyName,\r\n ContactName,\r\n ContactTitle,\r\n Address,\r\n City,\r\n Region,\r\n PostalCode,\r\n Country,\r\n Phone,\r\n Fax\r\nFROM [value]\r\nWHERE ContactTitle='Owner'<\/pre>\n<\/li>\n<\/ol>\n
       <\/p>\n
      That’s it once you save your DSN you can change your previous script as below to use DSN<\/p>\n
      $conn = New-Object System.Data.Odbc.OdbcConnection\r\n\r\n$conn.connectionstring = \"DSN=ZappySys JSON PowerBI\"\r\n$conn.Open()\r\n$sql = \"SELECT * FROM value\"\r\n$cmd = $conn.CreateCommand()\r\n$cmd.CommandText = $sql\r\n$dataset = New-Object System.Data.DataSet\r\n\r\n#Load data in DataSet\r\n(New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($dataSet)\r\n\r\n#Export datatable to file in CSV format\r\n$dataset.Tables[0] | ConvertTo-csv -NoTypeInformation -Delimiter \"`t\" | Out-File \"c:\\temp\\dump.csv\" -fo\r\n\r\nWrite-Host \"Total rows $($dataSet.Tables[0].Rows.Count)\"\r\n$conn.Close()<\/pre>\n
       <\/p>\n
      Example 3 – Reading data from JSON\u00a0 files (Single \/ Multiple Files) in PowerShell<\/h3>\n
      If you like to read JSON data from local file then use File Path rather than URL as below. Notice that we are reading data from multiple files using Wildcard pattern (*.json). For reading from multiple file make sure all files have same structure.<\/p>\n
      $conn = New-Object System.Data.Odbc.OdbcConnection\r\n\r\n$conn.connectionstring = \"DRIVER={ZappySys JSON Driver}\"\r\n$conn.Open()\r\n$sql = \"SELECT * FROM value WITH (SRC='C:\\temp\\File*.json')\"\r\n$cmd = $conn.CreateCommand()\r\n$cmd.CommandText = $sql\r\n$dataset = New-Object System.Data.DataSet\r\n\r\n#Load data in DataSet\r\n(New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($dataSet)\r\n\r\n#Export datatable to file in CSV format\r\n$dataset.Tables[0] | ConvertTo-csv -NoTypeInformation -Delimiter \"`t\" | Out-File \"c:\\temp\\dump_multi_files.csv\" -fo\r\n\r\nWrite-Host \"Total rows $($dataSet.Tables[0].Rows.Count)\"\r\n$conn.Close()<\/pre>\n
       <\/p>\n
      Example 4 – POST data to REST API URL (Supply Header \/ Body) in PowerShell<\/h3>\n
      Now let’s look at some example to POST data to API.\u00a0 In below example we will also see how to pass HTTP headers and Body for POST request. POST requests usually made to Create or Update existing record. However sometimes you will call POST request to read data too (specially you have to supply many parameters in Body rather than URL to call your request).<\/p>\n
      $conn = New-Object System.Data.Odbc.OdbcConnection\r\n\r\n$conn.connectionstring = \"DRIVER={ZappySys JSON Driver}\"\r\n$conn.Open()\r\n$sql = @\"\r\nSELECT * FROM `$\r\nWITH \r\n( \r\n  SRC='http:\/\/httpbin.org\/post' ,\r\n  METHOD='POST' ,\r\n  HEADER='Content-Type: application\/json || x-hdr1: SomeValue' ,  \r\n  BODY='{ id:1, notes:\"My Line1\\r\\nMy Line2\" }'\r\n)\r\n\"@\r\n$cmd = $conn.CreateCommand()\r\n$cmd.CommandText = $sql\r\n$dataset = New-Object System.Data.DataSet\r\n\r\n#Load data in DataSet\r\n(New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($dataSet)\r\n\r\n#Export datatable to file in CSV format\r\n$dataset.Tables[0] | ConvertTo-csv -NoTypeInformation -Delimiter \"`t\" | Out-File \"c:\\temp\\dump_post_response.csv\" -fo\r\n\r\nWrite-Host \"Total rows $($dataSet.Tables[0].Rows.Count)\"\r\n$conn.Close()<\/pre>\n
       <\/p>\n
      Example 5 – Upload File Content to Web API in PowerShell<\/h3>\n
      Now let’s look at example where you have to Upload File Content to Web API using PowerShell Script.<\/p>\n
      $conn = New-Object System.Data.Odbc.OdbcConnection\r\n\r\n$conn.connectionstring = \"DRIVER={ZappySys JSON Driver}\"\r\n$conn.Open()\r\n$sql = @\"\r\nSELECT * FROM `$\r\nWITH \r\n( \r\n  SRC='http:\/\/httpbin.org\/post' ,\r\n  METHOD='POST' ,\r\n  HEADER='Content-Type: application\/json || x-hdr1: SomeValue' ,  \r\n  BODY='@c:\\temp\\test.json',\r\n  IsMultiPart='True'\r\n)\r\n\"@\r\n$cmd = $conn.CreateCommand()\r\n$cmd.CommandText = $sql\r\n$dataset = New-Object System.Data.DataSet\r\n\r\n#Load data in DataSet\r\n(New-Object System.Data.Odbc.OdbcDataAdapter($cmd)).Fill($dataSet)\r\n\r\n#Export datatable to file in CSV format\r\n$dataset.Tables[0] | ConvertTo-csv -NoTypeInformation -Delimiter \"`t\" | Out-File \"c:\\temp\\file-upload-response.csv\" -fo\r\n\r\nWrite-Host \"Total rows $($dataSet.Tables[0].Rows.Count)\"\r\n$conn.Close()<\/pre>\n
      REST API with Pagination in PowerShell<\/h2>\n
      Even we set up ODBC Data Source to get the data, it may not be enough. Usually, if you are getting a huge data set from API provider, it won't give it to you in one HTTP response. Instead, it gives back only a subset of data and provides a mechanism for data pagination. The good news is that\u00a0ZappySys ODBC Driver<\/em> includes many options to cover virtually any pagination method.<\/div>\r\n
      Below you will find a few examples of API pagination. If you need something more sophisticated check the below link (the article was written for SSIS PowerPack but UI options and concepts apply to ODBC Driver too):<\/span><\/div>\r\n
      https:\/\/zappysys.com\/blog\/ssis-rest-api-looping-until-no-more-pages-found\/<\/a><\/div>\r\n
      Paginate by Response Attribute<\/h3>\r\nThis example shows how to paginate API calls where you need to paginate until the last page detected. In this example, next page is indicated by some attribute called nextlink (found in response). If this attribute is missing or null then it stops fetching the next page.\r\n
      SELECT * FROM $\r\nWITH(\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/pagination_nextlink_inarray_1.json'\r\n,NextUrlAttributeOrExpr = '$.nextlink'  --keep reading until this attribute is missing. If attribute name contains dot then use brackets like this $.['my.attr.name']\r\n)<\/pre>\r\n
      Paginate by URL Parameter (Loop until certain StatusCode)<\/h3>\r\nThis example shows how to paginate API calls where you need to pass page number via URL. The driver keeps incrementing page number and calls next URL until the last page detected (401 error). There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found).\r\n
      SELECT * FROM $\r\nWITH (\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/page-xml.aspx?page=1&mode=DetectBasedOnResponseStatusCode'\r\n,PagingMode='ByUrlParameter'\r\n,PagingByUrlAttributeName='page'\r\n,PagingByUrlEndStrategy='DetectBasedOnResponseStatusCode'\r\n,PagingByUrlCheckResponseStatusCode=401\r\n,IncrementBy=1\r\n)<\/pre>\r\n
      Paginate by URL Path (Loop until no record)<\/h3>\r\nThis example shows how to paginate API calls where you need to pass page number via URL Path. The driver keeps incrementing page number and calls next URL until the last page is detected. There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found).\r\n
      SELECT * FROM $\r\nWITH (\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/cust-<%page%>.xml'\r\n,PagingMode='ByUrlPath'\r\n,PagingByUrlAttributeName='<%page%>'\r\n,PagingByUrlEndStrategy='DetectBasedOnRecordCount'\r\n,IncrementBy=1\r\n)<\/pre>\r\n
      Paginate by Header Link (RFC 5988)<\/h3>\r\nAPI like GitHub \/ Wordpress use Next link in Headers (RFC 5988<\/a>)\r\n
      SELECT * FROM $\r\nLIMIT 25\r\nWITH(\r\n\t Src='https:\/\/wordpress.org\/news\/wp-json\/wp\/v2\/categories?per_page=10'\r\n\t,PagingMode='ByResponseHeaderRfc5988'\r\n\t,WaitTimeMs='200' --\/\/wait 200 ms after each request\r\n)<\/pre>\r\n <\/div>\n
      REST API\u00a0Error Handling in PowerShell<\/h2>\n
      Sometimes errors occur... they just do and there is nothing you can do! Or can you? Actually, in ODBC PowerPack you can handle them in two ways.\r\n
      METHOD 1 - Using Error Handling Options<\/h3>\r\n\"\"\r\n
      When to use?<\/h4>\r\nYou may want to use them when your source is a resource located\u00a0on\u00a0the Internet; e.g. a file on a website, a file on an FTP server or just a plain API HTTP response. By default, when a remote server returns an error, data retrieval is stopped, an error is raised and no data is given back to you. This might not be always desirable.\r\n
      Scenario 1<\/h4>\r\nImagine a scenario, that there is a\u00a0web server which\u00a0each day at 12 AM releases a new JSON file with\u00a0that day's date as filename, e.g. http:\/\/www.some-server.com\/data\/2018-06-20.json<\/em><\/span>. And, of course, you want to download it and use it daily in your Power BI report. But you have a problem: Power BI report data sources are\u00a0refreshed each\u00a0hour and you may get\u00a0HTTP 404 status code<\/a>\u00a0(no file was found) when a\u00a0file is not released yet. Which consequentially means other data sources won't be updated as well and you will see old and cached data on the report. That's where\u00a0you could use\u00a0Continue on any error<\/span><\/strong>\u00a0or Continue when Url is invalid or missing (404 Errors)<\/span><\/strong>\u00a0to avoid an error being raised and let other data sources to be updated.\r\n
      Scenario 2<\/h4>\r\nAnother scenario is when you expect a web server to raise some kind of HTTP error when accessing a URL. You don't want ODBC Data Source to raise an error but instead, you want to get response data. That's where you can use\u00a0Continue on any error<\/span><\/strong>\u00a0or alike together with\u00a0\u00a0Get response data on error<\/span><\/strong>\u00a0to continue on an error and get the data:\r\n\r\n\"\"\r\n
      METHOD 2 - Using Connection [Retry Settings]<\/h3>\r\nAnother scenario you may run into is a buggy web server. You ask it to give you some file or data and it, like a snotty kid, just doesn't give it to you! You have to ask twice or thrice before it does its job. If that's the case, you have to retry HTTP requests using Connection<\/em>:\r\n\r\n\"\"<\/div>\n
      REST API Authentication\u00a0in PowerShell (Basic Auth, OAuth, JWT, SOAP WSS)<\/h2>\n
      If you need to authenticate or authorize your user to access a web resource, you will need to use one of\u00a0the Connections:<\/em><\/div>\r\n