csv file reading problem

So I have a csv file:

"453FDG","656HGH","54645","MARIA","V543","534","TRETCITY","R34",09094553,09094553,09094553,"21/01/10","RE"
"45er3FDG","656HGH","54645","M343ARIA","V543","534","TRETCITY","R34",090-94553,0909-4553,090-94553,"21/01/10","RE"

problem 1:

Connection string is this:

Dim strConnString As String = "Driver={Microsoft Text D开发者_如何学Criver (*.txt; *.csv)};Dbq=" & System.IO.Path.GetDirectoryName(filediag.PostedFile.FileName).ToString & ";Extensions=asc,csv,tab,txt;Persist Security Info=False;HDR=NO;IMEX=1"

My problem is when i use this schema.ini, the 9th, 10th and 11th column of the second row of the csv file doesn't read properly if there's a special character in it (it supposed to be telphone number), i think because the row above is returned as a number(integer) because it's pure numeric:

[#42r.csv]:

ColNameHeader= false

Format=CSVDelimited

MaxScanRows=0

CharacterSet=ANSI

So what will I do with this?

problem 2:

Since I can't solve the prob no 1, i tried to use the second connection string:

Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.IO.Path.GetDirectoryName(filediag.PostedFile.FileName).ToString & ";Extended Properties='text;HDR=No;FMT=Delimited;IMEX=1"

The problem with this is it treat the first row of the csv file as a column header. Please help. Thanks.

---

The issue is that you're using Select * FROM CSVFILE.CSV. This is forcing ADO to infer the datatypes, for which it will probably just use the first row.

The best thing to do is probably to follow the schema suggested in this question:

When reading a CSV file using a DataReader and the OLEDB Jet data provider, how can I control column data types?