Reputation: 31
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 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.
Upvotes: 2
Views: 1629
Answers (1)
Reputation: 41539
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:
Upvotes: 1