By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,319 Members | 2,361 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,319 IT Pros & Developers. It's quick & easy.

Null Values when exporting Excel to CSV using OLEDB

P: n/a
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
http://msdn.microsoft.com/library/de...a_ini_file.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.

Aug 16 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.