Power BI-从发布请求中获取数据 [英] Power BI - Get data from post request

问题描述

我正在尝试从 https://www2.cetip.com获取.cvs文件.br/TitulosCRI 自动插入Power BI.为了手动下载文件，必须首先单击"Enviar"按钮(发送，使用葡萄牙语)，然后单击"Exportar para CSV"按钮(导出为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).

我的第一个尝试是找到文件的下载链接，但结果却是网页链接本身.

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

阅读此书后，我了解到可能是当我单击按钮时，我正在发出HTTP发布请求.我试图找到一些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.

通过分析页面的源代码，我找到了下面的代码，这可能与请求有关:

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

<输入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?

几年来，我一直在努力抓取此网页(使用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中找到了一个解决方案.我张贴在下面:

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:\Windows\SysWOW64\UIAutomationCore.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]

这篇关于Power BI-从发布请求中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！