Saving a CSV file to a database

In this example we will take our reading a csv file example a step further and we will store it into a database . Lets get down to business and make our 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 .

figure 1

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.

Code :

<!-- #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
%>

Download

csvtoaccess.zip which contains the database , the script , the ado include file , the quote csv .