csv column contain mix datatype connot read all cell data using oledb provider

Question

hi my csv contain mix datatype f.ex. in cell E8 data format is date, and other data in this E column are string. i use this connetion string in classic asp ,VB Script and back end SQl server

ChekCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path1 & ";Extended Properties=""text;HDR=YES;IMEX=1;FMT=Delimited""" Set rs1 = ChekCnn.Execute("SELECT * FROM myCSV.csv")

problem here is when if at cell E8 is there date then other cell which contain string data will not read and show as blank and if at Cell E8 is blank then it read each cell data of column E.

Answer 1

You can use a "schema.ini" to control which data type is used for a column so you can set your column to text an convert the data per row.

MSDN Reference for "schema.ini" : http://msdn.microsoft.com/en-us/library/ms709353.aspx

The "schema.ini" has to be located in the same folder as the *.csv file.

Example:

dim ChekCnn : set ChekCnn=CreateObject ("ADODB.Connection")
dim rs1  : set rs1 = CreateObject ("ADODB.Recordset")
ChekCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "c:\temp\" & ";Extended Properties=""text;HDR=YES;IMEX=1;FMT=Delimited""" 
Set rs1 = ChekCnn.Execute("SELECT * FROM test.csv")

Do until rs1.eof
    wscript.echo rs1("dataname") & "=>" & rs1("data")
rs1.MoveNext
loop

rs1.close

---

test.csv

dataname,data
test1,100
test2,100
test3,100
test4,nothing
test5,100
test6,100
test7,100

---

schema.ini

[test.csv]
Format=CSVDelimited
DecimalSymbol=.
Col1=dataname Text
Col2=data Text

---

Output without schema.ini

test1=>100
test2=>100
test3=>100
test4=>
test5=>100
test6=>100
test7=>100

---

Output with schema.ini

test1=>100
test2=>100
test3=>100
test4=>nothing
test5=>100
test6=>100
test7=>100