Categories

Subscribe via Email

Enter your email address to subscribe and receive notifications of new content by email.

Saving a CSV file to a database

Create a database in access called stock.mdb .

Now create a table like in the image in figure 1 and save this as stock

stock database

stock database

Now ensure that the last price , change , open price , daily high , daily low fields all have the field size set as Double , this is because the data is fractional and if you use the default Field size for a Number data type in Access this will give you a Long Integer and you will lose some of the data.

<!– #include file =”adovbs.inc”–>
<%

‘declare variables for the database
Dim objConn , objRS
‘create instance of the Connection object
Set objConn = Server.CreateObject(“ADODB.Connection”)
‘open our stock database
objConn.Open “DBQ=” & Server.MapPath(“stock.mdb”) & “;Driver={Microsoft Access Driver (*.mdb)}”
‘create instance of the recordset object
Set objRS = Server.CreateObject(“ADODB.Recordset”)
‘our SQL statement
strSQL = “SELECT * FROM stock”
‘open the database
objRS.Open strSQL, objConn , adOpenStatic , adLockOptimistic
‘declare our variables for the file handling
Dim objFSO , strURL , objFile
‘create an instance of the file system object
Set objFSO = Server.CreateObject(“Scripting.FileSystemObject”)
‘this is the csv file downloaded from yahoo
strURL = Server.MapPath(“quotes.csv”)
‘open the file
Set objFile = objFSO.OpenTextFile(strURL)
‘while we are not at the end of the file
Do While Not objFile.AtEndOfStream
‘store the contents of the file in strText
strText = objFile.readLine
‘split the strText
arrText = split(strText, “,”, 9)
‘add our new records
objRS.AddNew
objRS(“symbol”) = arrText(0)
objRS(“last price”) = arrText(1)
objRS(“date”) = arrText(2)
objRS(“time”) = arrText(3)
objRS(“change”) = arrText(4)
objRS(“open price”) = arrText(5)
objRS(“daily high”) = arrText(6)
objRS(“daily low”) = arrText(7)
objRS(“volume”) = arrText(8)
‘update the database
objRS.Update
Loop
‘close and destroy objects
objRS.Close
objFile.Close
Set objRS = nothing
Set objConn = nothing
Set objFile = Nothing
Set objFSO = Nothing
%>

FacebookTwitterGoogle+PinterestTumblrStumbleUponRedditLinkedInWhatsAppBibSonomyDeliciousDiggDiigoSina WeiboWordPressBlogger PostShare

Leave a Reply

  

  

  


*

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>