ADO.NET text driver not importing data as text

[Buddy Ackerman]

I have the following comma delimited file to read:

Date,campaign code,count
12/1/2004,DM31,10
12/1/2004,DM32,15
12/1/2004,DM33,20
12/1/2004,DM34,25
12/1/2004,DM35,30



I'm using the ADO.NET, System.Data.Odbc namespace to create and ODBCConnection
to this file and read it using the following (simplified) code.


Dim cn As New OdbcConnection("Driver={Microsoft Text Driver (*.txt; _
*.csv)};DefaultDir=d:\websites\jpg\dataimport;")
Dim cmd As New OdbcCommand ("select * From cfr.csv")
Dim myreader As OdbcDataReader

cn.open()
cmd.connection = cn
myreader = cmd.executereader()

response.write("<table>")
While myreader.read()
response.write("<tr><td>" & myreader.getstring(0) & "</td><td>" & _
myreader.getstring(1) & "</td><td>" & _
myreader.getstring(2) & "</td></tr>")
End While
response.write("</table>")

cn.close()



It outputs the following:


2004-12-01 00:00:00 31.0000 10
2004-12-01 00:00:00 32.0000 15
2004-12-01 00:00:00 33.0000 20
2004-12-01 00:00:00 34.0000 25
2004-12-01 00:00:00 35.0000 30


It converted the second field to a numeric value. I can get it to treat the
field as character data if I put the values inside quotes but I don't have
control of this file, it's delivered to me from a third party as is. Is there
anyway to get it to read the data as plain character data without modifying the
file?




--Buddy

 

[Paul Clement]

¤ I have the following comma delimited file to read:
¤
¤ Date,campaign code,count
¤ 12/1/2004,DM31,10
¤ 12/1/2004,DM32,15
¤ 12/1/2004,DM33,20
¤ 12/1/2004,DM34,25
¤ 12/1/2004,DM35,30
¤
¤
¤
¤ I'm using the ADO.NET, System.Data.Odbc namespace to create and ODBCConnection
¤ to this file and read it using the following (simplified) code.
¤
¤
¤ Dim cn As New OdbcConnection("Driver={Microsoft Text Driver (*.txt; _
¤ *.csv)};DefaultDir=d:\websites\jpg\dataimport;")
¤ Dim cmd As New OdbcCommand ("select * From cfr.csv")
¤ Dim myreader As OdbcDataReader
¤
¤ cn.open()
¤ cmd.connection = cn
¤ myreader = cmd.executereader()
¤
¤ response.write("<table>")
¤ While myreader.read()
¤ response.write("<tr><td>" & myreader.getstring(0) & "</td><td>" & _
¤ myreader.getstring(1) & "</td><td>" & _
¤ myreader.getstring(2) & "</td></tr>")
¤ End While
¤ response.write("</table>")
¤
¤ cn.close()
¤
¤
¤
¤ It outputs the following:
¤
¤
¤ 2004-12-01 00:00:00 31.0000 10
¤ 2004-12-01 00:00:00 32.0000 15
¤ 2004-12-01 00:00:00 33.0000 20
¤ 2004-12-01 00:00:00 34.0000 25
¤ 2004-12-01 00:00:00 35.0000 30
¤
¤
¤ It converted the second field to a numeric value. I can get it to treat the
¤ field as character data if I put the values inside quotes but I don't have
¤ control of this file, it's delivered to me from a third party as is. Is there
¤ anyway to get it to read the data as plain character data without modifying the
¤ file?

You will need to use a schema.ini file. Otherwise the Text driver will simply guess at the data
type:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)