locked
Reading Data from Excel using OleDb RRS feed

  • 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

    Friday, February 4, 2011 3:46 PM

Answers

  • 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
    • Marked as answer by Bessie Zhao Friday, February 11, 2011 8:48 AM
    Monday, February 7, 2011 11:43 AM

All replies

  • 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
    Friday, February 4, 2011 4:50 PM
  • 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

    Monday, February 7, 2011 4:50 AM
  • 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
    • Marked as answer by Bessie Zhao Friday, February 11, 2011 8:48 AM
    Monday, February 7, 2011 11:43 AM