Results 1 to 2 of 2

Thread: [RESOLVED] vbs to download data from url as csv table

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2021
    Posts
    2

    Resolved [RESOLVED] vbs to download data from url as csv table

    Hi!

    I am using the following code to download data from a website using vbs. The data is held in the webpage in table form. However, the resulting downloaded data is in the form of a simple continuous text, which I cannot import into excel and use as a table. (I can download the same using excel etc., but I find vbs to be much faster and efficient).

    The solution at https://www.example-code.com/vbscrip...ble_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 the data in csv format using vbs only and without using a third-party software.

    Also, I had posted this request on Stackoverflow at https://stackoverflow.com/questions/...91746#65891746. A user had posted a probable solution, but I am unable to use it due to my limited vbs literacy.

    Perhaps above links could give some ideas?

    Code:
    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://www.investing.com/indices/major-indices"
    
    '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
    Note:

    1. I would request to kindly post the full working code with modifications to download the data in csv format, since I am not familiar with vbs.

    2. The file with the above code needs to be saved in D:\ as any_name.vbs and resulting downloaded data file will be downloaded in D:\

    Thanks.

  2. #2

    Thread Starter
    New Member
    Join Date
    Jan 2021
    Posts
    2

    Re: [RESOLVED] vbs to download data from url as csv table

    Hi!

    I got the solution from Stackoverflow at https://stackoverflow.com/questions/...91746#65891746

    I was however wondering how to modify the code to download only first 4 rows from the website. I know For i = 1 to 4 or Do While loops should work, but I am unable to get it to work.

    Could someone kindly help me modify below code to download only first 4 rows:

    Code:
    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_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
                CSV = CSV & ROW & vbCrLf
            Next
        Next
        MyFile.WriteLine CSV
        MyFile.close
    End If
    
    End Sub
    
    'Process Completed
    'WScript.Quit
    Thanks
    Last edited by Rohan_Singh; Jan 26th, 2021 at 05:05 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width