How to do this ?? csv OHLC data storage

• Digs

  SSCertifiable

  Points: 7333

  More actions

  April 19, 2010 at 5:56 pm

  #221546

  Ok..to give you an idea.

  Copy this URL into browser address bar:

  http://ichart.finance.yahoo.com/table.csv?s=^DJI&a=01&b=01&c=1900&d=ignore&e=&ignore&f=ignore&g=d&ignore=.csv

  The result is a CSV file with: Date, O,H,L,C,V,AdjC with a few thousand records.

  I read this into a vb.net asp.net application like this:

  Dim History As String = String.Empty

  Dim wc As New WebClient()

  History = wc.DownloadString(URL)

  History = History.Replace(vbCr, "")

  Dim rows() As String = History.Split(ControlChars.Lf)

  Dim rowVal() as string = rows(i).Split(","c)

  Then each rowVal is loaded into an Array for manipulation.

  I currently save this Array into a flat text file like : ^DJI.txt . THIS WORKs as I only have a few files. So when I want the data again I look for file (via windows folder operating system search) in the data folder and import it back into a new Array using .net StreamReader functions.

  Question:

  With so many records (1000's of lines of OHLC) how can this be stored quickly and easily into a SQL table by OHLC row or maybe stored the whole string in field or convert to XML and save in a XML field. Doing a INSERT SQL line be line would be a nightmare of slow.

  So how can I go from an array of data with thousands of records into SQL server: either table or field ???

• dmoldovan

  SSCertifiable

  Points: 6334

  More actions

  April 22, 2010 at 6:48 am

  #1156049

  Convert the csv into xml and import the xml using the "nodes" method of the xml datatype.