Reading Data from Excel using OleDb

Question

Hi,

I am reading data from Excel into a Dataset using OleDB. In Excel a particular column has data as text as well as Numbers. When i read it to Dataset using OleDb, Depending on the First entry of the column, the Datatype of the column is set.

Example if the First entry of the column reads 2.0 and second entry reads "NA", When i read "NA" it gives me System.DBNull error. This error occurs vice  versa.

Is there any way to convert data to string format when i am writing in to Dataset or any other solution.

 

Thanks,

Tushar Bedekar

Answer 1

I don't know what language you are using, but I sisuspect it is C#.  Each cell in a worksheet has a number of different properties.  I think you may want to use the ".text" property to perform your reads/writes.  Don't forget an empty string is not a NULL, but simply "" (two double quotes with nothing inside).  In C# yo can use the "ToMethod" to convert different type variables to strings.

---

jdweng

Answer 2

Hi Joel,

Yes i am using C#. I have already tried the text property of text property.

excelCommand.CommandText = "SELECT * FROM " + spreadSheetName;

Is there a way in which when i write data to dataset it converts to string in the above string.

Thanks,

Tushar Bedekar

Answer 3

I don't know what the type of the variable spreadSheetName is defined in your code.  I checked and a worsheet name and a command text are both strings although a command text can be both a string and a variant.

 

I would typecast both variables to be sure

If ws is the worksheet object then something like this

string spreadSheetName

spreadSheetName = ws.name

(string)excelCommand.CommandText = "SELECT * FROM " + spreadSheetName

 

---

jdweng