2018年6月26日 星期二

使用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()     
 }

 

2018年6月16日 星期六

SQL2017 複製查詢結果,換行符號無作用?

改用SQL Server Management Studio 2017有一段時間了。

在執行一段之前寫的SQL要組出一段語法時,里頭包含了使用CHAR(10)當作換行符號的文字,在輸出結果中,使用複製貼上的方式要進行執行時,發現換行符號都失效了。

但如果使用Results To Text時,輸出結果看來又很正常。

同樣的SQL,改用使用SQL Server Management Studio 2014,在Results to Grid輸出結果中,卻又可正常顯示。

查了一下,在 SQL Server Management Studio 2017有個屬性可以設定

Tools > Options >Query Results > SQL Server >Results to Grid,勾選Retain CR/LF on copy or Save。

2018年6月14日 星期四

IE瀏覽器無法瀏覽某些HTTPS網站

最近的IE突然無法瀏覽某些HTTPS網站,顯示要連線的網站TLS不安全。
但相同的網站使用FIREFOX卻可以瀏覽。

因為最近本機使用了IISCrypto.exe工具將關閉了TLS 1.0、CIPHER SUITES也停掉了一些加密原則,所以用FIREFOX查看這些網站的HTTPS的資訊




後來發現部份網站雖是TLS1.2,但CIPHER  SUITES是 TLS_RSA_WITH_AES_128_CBC_SHA,所以如果本機的加密原則未啟用就會造成無法連線。

IE走的SSL設定應是與WINDOWS主機相同,所以為了資安關掉一些SSL設定時,得要小心。
在不同瀏覽器使用使用這個網址檢測支援的CIPHER SUITES
https://cc.dcsec.uni-hannover.de/
 
使用SSLLAB檢測瀏覽器
https://www.ssllabs.com/ssltest/viewMyClient.html

publish error allowDefinition='MachineToApplication'

一個老舊的aspx web form專案,調了一些功能建置成功,但進行部署時顯示以下錯誤。 在應用程式層級之外使用註冊為 allowDefinition='MachineToApplication' 的區段發生錯誤。錯誤的原因可能是虛擬目錄尚未在 IIS 中設定為...