OleDbDataReader How to read number types?

Question

Trying to read data from *.XLS file. I am able to read all the cell strings but unable to read the numbers in the cells. I have tried using .ToString() method with no success. Getting back a database null.

Code:

public xlsConverter(string excelFilePath, int numColumns)
        {
            string cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR=No;'", excelFilePath);
            string queryStr = "SELECT * FROM [Sheet1$]";

            using (OleDbConnection connection = new OleDbConnection(cnnStr))
            {
                OleDbCommand command = new OleDbCommand(queryStr, connection);
                connection.Open();
                OleDbDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    for(int i = 0; i <= numColumns; i++)
                    {
                        cleanedString.AppendFormat("{0}{1}", (reader[i].ToString()), "|");
                    }
                    cleanedString.AppendLine();
                }

                reader.Close();
            }
        }

cleanedString.AppendFormat("Row[{0}], DataType[{1}], String[{2}], Column[{3}] |-|", counter, (reader[i]).GetType(), reader[i].ToString(), i);

Produces:

Row[98], DataType[System.DBNull], String[], Column[4] |-|

Column 4 contains mixed strings and numbers, eg: 1300, 2341, 5000, 4000 (DED), 1243

Tried using “GetInt, GetValue, Parse, ‘casting’, ToString() etc”, but null is null! Anyone want to lead me in the right direction?

Sample XLS file:

https://github.com/abflett/adamflett_ca

https://github.com/abflett/adamflett_ca/blob/master/sample.xls

If you are certain it's a number, just cast to the type you wish. e.g. (int)reader[i]; — detellda, Apr 19 '16 at 17:13
Tried that one and even changed to: cleanedString.AppendFormat("Row[{0}], DataType[{1}], String[{2}], Column[{3}] |-|", counter, (reader[i]).GetType(), reader[i].ToString(), i); System.DBNull returned on cells that have a number data type. I think maybe I am loosing data with the command, query or connection string? — abflett, Apr 19 '16 at 19:04

score 3 · Accepted Answer · edited May 23 '17 at 12:07

3

Took some digging but ended up being a simple fix.

The connection string was the issue.

string cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0'", excelFilePath);

ImportMixedTypes=Text;TypeGuessRows=0

^Was added!

Youtube video I found it at: https://www.youtube.com/watch?v=jhPp_Hz54BU

and already answerd here at stackoverflow: Help with a OleDB connection string for excel files

edited May 23 '17 at 12:07

Community

1
1

answered Apr 20 '16 at 01:50

abflett

69
13

For me `IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0` was missing from my connection string. Thanks for posting. – RBT Feb 15 '19 at 06:30

OleDbDataReader How to read number types?

1 Answers1

Linked