使用powershell連接OPENDATA資料

最近在測試POWERBI工具，本來想由台灣的OPENDATA網站看看是否能有相關的API可用。但測試了一下，大部份提供的網址都沒法正常用ODATA方式讀取。
後來剛好有個專案上的需求，需要讀取一些資料，並轉入到資料庫中。測試了大半天，總算有點小心得。


作業系統：Windows 10
powersell版本： Windows PowerShell ISE

寫入資料庫版本：Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64)

資料來源：商業司提供的API  http://data.gcis.nat.gov.tw/od/rule
使用前可先參考說明 下載經濟部商業司開放資料供應清單
提供了很多的API可用，但使用起來，覺得每個API提供的結果都好像少了一些想要的欄位。
然後，每個API都沒法一次性全部撈出想要的結果。

不過，每個API都有提供範例語法可參考，算是很佛系服務的啦。

因為商業司採白名單IP開放，開始用之前，還要先EMAIL申請書，使用系統介接API之單位或個人請填寫使用告知書
等收到EMAIL啟用通知後，才能使用這些API。
回覆的EMAIL中，還註明了使用上的一些限制，如下：

平台的介接限制：
系統最大連線數量:100
申請者最大同時連線數量:3
申請者每日最大介接次數:1000000


為了要測試可回傳多筆的JSON資料，所以使用 32.(測試)營業項目代碼( A農、林、漁、牧業 )查公司，指定細項 A101011 種苗業的公司有哪些。

範例的API如下，
http://data.gcis.nat.gov.tw/od/data/api/11944415-69DE-4770-8D19-1FD8732AA81A?$format=json&$filter=Business_Item eq A101011 and Register_Organization eq 02 and Capital_Stock_Amount eq F

截取參數說明，其中紅框內的參數，我使用自建的資料表，依指定的SQL條件可動態組出，所以公司申登機關代碼表共23家，及資本額範圍共7種，所以應有23*7=161次的API Request送出。





第一次寫powersell，GOOGLE了許多寫法，測試了半天，好像有點小成果，手刻了一段script，有時間再來精進一下。

二個注意事項：
1.API中含有一些關鍵字，得要使用跳脫符號避開。參考了這篇文章，在powershell中，使用$要前置backtick "`"，不然API URL中，$開頭的參數都會消失不見。
2.API回傳的JSON是UTF8，在powershell使用Invoke-WebRequest接回來時，中文都變?了，所以要指定編碼。 


$sqlserver = "myserver"
$database = "OpenData"
$connstring = "Server=$sqlserver;Database=$database;Integrated Security=True" 
#使用自建的資料表,組合出公司申登機關代碼*公司資本額範圍代碼組合的URL
$query = "declare @url135 nvarchar(max) ='http://data.gcis.nat.gov.tw/od/data/api/11944415-69DE-4770-8D19-1FD8732AA81A?`$format=json&`$filter=Business_Item eq A101011 and Register_Organization eq {1} and Capital_Stock_Amount eq {2}'" +
                   " select top 1 replace(replace(@url135,'{1}',a.OrgnCode),'{2}',b.RangeCode) as url,a.OrgnCode,b.RangeCode from RegisterOrgn a full join SalesRange b on 1= 1 where a.OrgnCode = '01' and b.RangeCode='F' ;"

$sqlconn = New-Object System.Data.SqlClient.SqlConnection
$sqlconn.ConnectionString = $connstring
$sqlconn.Open()
$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $sqlconn
$sqlcmd.CommandText = $query


$sqladapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqladapter.SelectCommand = $sqlcmd
$ds = New-Object System.Data.DataSet
[void]$sqladapter.Fill($ds)
$sqlconn.Close()
$table = $ds.Tables[0]

#逐API URL呼叫
foreach ($row in $table){
    $request = $row.Item(0) 
    $orgn = $row.Item(1)   
    $range = $row.Item(2)
    $result = Invoke-WebRequest $request
    #指定UTF8
    $char=$result.RawContentStream.ToArray()
    $str = [Text.Encoding]::UTF8.GetString($char)
    $myobj = ConvertFrom-Json $str

    $sqlconn.Open() 
    $cmd = $sqlconn.CreateCommand()
    #逐JSON筆數取欄位資料
    foreach($obj in $myobj) { 
        $cmd.CommandText = "INSERT INTO OpenResult (CompanyNo,CompanyName,DataType,OrgnCode,RangeCode,AddDate,Url) VALUES (@col1,@col2,32,@col3,@col4,getdate(),@col5)"
        $cmd.Parameters.AddWithValue("@col1", $obj.Business_Accounting_NO); 
        $cmd.Parameters.AddWithValue("@col2", $obj.Company_Name);  
        $cmd.Parameters.AddWithValue("@col3", $orgn);      
        $cmd.Parameters.AddWithValue("@col4", $range);       
        $cmd.Parameters.AddWithValue("@col5", $request);   
        $cmd.ExecuteNonQuery() 
        $cmd.Parameters.clear(); 
    }  
    $sqlconn.Close()     
 }