Data Type Change while using FSO?

[JohnL]

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 = oFSpentextfile(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
%>

 

[Aaron Bertrand [MVP]]

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).

 

[Egbert Nierop \(MVP for IIS\)]

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.

just curious, why go the difficult way if Access supports importing? Just
run the text import wizard. Access will remember the import definitions. You
run the import again using the DoCmd statement.

 

[Guest]

Aaron,

Please forgive my ignorance on this. I'm very new to FSO
and this type of script.

Can you show me an example of how this would look?

For example, if the following fields are text:
first_name, surname, email, webpass and cross.

The following are numeric: senate_dist, cong_dist &
house_dist

It usually only takes me one example to learn and make it
work.

Thanks a million,

John

 

[Guest]

I have never seen the DoCmd function. Last year, when I
was building some other updates for the website, I was
informed that there was no way to call an action query or
command using ASP.

Any good tutorial links you can think of?

 

[Egbert Nierop \(MVP for IIS\)]

I have never seen the DoCmd function. Last year, when I
was building some other updates for the website, I was
informed that there was no way to call an action query or
command using ASP.

Any good tutorial links you can think of?

oops: I've not quite read your post. Ignore my message.

 

[Egbert Nierop \(MVP for IIS\)]

Ignore this post

 

[Aaron Bertrand [MVP]]

Only you know what order your fields are in the text file. When you hit
first_name, you will need to surround the value with ' ... when you hit
senate_dist, you will need to NOT surround the value with anything.

PLEASE KEEP REPLIES IN THE NEWSGROUPS, SO EVERYONE BENEFITS. I HAVE TO
FILTER ENOUGH E-MAIL AS IT IS.