csv file reading problem

Question

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 Driver (*.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.

Please can you post more of the code used to do the reading of the data - there may well be a workaround. — Jon Egerton, Jul 08 '11 at 08:35
I JUST FILL THE DATASET WITH THE STRING OF "SELECT * FROM CSVFILE.CSV" — Blair Yumi, Jul 08 '11 at 08:43
CORRECTION WITH PROB 2: IT HAS THE SAME EFFECT WITH PROB 1, THEY BOETH DOESN'T RETURN THE RIGHT DATA OF TEL NUMBER FROM CSV FILE. IT RETURN NULL WHENEVER IT HAS A - (DASH) CHARCTER IN IT — Blair Yumi, Jul 08 '11 at 08:44

score 1 · Answer 1 · edited May 23 '17 at 10:34

1

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?

edited May 23 '17 at 10:34

Community

1
1

answered Jul 08 '11 at 09:11

Jon Egerton

40,401
11
97
129

That's great. If the answer helped then mark this answer using the tick on the left. – Jon Egerton Jul 11 '11 at 06:51

csv file reading problem

1 Answers1