如何将csv文件从互联网加载到Access数据库？ [英] How to load csv files from the internet into an Access database?

问题描述

我有以下包含ticker符号的数组： Public Shared tickerArray（）As String = {GOOG，AAPL，V，MSFT} 。我需要使用这个循环：对于每个tickerValue在Form1.tickerArray 中将每个代码符号的csv文件加载到Microsoft Access中的一个大表中。 csv文件位于http://ichart.yahoo.com/table.csv?s=& tickerValue 。我还需要相应的报价符号被加载到从csv文件导入的表的每一行，以使每一行唯一。因此，数据库中的列应为：Ticker，Date，Open，High，Low，Close，Volumne& Adj Close。

加载一个本地csv文件到Access，但我似乎不知道如何加载csv文件从互联网到Access通过vb.net。

需要频繁更新此表，所以我需要从csv文件中只插入新的唯一行。无重复。

有任何建议吗？非常感谢！

UPDATE：
这是我到目前为止的代码。

 导入System.Data 
导入System.Data.OleDb 
导入System.Net 
导入System.IO 
 
公共类Form1 
 
公共共享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 
'创建Yahoo的请求URL。 
 strURL =http://ichart.yahoo.com/table.csv?s=& tickerValue 
 strBuffer = RequestWebData（strURL）
'创建数组。 
 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（）
对于每一行在lines.Skip（1）
 MsgBox（Line） 
 Dim myInsert As String = TextToInsert（Line，tickerValue）
 Dim cmd作为新的OleDbCommand（myInsert，myConnection）
 cmd.ExecuteNonQuery（）
下一页
下一页
 End Sub 
 
函数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日期，打开，高，低，关闭，音量，Adj关闭）& VALUES（&'&&'，'& Dt&'，'& Open&'，'& High&'，& '& Low&'，'& amp;'，'&'，'& Adj_Close&'&）
返回SQLstr 
结束函数
 
私有函数RequestWebData（ByVal pstrURL As String）As String 
 Dim objWReq As WebRequest 
 Dim objWResp As WebResponse 
 Dim strBuffer As String 
'联系网站
 objWReq = HttpWebRequest.Create（pstrURL）
 objWResp = objWReq.GetResponse（）
'从网站读取答案并将其存储到流中
 Dim objSR As StreamReader 
 objSR =新的StreamReader（objWResp.GetResponseStream）
 strBuffer = objSR.ReadToEnd 
 objSR.Close（）
 
 objWResp.Close （）
 
返回strBuffer 
结束函数
 
结束类
  

我得到错误代码OleDBException未处理。INSERT INTO语句中的语法错误。在 cmd.ExecuteNonQuery（）请帮助！

解决方案

使用System.Data.OleDb命名空间定义函数，使其插入db。
类似的东西（非常粗糙）：

  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（）
  

然后在csv

中的每一行上循环。

  Dim myInsert as String = functionTextToInsert（inputString）
 
 Dim cmd作为新的OleDbCommand（myInsert，myConnection）
 
 cmd.ExecuteNonQuery（）
  

functionTextToInsert（ByVal inputString as string）是一个转换插入字符串中的csv的行的函数：max; min; vol Insert into MYTABLE（Column_01，Column_02，Column_03）VALUES（max，min，vol）;

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!

解决方案

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);"

这篇关于如何将csv文件从互联网加载到Access数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！