How to load csv files from the internet into an Access database?

Question

I have the following array that contains ticker symbols: Public Shared tickerArray() As String = {"GOOG", "AAPL", "V", "MSFT"}. I need to use this loop: For Each tickerValue In Form1.tickerArray to load the csv file for each ticker symbol into one large table in Microsoft Access. The csv files are located at "http://ichart.yahoo.com/table.csv?s=" & tickerValue. I also need the respective ticker symbol to be loaded into each line of the table that is imported from the csv file in order to make each line unique. So the columns in the database should be: "Ticker, Date, Open, High, Low, Close, Volumne & Adj Close".

I've found information about loading a local csv file into Access but I can't seem to figure out how to load csv files from the internet into Access through vb.net.

Also, I will need to update this table frequently so I need to only insert new unique lines from the csv file. No duplicates.

Any suggestions? Thanks!

UPDATE: Here is the code I have so far.

Imports System.Data
Imports System.Data.OleDb
Imports System.Net
Imports System.IO

Public Class Form1

Public Shared tickerArray() As String = {"GOOG", "AAPL", "V", "MSFT"}

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
    Dim myConnection As OleDbConnection
    Dim DBpath As String = 
    Dim sConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBpath & ";Persist Security Info=True"
    myConnection = New OleDbConnection(sConnectionString)
    myConnection.Open()

    Dim strURL As String
    Dim strBuffer As String
    For Each tickerValue In Form1.tickerArray
        'Creates the request URL for Yahoo.
        strURL = "http://ichart.yahoo.com/table.csv?s=" & tickerValue
        strBuffer = RequestWebData(strURL)
        'Create array.
        Dim sReader As New StringReader(strBuffer)
        Dim List As New List(Of String)
        Do While sReader.Peek >= 0
            List.Add(sReader.ReadLine)
        Loop
        Dim lines As String() = List.ToArray
        sReader.Close()
        For Each Line In lines.Skip(1)
            MsgBox(Line)
            Dim myInsert As String = TextToInsert(Line, tickerValue)
            Dim cmd As New OleDbCommand(myInsert, myConnection)
            cmd.ExecuteNonQuery()
        Next
    Next
End Sub

Function TextToInsert(ByVal inputString As String, ByVal ticker As String)
    Dim Dt As String = inputString.Split(",")(0).Trim
    Dim Open As String = inputString.Split(",")(1).Trim
    Dim High As String = inputString.Split(",")(1).Trim
    Dim Low As String = inputString.Split(",")(1).Trim
    Dim Close As String = inputString.Split(",")(1).Trim
    Dim Volume As String = inputString.Split(",")(1).Trim
    Dim Adj_Close As String = inputString.Split(",")(1).Trim
    Dim SQLstr As String
    SQLstr = "INSERT INTO Historical (Ticker, Date, Open, High, Low, Close, Volume, Adj Close) " & "VALUES (" & "'" & ticker & "','" & Dt & "','" & Open & "','" & High & "'," & "'" & Low & "','" & Close & "','" & Volume & "','" & Adj_Close & "'" & ")"
    Return SQLstr
End Function

Private Function RequestWebData(ByVal pstrURL As String) As String
    Dim objWReq As WebRequest
    Dim objWResp As WebResponse
    Dim strBuffer As String
    'Contact the website
    objWReq = HttpWebRequest.Create(pstrURL)
    objWResp = objWReq.GetResponse()
    'Read the answer from the Web site and store it into a stream
    Dim objSR As StreamReader
    objSR = New StreamReader(objWResp.GetResponseStream)
    strBuffer = objSR.ReadToEnd
    objSR.Close()

    objWResp.Close()

    Return strBuffer
End Function

End Class

I get error code "OleDBException was unhandled. Syntax error in INSERT INTO statement." at line cmd.ExecuteNonQuery() Please help!

Answer 1

you may use the System.Data.OleDb Namespace to define function to make insert into the db. Something like (in a very rough way):

Dim myConnection As OleDbConnection

Dim sConnectionString  As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBpath & ";Persist Security Info=True"

myConnection = New OleDbConnection(sConnectionString)

myConnection.Open()

Then make a cycle on each line in the csv

Dim myInsert as String= functionTextToInsert(inputString)

Dim cmd As New OleDbCommand(myInsert, myConnection)

cmd.ExecuteNonQuery()

The functionTextToInsert(ByVal inputString as string) is a function that converts a line from the csv in a insert string: "max;min;vol" -> "Insert into MYTABLE (Column_01,Column_02,Column_03) VALUES (max,min,vol);"

Answer 2

Try this:

Writing large number of records (bulk insert) to Access in .NET/C#

I've never worked with DAO, so I can't be much help here, but...

if this MSSQL syntax works on MS ACCESS, use a command object and pass it a parameter containing your csv as a varchar(max), or similar access data type, which will the content of the CSV from your app:

BULK
INSERT mytable
FROM @myCSVstring
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Two things, the SO post states that DAO is a lot faster, and your terminators may be different, but I'd start with these.

Answer 3

I think the previous answers are unnecessarily complicated. All you need to do it send an XMLHTTP request. That fetches the CSV data as a string, which can then be parsed into a table.

Below is a VBA subroutine that does that. Documentation of where I found the techniques is in the code comments. I've run this in Access 2019, and it works.

The modifications of the data indicated in the question can be performed before or after inserting the data into the table.

Sub pImportOnlineCsvAsTable(sURL As String, sTableName As String, Optional sKey As String)

' Download a CSV file from the Internet and parse it into an Access table (all strings).

' Adapted from "www.stackoverflow.com/questions/52142757/vba-download-csv-from-a-link/#52175815"
'   This shows how to download an online CSV file into an ADO stream, then save it as a local file.
' and "www.stackoverflow.com/questions/33860833/convert-adodb-binary-stream-to-string-vba/#33883789"
'   This shows I don't need the stream, but can use the CSV data as a string and parse it into a table.

' Documentation on "XMLHTTP" object:
'   "https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms760305(v=vs.85)"

Dim oWinHttpReq As Object
Dim oDB As Database
Dim sSQL As String, sCSVdata As String, _
    asCSVrows As Variant, asCSVfields As Variant, _
    nCSVrows As Long, nCSVfields As Integer, _
    nCountRows As Long, nCountFields As Integer

Set oDB = CurrentDb()
Set oWinHttpReq = CreateObject("Microsoft.XMLHTTP")
oWinHttpReq.Open "get", sURL, False
oWinHttpReq.send
If oWinHttpReq.status = 200 Then
    sCSVdata = oWinHttpReq.ResponseText
    asCSVrows = Split(sCSVdata, Chr(10))
    nCSVrows = UBound(asCSVrows) + 1
    asCSVfields = Split(asCSVrows(0), ",")
    nCSVfields = UBound(asCSVfields) + 1
    sSQL = ""
    For nCountFields = 0 To (nCSVfields - 1)
        sSQL = sSQL & "[" & asCSVfields(nCountFields) & "] varchar(255)" & _
                    IIf(nCountFields = nCSVfields - 1, "", ", ")
      Next
    sSQL = "create table [" & sTableName & "] (" & sSQL & ");"
    oDB.Execute (sSQL)
    For nCountRows = 1 To (nCSVrows - 1)
        asCSVfields = Split(asCSVrows(nCountRows), ",")
        sSQL = ""
        For nCountFields = 0 To (nCSVfields - 1)
            sSQL = sSQL & """" & asCSVfields(nCountFields) & """" & _
                        IIf(nCountFields = nCSVfields - 1, "", ", ")
          Next
        sSQL = "insert into [" & sTableName & "] values (" & sSQL & ");"
        oDB.Execute (sSQL)
      Next
    If sKey <> "" Then _
        oDB.Execute "alter table [" & sTableName & "] add primary key ([" & sKey & "]);"
  End If

Set oWinHttpReq = Nothing
Set oDB = Nothing

End Sub     ' pImportOnlineCsvAsTable()