Null Values when exporting Excel to CSV using OLEDB

Visual Studio378
I have a 4 column, 6 row Excel sheet with all cells formatted for text:

1 1 a a

2 2 b b

3 3 c c

a 4 d 1

b 5 e 2

c 6 f 3



I have the following schema.ini:



[ADP.csv]

ColNameHeader=True

CharacterSet=1252

Format=CSVDelimited

Col1=F1 Char Width 255

Col2=F2 Char Width 255

Col3=F3 Char Width 255

Col4=F4 Char Width 255



I achieved this schema.ini by editing the one created by .NET. It

originally consisted of 3 lines that contained FLOAT for datatype.



When I create a CSV file using OLEDB and .NET, I get the following

1 1 a

2 2 b

3 3 c

4 d 1

5 e 2

6 f 3



In a column, only text or only numbers work fine. Mixed types omit the text.

Here is my OLEDB code line that builds the CSV file



Connection string = "Provider=Microsoft.Jet.OLEDB.4.0;Data

Source=C:\Temp\Book1.xls;Extended Properties="Excel 8.0;HDR=NO;"



Dim oCmd As New System.Data.OleDb.OleDbCommand("SELECT * INTO

[Text;DATABASE=C:\Temp].[ADP.csv] FROM [sheet1$]", oConn)



I did review

msdn.microsoft.com/library/default.asp



but it did not help. What can I do to force the text cells not to be null

in columns that also contain numbers formatted as text. XLS, CSV, and INI

are all in same folder.


-