Re: Data Type Change while using FSO?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Aaron Bertrand [MVP] (aaron_at_TRASHaspfaq.com)
Date: 04/13/04 

Date: Tue, 13 Apr 2004 10:22:27 -0400

Don't put ' delimiters around numeric values! This will tell Access to
treat them as strings. So, you need two AddSQL functions, or an additional
argument that tells what type of delimiter (nothing for numerics, ' for
strings, and # for dates). 

-- 
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"JohnL" <anonymous@discussions.microsoft.com> wrote in message 
news:1be6c01c42161$b80d24f0$a401280a@phx.gbl...
> 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
>     %>

Relevant Pages

  • Re: How to store numerical data in metadata
    ... i stored all numerics as a 8 byte double ... a field for each data type I am supporting. ... Another field records the ... support numeric variables in this scenario? ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Getting two columns SUM()
    ... stop using VARCHARfor numerics. ... a column is not like a field is that it has a data type. ... that you fix this nightmare in a nameless column in a nameless table: ... DDL leads to bad DML. ...
    (microsoft.public.sqlserver.programming)
  • Re: Evil type coercion
    ... > Try reading the original posts Russ, ... containing strings and numerics are compared, ... actually you were the unwitting victim of a rule in which no type coercion ...
    (microsoft.public.vb.syntax)
  • Re: A nongeneric bounded string array type (in database code)
    ... > and all numerics must be marshalled into and out of strings, ... Of course, I don't use strings for numeric data, and when I use ... Quality is scientific reality. ... -- from Zen and the Art of Motorcycle ...
    (comp.lang.ada)
  • Re: Sorting help
    ... manipulated as numerics, and sorted as numerics. ... Subject: Sorting help ... What is the best way to sort such an array of strings? ...
    (comp.lang.ruby)