最近在測試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月26日 星期二
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。
在執行一段之前寫的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
但相同的網站使用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
訂閱:
文章 (Atom)
publish error allowDefinition='MachineToApplication'
一個老舊的aspx web form專案,調了一些功能建置成功,但進行部署時顯示以下錯誤。 在應用程式層級之外使用註冊為 allowDefinition='MachineToApplication' 的區段發生錯誤。錯誤的原因可能是虛擬目錄尚未在 IIS 中設定為...
-
上網找了免費的白箱檢測工具,找到了 puma scan 因為支援OWASP TOP 10的檢測,所以這是一個符合客戶需求又能與Visual studio結合的工具(白話說...交差了事矣....) 更重要的是這不用錢....檢測工具都貴森森的,尤其是源碼檢測工具。 ht...
-
在一台windows 2016的主機上,某一天,用遠端登入後,出現了以下的訊息.... 遠端工作階段中斷,因為沒有提供授權的遠端桌面授權伺服器可以使用。請連伺服器系統管理員。 這啥啊........... 想不到,在MOBILE01找到了解決...........哈哈...
-
SQL Server 2005 後,提供EXCEP 和INTERSECT, 功能和ORACLE的MINUS, INTERSECT相同 EXCEP 找出存在於第一個子集,但不存在於第二個子集的筆數 INTERSECT 找出皆存在於二個子集中的筆數 這二個方法很方便查找有多...