vbs to download data from url as csv table

user_15071558 0
Viewed 643

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
csv vbscript
2 Answers

I would propose this solution:

For i = 1 to 1
createFile(i)
Next

Public Sub createFile(a)

    Dim fso,MyFile
    filePath = "z:\_Comunity\StackOverflow\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_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.

user_53149400

Do you mean this following solution ?

For i = 1 to 1
    createFile(i)
Next

Public Sub createFile(a)

Dim fso, MyFile
FilePath = "z:\_Comunity\StackOverflow\file_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
user_53149400
Related