VBS从url下载数据为CSV表

Question

我使用以下代码从使用vbs的网站下载数据。数据以表的形式保存在url中。然而，最终下载的数据是一个简单的连续文本数据的形式。

https://www.example-code.com/vbscript/html_table_to_csv.asp的解决方案允许将下载的数据转换为csv格式，但需要预先安装特定的api和软件。

我想知道是否有可能下载/转换数据在csv格式使用vbs，而不使用第三方软件。也许上面的链接可以给一些想法?

(我可以使用excel等下载相同的，但我发现vbs更快，更有效)。

注意:

1. 文件需要保存在D:作为any_name。将下载的数据文件下载到D:

For i = 1 to 1
createFile(i)
Next
Public Sub createFile(a)
Dim fso,MyFile
filePath = "D:file_name" & a & ".txt"
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.CreateTextFile(filePath)

myURL = "https://example-code.com/data/etf_table.html"
'Create XMLHTTP Object & HTML File
Set oXMLHttp = CreateObject("MSXML2.XMLHTTP")
Set ohtmlFile = CreateObject("htmlfile")
'Send Request To Web Server
oXMLHttp.Open "GET", myURL, False
oXMLHttp.send
'If Return Status is Success
If oXMLHttp.Status = 200 Then
'Get Web Data to HTML file Object
ohtmlFile.Write oXMLHttp.responseText
ohtmlFile.Close

'Parse HTML File
Set oTable = ohtmlFile.getElementsByTagName("table")
For Each oTab In oTable
MyFile.WriteLine oTab.Innertext
Next
MyFile.close
End If
End Sub
'Process Completed
'WScript.Quit

Answer 1

我的建议是:

For i = 1 to 1
createFile(i)
Next
Public Sub createFile(a)
Dim fso,MyFile
filePath = "z:_ComunityStackOverflowfile_name" & a & ".txt"
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.CreateTextFile(filePath)

myURL = "https://example-code.com/data/etf_table.html"
'Create XMLHTTP Object & HTML File
Set oXMLHttp = CreateObject("MSXML2.XMLHTTP")
Set ohtmlFile = CreateObject("htmlfile")
'Send Request To Web Server
oXMLHttp.Open "GET", myURL, False
oXMLHttp.send
'If Return Status is Success
If oXMLHttp.Status = 200 Then
'Get Web Data to HTML file Object
ohtmlFile.Write oXMLHttp.responseText
ohtmlFile.Close

'Parse HTML File
Set oTable_coll = ohtmlFile.getElementsByTagName("table")
For Each oTab_enum In oTable_coll
For Each oRow_enum In oTab_enum.rows
ROW = ""
For Each oCell_enum In oRow_enum.cells
ROW = ROW & oCell_enum.innerText & ","
Next
ROW = Left(ROW, Len(ROW) - 1)
CSV = CSV & ROW & vbCrLf
Next
Next
MyFile.WriteLine CSV
MyFile.close
End If
End Sub
'Process Completed
'WScript.Quit

备注:测试作品!!顺便说一句。我不是很好VBS脚本。

Answer 2

你指的是下面这个解决方案吗?

For i = 1 to 1
createFile(i)
Next
Public Sub createFile(a)
Dim fso, MyFile
FilePath = "z:_ComunityStackOverflowfile_name" & a & ".txt"
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.CreateTextFile(FilePath)

' myURL = "https://www.investing.com/indices/major-indices"
myURL = "https://example-code.com/data/etf_table.html"
'Create XMLHTTP Object & HTML File
Set oXMLHttp = CreateObject("MSXML2.XMLHTTP")
Set ohtmlFile = CreateObject("htmlfile")
'Send Request To Web Server
oXMLHttp.Open "GET", myURL, False
oXMLHttp.send
'If Return Status is Success
If oXMLHttp.Status = 200 Then
'Get Web Data to HTML file Object
ohtmlFile.Write oXMLHttp.responseText
ohtmlFile.Close

'Parse HTML File
Set oTable_coll = ohtmlFile.getElementsByTagName("table")

CSV = ""
ROW_idx = 0
COL_idx = 0
For Each oTab_enum In oTable_coll
ROW_idx = 0
For Each oROW_enum In oTab_enum.rows
ROW = "" 

COL_idx = 0
For Each oCell_enum In oROW_enum.cells
If COL_idx >= 0 And COL_idx < 3 Then                
ROW = ROW & oCell_enum.innerText & ","
End If
' incrase Column index counter
COL_idx = COL_idx + 1
Next

If ROW_idx > 2 And ROW_idx < 5 Then
ROW = Left(ROW, Len(ROW) - 1)
CSV = CSV & ROW & vbCrLf
End If

' incrase ROW index counter
ROW_idx = ROW_idx + 1
Next
Next
MyFile.WriteLine CSV
MyFile.close
End If
End Sub