问题描述
我正在尝试从 https://www2.cetip.com 获取 .cvs 文件.br/TitulosCRI 自动进入power BI.要手动下载文件,您必须先点击Enviar"按钮(发送,葡萄牙语),然后点击Exportar para CSV"按钮(导出为 CSV).
I'm trying to get the .cvs file from http://www.51sjk.com/Upload/Articles/1/0/334/334117_20221025103349505.jpg 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:
<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?
我多年来一直在尝试抓取这个网页(使用 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: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 = "http://www.51sjk.com/Upload/Articles/1/0/334/334117_20221025103349505.jpg" 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]