vbscript - vbs to download data from url as csv table

Question

I am using the following code to download data from a website using vbs. The data is held at the url in table form. However, the resulting downloaded data is in the form of a simple continuous text data.

The solution at https://www.example-code.com/vbscript/html_table_to_csv.asp allows converting the downloaded data to csv format, but requires specific api and software to be pre-installed.

I was wondering if it would be possible to download/ convert the data in csv format using vbs only and without using a third-party software. Perhaps above link could give some ideas?

(I can download the same using excel etc., but I find vbs to be much faster and efficient).

Note:

1. the file needs to be saved in D: as any_name.vbs and resulting downloaded data file will be downloaded in 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

question from:https://stackoverflow.com/questions/65873401/vbs-to-download-data-from-url-as-csv-table

Answer 1

I would propose this solution:

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

REMARK: TESTED WORKS !!! btw. and I'm not well VBS scripter.