問題描述

我正在嘗試從 https://www2.cetip.com 獲取 .cvs 文件.br/TitulosCRI 自動進入power BI.要手動下載文件，您必須先點擊Enviar"按鈕(發(fā)送，葡萄牙語)，然后點擊Exportar para CSV"按鈕(導(dǎo)出為 CSV).

I'm trying to get the .cvs file from https://www2.cetip.com.br/TitulosCRI into power BI automatically. In order to do download the file manually, you must first click in the button that says "Enviar" (send, in portuguese) and then in the button that says "Exportar para CSV" (export to CSV).

我的第一次嘗試是找出文件的下載鏈接，但結(jié)果卻是網(wǎng)頁鏈接本身.

My first attempt was to find out the download link for the file, but it turned out to be the webpage link itself.

閱讀后，我了解到當我單擊按鈕時，我可能會發(fā)出 HTTP 發(fā)布請求.我試圖找到一些 Power BI 代碼示例來提出此類請求，但由于我缺乏該主題的知識，因此很難理解所提供的代碼.

Reading about it, I learnt it might be the case that when I click the buttons, I'm making HTTP post requests. I tried to find some examples of Power BI code to make this kind of request, but my lack of knowledge in the subject made it hard to understand the code provided.

通過分析頁面的源代碼，發(fā)現(xiàn)如下代碼，可能與請求有關(guān):

By analyzing the page's source code, I found the code below, which might have something to do with the request:

<input type="submit" name="btExportarCSV" value="Exportar para CSV" id="btExportarCSV" class="button">

誰能幫助我了解如何使用 Power BI 自動獲取此文件?

Can anyone help me understand how can I get this file automatically using power BI?

推薦答案

Lucca，我相信你是巴西人，不過我還是用英文寫，好讓大家明白答案，好嗎?

Lucca, I believe you're from Brasil, but anyway I'll write in english so people can understand the answer, ok?

我多年來一直在嘗試抓取這個網(wǎng)頁(使用 R、python 和 excel+vba)，最后我找到了解決方案:這里和這里.

I'm trying for some years to scrape this webpage (with R, python and excel+vba), and finally I found a solution: here and here.

我在 Excel + VBA 中找到了解決方案.我在下面發(fā)布:

I found a solution within Excel + VBA. That I post bellow:

Option Explicit
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

'------------------------------------------------------

Public Sub AddReference()
    ThisWorkbook.VBProject.References.AddFromFile "C:WindowsSysWOW64UIAutomationCore.dll"
End Sub

'-------------------------------------------------------------'
Sub baixa_titulos_CRI()
'Dimensioning the vars
Dim IE As Object
Dim county As String
Dim htmlDoc As Object
Dim sURL As String
Dim buttonclick As Object
Dim ShowMore As Object
Dim exportar_para_CSV As Object
'----------------'
Set IE = CreateObject("internetexplorer.application")
sURL = "https://www2.cetip.com.br/TitulosCRI"
With IE
.Navigate (sURL)
.Visible = True
End With
'I put some waits to run the webpage properly.
WaitIE2 IE, 2000
Set htmlDoc = IE.Document
WaitIE2 IE, 1000
'here it will press the button "enviar"
Set button_send = htmlDoc.getElementById("btEnviar")
button_send.Click
WaitIE2 IE, 2000
'here it will press the button "exportar para CSV"
Set exportar_para_CSV = htmlDoc.getElementById("btExportarCSV")
exportar_para_CSV.Click
WaitIE2 IE, 2000
'this is the solution that I found to press the button "Salvar" in IE.
'I also save the directory that I wanted as default so IE save in the path properly.
'I still couldnt find a solution to name of the file and to the path to save.
'The solution I adapted is to list.files and the date it was saved to compare with older files (as I think you'll do it also I mentioned it here)
Dim o As IUIAutomation
Dim e As IUIAutomationElement
Set o = New CUIAutomation
Dim h As Long
h = IE.Hwnd
If h = 0 Then Exit Sub
Set e = o.ElementFromHandle(ByVal h)
Dim iCnd As IUIAutomationCondition
Set iCnd = o.CreatePropertyCondition(UIA_NamePropertyId, "Save")
Dim Button As IUIAutomationElement
Set Button = e.FindFirst(TreeScope_Subtree, iCnd)
Dim InvokePattern As IUIAutomationInvokePattern
Set InvokePattern = Button.GetCurrentPattern(UIA_InvokePatternId)
InvokePattern.Invoke
WaitIE2 IE, 2000
IE.Quit
Set IE = Nothing
End Sub
'-----------------------------------------------------------
Sub WaitIE2(IE As Object, Optional time As Long = 250)
Dim i As Long
Do
    Sleep time
    Debug.Print CStr(i) & vbTab & "Ready: " & CStr(IE.ReadyState = 4) & _
                vbCrLf & vbTab & "Busy: " & CStr(IE.Busy)
    i = i + 1
Loop Until IE.ReadyState = 4 Or Not IE.Busy
End Sub

希望對你有幫助

最好的，費利佩·里貝羅

Best, Felipe Ribeiro

[excel] [vba]

[excel] [vba]

這篇關(guān)于Power BI - 從發(fā)布請求中獲取數(shù)據(jù)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！