Good morning.
I'm importing records from a csv file into an Access
database table using FSO.
There are three fields that need to be inserted as numeric
as they are used in many joins.
They are: senate_dist, house_dist and cong_dist.
I am not too familiar with manipulating data types on the
fly.
If I change the fields in the access table from text to
numeric, I receive a data type mismatch error on import.
If I leave the fields as text in the access table, none of
my joins work, as the related fields are all numeric.
My question is: How would I convert the three fields to
numeric data type while being imported (the data fields
would be changed to numeric in the Access Table).
Thank you for your time,
John
Here is the code for the insert...
<%
Function AddSQL( strField )
AddSQL = "'" + Replace(strField,"'","''") + "'"
End Function
Sub SaveCSV
Dim oCnn, oFSO, strURL, oFile, strText, strSQL,
arrText, nCount
Set oCnn = Server.CreateObject
("ADODB.Connection")
oCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source= q:\websites\mysite\db\aoi.mdb"
Set oFSO = Server.CreateObject
("Scripting.FileSystemObject")
strURL =Server.MapPath("../../db/staff.csv")
Set oFile = oFSO.opentextfile(strURL, 1, false,
0)
' Skip the Headers
oFile.ReadLine
' Read the rest of the values
Do While Not oFile.AtEndOfStream
strText = oFile.ReadLine
arrText = Split( strText & String
(7,","), "," )
strSQL = "INSERT INTO muser(first_name,
surname, email, webpass, [cross], senate_dist, cong_dist,
house_dist) VALUES("
For nCount = 0 To 6
strSQL = strSQL & AddSQL(arrText
(nCount)) & ","
Next
strSQL = strSQL & AddSQL(arrText(7)) & ")"
Response.Write( "SQL = """ & strSQL
& """<br>" )
oCnn.execute( strSQL )
Loop
oFile.Close
Set oCnn = nothing
Set oFile = Nothing
Set oFSO = Nothing
End Sub
%>